[XSL] 在 xsl 裡做字串取代

要在 xsl 裡做字串取代, 有2個方法, 參考看看下面的範例:
1. translate

translate(string,':',', ')

2. template

<xsl:template name="replace-string">
    <xsl:param name="text"/>
    <xsl:param name="replace"/>
    <xsl:param name="with"/>
    <xsl:choose>
      <xsl:when test="contains($text,$replace)">
        <xsl:value-of select="substring-before($text,$replace)"/>
        <xsl:value-of select="$with"/>
        <xsl:call-template name="replace-string">
          <xsl:with-param name="text" select="substring-after($text,$replace)"/>
          <xsl:with-param name="replace" select="$replace"/>
          <xsl:with-param name="with" select="$with"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$text"/>
      </xsl:otherwise>
    </xsl:choose>
</xsl:template>

call template example:

<xsl:call-template name="replace-string">
  <xsl:with-param name="text" select="'aa::bb::cc'"/>
  <xsl:with-param name="replace" select="'::'" />
  <xsl:with-param name="with" select="','"/>
</xsl:call-template>

資料來源:
xslt 1.0 string replace function
http://stackoverflow.com/questions/7520762/xslt-1-0-string-replace-function

Replace
http://www.dpawson.co.uk/xsl/sect2/replace.html#d8766e61

[Asp].ASPStringBuilder 和 .net stringer builder 效能比較

最近遇到一個功能, 要匯出資料, 因為效能很差, 要匯很久, 想看看用純 asp 處理比較快, 還是改用 .net 比較快, 分別測試2種方法, 結論是差不多, 要快的話, 中間使用的流程/架構要翻掉, 改用其他方法, 才會有效率.

這一個個案遇到的是 XML 的內容就 71mb, 含 DOM object 一起的話RAM 大約占用 150mb, 在 apply style 套 xsl 時, 瞬間的 RAM 再會多用掉500多MB, 像寫的這麼爛的程式, 真的很怕有很多人同時點下去執行, 應該要把Web Server 弄掉 memory overflow 錯誤.


有圖有真相, 畫面如下:

測試方案1號: 使用 ASP 寫的 StringBuilder(cASPString.asp),

圖片1.1: 程式執行前的RAM, 大約用掉 127mb:

圖片1.2: 程式執行前的RAM, DOM 大約用掉 150mb 左右:

(說明: 由於沒有在用 RAM 回收的太快, 所以數字不太準.)


測試方案2號: 使用 ASP 呼叫 .Net StringBuilder
圖片2.1: 程式執行前的RAM, 大約用掉 133mb:

圖片2.2: 程式執行前的RAM, DOM 大約用掉 150mb 左右:

在 DOM ready 好的時候, 可以看到 RAM 的使用快速成長, 會從 300mb 變 500, 再長大到 700mb, 最後在 860mb 右右停留 2~3秒, 再回來 200MB 左右.

測試結論: 在 Asp 使用 StringBuilder(cASPString.asp), 和呼叫 .Net 的 String Builder 效能上差不多, 主要的瓶頸在 XML 太大.

附上, asp 版本的 StringBuilder.

'====================================
' MODULE:    cASPString.asp
' AUTHOR:    www.u229.no
' CREATED:   May 2006
'====================================
' COMMENT: A fast string class for classic ASP.               
'====================================
' ROUTINES:

' - Public Property Get NumberOfStrings()
' - Public Property Get NumberOfBytes()
' - Public Property Get NumberOfCharacters()
' - Private Sub Class_Initialize()
' - Public Sub Append(sNewString)
' - Public Function ToString()
' - Public Sub Reset()
'====================================


'====================================
Class ASPStringBuilder
'====================================

'// MODULE VARIABLES
Dim m_sArr            '// Array holding the strings.
Dim m_lResize        '// Factor for rezising the array.
Dim m_lStrings       '// Number of strings appended.

'// PROPERTIES
Public Property Get NumberOfStrings()
    NumberOfStrings = m_lStrings
End Property

Public Property Get NumberOfBytes()
    NumberOfBytes = LenB(Join(m_sArr, ""))
End Property

Public Property Get NumberOfCharacters()
    NumberOfCharacters = Len(Join(m_sArr, ""))
End Property

'------------------------------------------------------
' Comment: Initialize default values.
'------------------------------------------------------
Private Sub Class_Initialize()
    m_lResize = CLng(50)
    m_lStrings = CLng(0)
    ReDim m_sArr(m_lResize)
End Sub

'------------------------------------------------------
' Comment: Add a new string to the string array.
'------------------------------------------------------
Public Sub Append(sNewString)

    If Len(sNewString & "") = 0 Then Exit Sub
    
    '// If we have filled the array, resize it.
    If m_lStrings > UBound(m_sArr) Then ReDim Preserve m_sArr(UBound(m_sArr) + m_lResize)

    '// Append the new string to the next unused position in the array.
    m_sArr(m_lStrings) = sNewString
    m_lStrings = (m_lStrings + 1)
End Sub

'------------------------------------------------------
' Comment: Return the strings as one big string.
'------------------------------------------------------
Public Function ToString()
    ToString = Join(m_sArr, "")
End Function

'------------------------------------------------------
' Comment: Reset everything.
'------------------------------------------------------
Public Sub Reset()
    Class_Initialize
End Sub

'====================================
End Class 
'====================================

讓 asp call .net string builder 的方法:

namespace MyObject
{
    public class StringBuilderObject
    {
        public StringBuilder MyStringBuilder = new StringBuilder("");
        public StringBuilderObject()
        {
        }
        public void Append(string value)
        {
            this.MyStringBuilder.Append(value);
        }
        public void Remove(int startIndex, int length)
        {
            this.MyStringBuilder.Remove(startIndex, length);
        }
        public override string ToString()
        {
            return this.MyStringBuilder.ToString();
        }
    }
}

[Asp].實作網頁匯出為 PDF 的心得

今天分享我在實作網頁匯出為 PDF 的心得, 今天遇到2個問題:
Q 1. 在後台匯出的網頁, 要匯出的網頁必需為 "登錄狀態" 才能匯出, 可是使用 wkhtmltopdf 時, 無法取得登入狀態.
Q 2. wkhtmltopdf 元件匯出的資料夾, 無法和中文目錄相容, 會有Error 產生, 訊息如下:

匯出檔案到 E:\oooo網\ 這個 "網" 是中文字, 造成Error.

Q 1. 首先解決, 登錄狀態, 的問題,
A 1: 這個解決方法很多很多,

方案1.1: 直接輸入 userid.
這是最快的解決方法, 直接寫一支 app_sso.asp 程式, 傳入 userid 就解決了. 可是這個解法變成, 萬一我是離職的工程師, 我事先就知道這支程式, 在user 傳入 userid 就可以取得該帳號的權限, 這樣問題就比較大.


方案1.2: 修改後台的程式, 遇到某些參數傳進來時(例如: ?exportPdf=true), 就開放部份的權限, 允許顯示資料庫內容. 這個解法也不錯.

方案1.3: session userid 放database, 透過 tokenid 來存取.
和上面的方案1.1相比, 這個方案1.3 會安全一點點, app_sso.asp 處理步驟如下:

1.3.1. 後台把 session userid 放進 table,
1.3.2. 取出 table 裡的 token_id,
1.3.3. 把 token_id 給下一支沒有 session 的 app,
1.3.4. 沒有 session 的 app request token_id 後, 透過 tokenid 再到 database 裡去取出 userid.
1.3.5 模擬使用者登入, 並重導(Redirect) 到實際上要產生為 PDF 的 URL.

附上使用到的 table schema:

CREATE TABLE [dbo].[webkitHtmlToken](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [tokenID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_webkitHtmlToken_tokenId]  DEFAULT (newid()),
 [userid] [nvarchar](20) NOT NULL,
 [siteid] [varchar](50) NULL,
 [status] [char](1) NULL CONSTRAINT [DF_webkitHtmlToken_status]  DEFAULT ('I'),
 [sessionid] [int] NULL,
 [createdDate] [smalldatetime] NULL CONSTRAINT [DF_webkitHtmlToken_createdDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_webkitHtmlToken] PRIMARY KEY CLUSTERED
(
 [tokenID] ASC
)
)

說明: 可能專案不同, 有可能需要多增加, 或刪除相關欄位.


Q 2: 中文字,
A 2:這個要解就很簡單, 先透過副程式判斷是否有中文字, 有的話就使用暫存的資料夾.


最後, 整個流程方式, 我的做法是:
2.1 在AP設定檔裡增加2個設定值,
(a)wkhtmltopdf 執行檔路徑
(b)wkhtmltopdf 遇到中文字路徑時, 要使用的暫存資料夾, 這個記得要允許 IUSER 有寫入及刪除檔案的權限, 例如:

<wkhtmltopdf>
    <path>C:\wkhtmltopdf\wkhtmltopdf.exe</path>
    <tempFolder>C:\wkhtmltopdf\tempFolder</tempFolder>
</wkhtmltopdf>

2.2 寫副程式, 取得 tokenid

'// purpose: 放入 session info 到 database, 取得 tokenid.
'// ex: ret = getWebkitHtmlTokenID(conn, session("userid"), session.sessionid, session("siteid"))
function getWebkitHtmlTokenID(byref conn, byval userid, byval sessionid, byval siteid)
...
...
end function

2.3 寫副程式, 取得 tokenid

'// purpose: 輸出某一個 URL 到實體檔案.
'// call HtmlToPdfFile(pdfUrl, user_output_filepath)
function HtmlToPdfFile(byval pdfUrl, user_output_filepath)
...
...
end function

2.4 寫一支  export_pdf.asp
2.4.1 先呼叫 2.2 的附程式取得 tokenID.
2.4.2 再把 tokenID 放入 app_sso.asp, 並設定實際要匯入資料的 URL.
2.4.3 輸出某一個 URL 到實體檔案, 在這個副程式裡執行外部指令(要等待程式回應, Wait For Single), 等待wkhtmltopdf.exe 匯出pdf 完成程式再繼續往下執行.
2.4.4 透過 ADODB.Stream, 取出檔案內容, 並 Response.BinaryWrite objStream.Read
2.4.5 最後, 完成後, 刪掉暫存檔, 後台匯出pdf 完成.
call DeleteFile(user_output_filepath)


說明: 前台(無登錄狀態)匯出 pdf 的話就會簡單很多, 少掉取得 tokenid 這一個 step.

增加這個功能, 會用到的相關檔案及說明:

• ApConfig.xml , 參數設定
• app_sso.asp , 讓外部程式模擬登入
• export_pdf.asp , 實際上匯出的 app.
• pdf.Function.asp , 副程式.

相關文章:
[Asp].用正規表示式檢查字串是不包含中文字
http://maxtellyou.blogspot.tw/2012/10/asp.html

[Asp].用正規表示式檢查字串是不包含中文字

今天遇到一個問題, wkhtmltopdf 在輸出 pdf 時, 無法輸出檔案到中文的資料夾下, 會發生錯誤:
Error: Unable to write to destination

我想到的解決辦法是, 先放到一個暫存的資料夾, 再搬到有中文的真正的目的地, 即可, 所以要先判斷輸出的資料夾, 是否包含中文, 附件 Asp 用的檢查副程式.

'// RegExp Test.
Function RegExpTest(byval patrn, byval str)
    Dim regEx
    Set regEx = New RegExp
    regEx.Pattern = patrn
    regEx.IgnoreCase = True
    regEx.Global = True
    RegExpTest = regEx.Test(str)
End Function


'// 檢查文字中是否有中文字
'// ex: ret = IsMatchChinese(str)
'// return:
'//     True: find.
'//     False: not found.
Function IsMatchChinese(byval str)
    IsMatchChinese = RegExpTest("[一-龥]+",str)
End Function

呼叫的範例如下:

if IsMatchChinese(output_filepath) then

    response.write "<br/>bingo, match chinese folder..."

else

    response.write "<br/>ok, not chinese folder continue..."

end if

聽說 Asp.Net 的範例如下, 還沒測試過:

Regex ex = new Regex("[一-龥]"); 
bool isMatched = ex.IsMatch("jjsss 中文 ksks");

[SQL].Exploring your database schema with SQL

今天有一個同事問我, 他用 M$ 的 sp_tables 取得的 tables 包含了 VIEW, 他不想列出 view, 該怎麼做? 我跟他講, 可以直接存取 sys.tabes 裡的 name 欄位, 就可以取得該 database 裡所有的 table name.


資料來源:
Exploring your database schema with SQL
http://www.simple-talk.com/sql/t-sql-programming/exploring-your-database-schema-with-sql/

In the second part of Phil's series of articles on finding stuff (such as objects, scripts, entities, metadata) in SQL Server, he offers some scripts that should be handy for the developer faced with tracking down problem areas and potential weaknesses in a database.

Pretty quickly, if you are doing any serious database development, you will want to know more about a database than SSMS can tell you; there are just more things you might need to know than any one GUI can provide efficiently. For example, if you are doing a review of a development database, there are a number of facts you’ll need to establish regarding the database, its tables, keys and indexes, in order to home-in on any possible problem areas.

Fortunately, SQL Server provides any number of ways to get at the metadata you need. TheINFORMATION_SCHEMA views provide basic metadata about most of the entities in each database. The far-more-expansive Catalog views offer just about every piece of metadata that SQL Server currently exposes to the user.

This article provides various scripts for interrogating these views to get all sorts of useful information about your database that you would otherwise have to obtain slowly, click-by-wretched-click, from the sluggish SSMS Object browser. Once you’ve built up your own snippet or template library, you’ll find it very easy to access your databases’ metadata using SQL Code.

Interrogating Information Schema and Catalog Views

Codd’s fifth Rule (no. 4) of what comprises a relational database states that there must be an active online, inline, relational catalog that is accessible to authorized users by means of their regular query language. This means that users must be able to access the database's structure (catalog) using SQL. XQuery isn’t allowed by Codd’s rule; it must the same query language that they use to access the database's data. TheINFORMATION_SCHEMA  provides a standard way of doing this for SQL-based relational databases.

Unfortunately, the standard doesn’t cover all the features in a SQL Server database. Sybase and SQL Server always provided the System tables to provide all the information that was required of a database’s structure, long before the INFORMATION_SCHEMA views became a SQL Standard. The Catalog Views, introduced in SQL Server 2005, provide a more efficient and concise way of doing this, even if one loses a bit of the feel for the underlying structure. There are many more views than actual system tables and Microsoft has been assiduous in providing simple ways of getting the metadata that you want.

Building a Snippet Library

If you are weaning yourself off dependency on the object browser of SQL Server Management Studio, you’ll need a clip library of handy routines instead. It is impossible to keep all the information in your head. I have a range of snippets, recipes and templates of SQL calls to get the information I want, many of which I present in this article. The SSMS templates are handy for this, though I’ll use SQL Prompt or AceText too, to store code snippets.

Probably my most-used snippet is one of the simplest, and it gets the actual definition of all the views, procedures and functions. This is something I keep as a template. You’ll have to change theMyObjectName for the name of the routine whose code you want.

--find the actual code for a particular stored procedure, view, function etc.

Select object_Name(object_ID),definition from sys.SQL_Modules

where object_Name(object_ID)='MyObjectName'

Sadly, it is impossible to get the build script for tables, along with all its associated objects, columns and indexes. It isn’t stored as such, though it is available via the Object Browser. If you want to get it via code, it has to be generated via SMO.

However, once you get started, there is a whole variety of things you will want to get information about what objects are associated with a given database, how many of them, who owns which objects, and so on.

Searching Schema-scoped Objects in a Database

Using a single Catalog view along with a special catalog function called OBJECTPROPERTY, we can find out the intimate details of any schema-scoped objects in the current database. Details of all schema-scoped objects are stored in the sys.objects Catalog view, from which other views such as  sys.foreign_keys, sys.check_constraints, sys.tables and sys.views  inherits. These additional views have added information that is specific to the particular type of object. There are database entities that are not classed as objects. Columns, indexes and parameters to routines, for example,  aren't classed by SQL Server as objects.

The OBJECTPROPERTY function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

Finding Tables with no Primary Keys

You’ll want to know if there tables without primary keys and why. Here is a way of getting that information from the INFORMATION_SCHEMA.tables view.

--Which of my tables don't have primary keys?

SELECT --we'll do it via information_Schema

  TheTables.Table_Catalog+'.'+TheTables.Table_Schema+'.'

                        +TheTables.Table_Name AS [tables without primary keys]

FROM

  information_Schema.tables TheTables

  LEFT OUTER JOIN information_Schema.table_constraints TheConstraints

    ON TheTables.table_Schema=TheConstraints.table_schema

       AND TheTables.table_name=TheConstraints.table_name

       AND constraint_type='PRIMARY KEY'

WHERE table_Type='BASE TABLE'

  AND constraint_name IS NULL

ORDER BY [tables without primary keys]

The following code, using a Catalog view, should give the same result as the previous code, but much more easily. The TableHasPrimaryKey property of the OBJECTPROPERTY function simply returns 1 if a primary key exists, or 0 if not.

-- you can save a lot of code by using the catalog views

-- along with the OBJECTPROPERTY() function

Select

DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'

                           +t.name  as [tables without primary keys]

FROM sys.tables t

WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0

ORDER BY [tables without primary keys]

Finding Tables with no Referential Constraints

You can, of course use almost the same query to explore many other characteristics of the tables. You’d certainly want to investigate any tables that seem to have no referential constraints, either as a key or a foreign reference.

           

--Which of my table are waifs (No Referential constraints)

SELECT

  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Waif Tables]

FROM

  sys.tables t

WHERE

  OBJECTPROPERTY(object_id, 'TableHasForeignKey')=0

  AND OBJECTPROPERTY(object_id, 'TableHasForeignRef')=0

  AND OBJECTPROPERTY(object_id, 'IsUserTable')=1

ORDER BY

  [Waif tables]

Finding Tables with no Indexes

You’d also be interested in those tables without clustered indexes and want to find out the reason why.

SELECT

  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without Clustered index]

FROM

  sys.tables t

WHERE

  OBJECTPROPERTY(object_id, 'TableHasClustIndex')=0

order by [Tables without Clustered index] 

  

And you’d scratch your head a bit if there were tables of any great size without any index at all.

SELECT

  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [Tables without any index]

FROM

  sys.tables t

WHERE

  OBJECTPROPERTY(object_id, 'TableHasIndex')=0

order by [Tables without any index] 

A one-stop View of your Table Structures

We can pull of this together in a single query against the sys.tables Catalog view to find out which objects (indexes, constraints and so on) do and don't exist on a given database. This is a handy query to get a summary of the characteristics of your tables’ structure at a quick glance.

SELECT

DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'

                                                    +t.name  AS [Qualified Name],

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasActiveFulltextIndex') = 0 

       THEN 'no' ELSE 'yes' END AS  [FT index],--Table has an active full-text index.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasCheckCnst') = 0 

       THEN 'no' ELSE 'yes' END AS  [Check Cnt],--Table has a CHECK constraint.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasClustIndex') = 0 

       THEN 'no' ELSE 'yes' END AS  [Clustered ix],--Table has a clustered index.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasDefaultCnst') = 0 

       THEN 'no' ELSE 'yes' END AS  [Default Cnt],--Table has a DEFAULT constraint.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasDeleteTrigger') = 0 

       THEN 'no' ELSE 'yes' END AS  [Delete Tgr],--Table has a DELETE trigger.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignKey') = 0 

       THEN 'no' ELSE 'yes' END AS  [FK Cnt],--Table has a FOREIGN KEY constraint.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasForeignRef') = 0 

       THEN 'no' ELSE 'yes' END AS  [FK Ref],--referenced by a FOREIGN KEY constraint.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasIdentity') = 0 

       THEN 'no' ELSE 'yes' END AS  [Identity Col],--Table has an identity column.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasIndex') = 0 

       THEN 'no' ELSE 'yes' END AS  [Any index],--Table has an index of any type.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasInsertTrigger') = 0 

       THEN 'no' ELSE 'yes' END AS  [Insert Tgr],--Object has an INSERT trigger.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasNonclustIndex') = 0 

       THEN 'no' ELSE 'yes' END AS  [nonCl Index],--Table has a nonclustered index.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 

       THEN 'no' ELSE 'yes' END AS  [Primary Key],--Table has a primary key

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasRowGuidCol') = 0 

       THEN 'no' ELSE 'yes' END AS  [ROWGUIDCOL],--ROWGUIDCOL for uniqueidentifier col

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasTextImage') = 0 

       THEN 'no' ELSE 'yes' END AS  [Has Blob],--Table has text, ntext, or image column

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasTimestamp') = 0 

       THEN 'no' ELSE 'yes' END AS  [Timestamp],--Table has a timestamp column.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasUniqueCnst') = 0 

       THEN 'no' ELSE 'yes' END AS  [Unique Cnt],--Table has a UNIQUE constraint.

  CASE WHEN OBJECTPROPERTY(object_id,'TableHasUpdateTrigger') = 0 

       THEN 'no' ELSE 'yes' END AS  [Update Tgr]--Table has an Update trigger.

FROM sys.tables t

ORDER BY [Qualified Name]

How many of each Object…

Since the OBJECTPROPERTY function generally returns either a 1 or a 0, it can be used pretty simply in order to find out not just whether there are constraints, defaults, rules or triggers on individual tables, but also how many of them there are.

--Which of my tables have constraints, defaults, rules or triggers on them? If so, then how many?

SELECT

  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name AS[Qualified_Name],

  Count(*),

  sum(OBJECTPROPERTY ( s.object_ID , 'IsPrimaryKey')) as [Pk],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsCheckCnst')) as [ChkCns],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsDefaultCnst')) as [DefCns],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsForeignKey')) as [Fk],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsConstraint')) as [Cnstrnt],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsDefault')) as [Default],

  sum(OBJECTPROPERTY ( s.object_ID , 'IsTrigger')) as [Trigger]

FROM

  sys.objects S --to get the objects

  inner JOIN sys.objects p

    --to get the parent object so as to get the name of the table

    ON s.parent_Object_ID=p.[object_ID]

WHERE

  OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0

GROUP BY

  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+p.name

Too many Indexes…

By a slightly different route, we can also find out which of our tables have the most indexes on them. Are any of them duplications? Here is a query you might use to see where the indexes might have gathered in undue numbers.

--Which of my tables have the most indexes?

SELECT TOP 10

  COUNT(*) AS [Indexes],

  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name AS [table]

FROM

  sys.indexes i

  INNER JOIN sys.objects t

    ON i.object_ID=t.object_ID

WHERE

  USER_NAME(OBJECTPROPERTY(i.object_id, 'OwnerId')) NOT LIKE 'sys%'

GROUP BY

  DB_NAME()+'.'+Object_Schema_name(t.object_ID)+'.'+t.name

ORDER BY

  COUNT(*) DESC

Seeking out Troublesome Triggers

I find triggers particularly troublesome as it is not always obvious that they are there. I’m not the only developer who has spent an hour trying to work out why the result of an update is nothing like what one was expecting, only to be struck by the thought that some crazed code-jockey has inexplicably placed an update trigger on one of your tables. Yes, there it is. The following code should winkle out these lurking problems, and much more besides.

--Which of my tables have triggers on them, and how many?

SELECT --firstly, we'll search the names of the basic objects

  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name AS [Qualified_Name],

  COUNT(*) AS [how many]

FROM

  sys.objects S --to get the objects

  INNER JOIN sys.objects p

    --to get the parent object so as to get the name of the table

    ON s.parent_Object_ID=p.[object_ID]

WHERE

  OBJECTPROPERTY ( s.object_ID , 'IsTrigger')<>0

  and OBJECTPROPERTY ( p.object_ID , 'IsTable')<>0

GROUP BY

  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+p.name

.. and from this, you can drill down to  see the sort of triggers your tables have:

SELECT

  DB_NAME()+'.'+Object_Schema_name(t.[object_ID])+'.'+t.name AS[Qualified_Name],

  case when OBJECTPROPERTY ( t.object_ID , 'HasAfterTrigger')<>0

                                         then 'yes' else 'no' end as [After],

  case when OBJECTPROPERTY ( t.object_ID , 'HasDeleteTrigger') <>0

                                         then 'yes' else 'no' end as  [Delete],

  case when OBJECTPROPERTY ( t.object_ID , 'HasInsertTrigger') <>0

                                         then 'yes' else 'no' end as  [Insert],

  case when OBJECTPROPERTY ( t.object_ID , 'HasInsteadOfTrigger') <>0

                                         then 'yes' else 'no' end as [Instead Of],

  case when OBJECTPROPERTY ( t.object_ID , 'HasUpdateTrigger ')<>0

                                         then 'yes' else 'no' end as [Update]

 FROM

 sys.tables t

Querying the Documentation in Extended Properties

Catalog queries are a powerful way of querying the documentation in order to find out more about the business rules governing the database structure. There are several useful queries that you can use if you have been sensible enough to structure your documentation, such as listing out your procedures and functions, along with a brief synopsis of how they are used and why. Here, we’ll just restrict ourselves to a useful list of all the tables that have no documentation in the extended properties. There really aren’t any other places to put your table documentation so you can be fairly sure that these tables have no documentation.

--Which tables do not have any documentation in extended properties

SELECT

  DB_NAME()+'.'+Object_Schema_name(s.[object_ID])+'.'+s.name AS [Undocumented Table]

FROM

  sys.objects s

  LEFT OUTER JOIN sys.extended_properties ep

    ON s.object_ID=ep.major_ID

       AND minor_ID=0

WHERE

  type_desc='USER_TABLE'

  AND ep.value IS NULL

Object Permissions and Owners

There are a whole variety of things you will need information about as well as the details of the database objects; lists of permissions on each object and the type of permissions they represent, for example. Here is a query that lists the database-level permissions for the users (or particular user, if the final condition that is currently commented out is used.)

 SELECT

  CASE WHEN class_desc='DATABASE' THEN DB_NAME()

       WHEN class_desc='SCHEMA' THEN SCHEMA_NAME(major_id)

       WHEN class_desc='OBJECT_OR_COLUMN' THEN OBJECT_NAME(major_id)

       WHEN class_desc='DATABASE_PRINCIPAL' THEN USER_NAME(major_id)

       WHEN class_desc='TYPE' THEN TYPE_NAME(major_id)

       ELSE 'Huh??'

  END, USER_NAME(grantee_principal_id) AS grantee,

  USER_NAME(grantor_principal_id) AS grantor, type, Permission_Name,

  State_Desc

FROM

  sys.database_permissions

WHERE

  Class_Desc IN ('DATABASE', 'SCHEMA', 'OBJECT_OR_COLUMN',

                 'DATABASE_PRINCIPAL', 'TYPE')

-- and grantee_principal_id = DATABASE_PRINCIPAL_ID('public');

A different task is to explore the ownership of the various objects in your database. The following code will make this task a lot simpler.

--find the user names of all the objects

Select [Entity Type], [Owner name], [Object Name]

from

       (

SELECT replace(SUBSTRING(v.name, 5, 31),'cns','constraint')  AS [entity type]

    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS [owner name]

    ,DB_NAME()+'.'+Object_Schema_name(o.object_ID)+'.'+o.name as [Object Name]

FROM sys.objects o

LEFT OUTER JOIN master.dbo.spt_values v--to get the type of object

            ON o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default

               AND v.type = 'O9T'

UNION

SELECT 'Type'

    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId'))

    ,DB_NAME()+'.'+Schema_name(schema_ID)+'.'+name

 FROM sys.types 

UNION

SELECT 'XML Schema Collection' 

    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id))

    ,DB_NAME()+'.'+Schema_name(xsc.schema_ID)+'.'+xsc.name

       FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s

    ON s.schema_id = xsc.schema_id

    )f

where [owner Name] not like 'sys'  

What's been recently modified then?

If you are working with others on a database, then one of the more useful bits of code you can have is the following, which tells you the date at which your database objects were last-modified. This is the full code, but generally you'll modify it slightly as you'll just want to know the twenty latest modifications or so, or maybe list all the objects modified in the past week. Sadly, it will not tell you what has been deleted!

SELECT  [Qualified_Name], Object_Type, CONVERT(CHAR(17), Created, 113),

        CONVERT(CHAR(17), Last_Modified, 113)

FROM    (SELECT --firstly, we'll search the names of the basic objects

                DB_NAME()+'.'+Object_Schema_name(s.[object_ID])

                    +'.'+COALESCE(p.name+'.', '')+s.name

                AS [Qualified_Name],

                REPLACE(SUBSTRING(v.name, 5, 31), 'cns', 'constraint')+' name'

                AS Object_Type, s.create_date AS 'Created',

                s.modify_date AS 'Last_Modified'

         FROM   sys.objects S --to get the objects

                LEFT OUTER JOIN master.dbo.spt_values v --to get the type of object

                  ON s.type=SUBSTRING(v.name, 1, 2) COLLATE database_default

                  AND v.type='O9T'

                LEFT OUTER JOIN sys.objects p --to get any parent object

                  ON s.parent_Object_ID=p.[object_ID]

         WHERE  Object_Schema_name(s.object_ID) NOT LIKE 'sys%'

         UNION ALL --now search the XML schema collection names

         SELECT DB_NAME()+'.'+name, 'XML Schema Collection name',

                create_date AS 'created', modify_date AS 'Last Modified'

         FROM   sys.xml_schema_collections

         UNION ALL

         SELECT DB_NAME()+'.'+name, LOWER(type_desc)  COLLATEdatabase_default,

                create_date AS 'created', modify_date AS 'Last Modified'

         FROM   sys.triggers

         WHERE  parent_class=0--only DDL triggers

         UNION ALL --names of CLR assemblies

         SELECT DB_NAME()+'.'+name, 'CLR Assembly', create_date AS 'created',

                modify_date AS 'Last Modified'

         FROM   sys.assemblies

         UNION ALL --almost done. We do the agent jobs too here

         SELECT DISTINCT

                'Agent'+'.'+DB_NAME()+'.'+[name]  COLLATE database_default,

                'Agent Job', date_created, date_modified

         FROM   MSDB.dbo.sysJobs Job

           INNER JOIN MSDB.dbo.sysJobSteps Step ON Job.Job_Id=Step.Job_Id

         WHERE  Database_name LIKE DB_NAME() COLLATE database_default)objects

ORDER BY Last_Modified DESC

Searching all your Databases

You can use these various routines on all databases, or on a list of databases. You can use undocumented code, of course, but a better approach would be to use yet another system catalog calledsys.Databases. You can then execute the code against all databases, collecting the result into a single table. Here is an example:

DECLARE @ii INT, --loop counter

       @iiMax INT, --loop counter upper limit

       @CurrentDatabase VARCHAR(255), --variable holding name of current database

       @command NVARCHAR(2000)--the dynamic command

DECLARE @whatWeSearch TABLE --the table of all the databases we search

  (Database_ID INT IDENTITY(1, 1),

   DatabaseName VARCHAR(255)

  )

DECLARE @Result TABLE --the result

  ([Tables Without Primary Keys] VARCHAR(255)

  )

INSERT INTO @whatWeSearch (DatabaseName)

 SELECT name FROM sys.Databases

    WHERE name NOT IN ('Master', 'TempDB', 'Model', 'MSDB')

--get all the databases we want to search

SELECT @ii=MIN(Database_ID), @iiMax=MAX(Database_ID) FROM @whatWeSearch

--and do them all one after another

WHILE @ii<=@iiMax

       BEGIN

       SELECT @CurrentDatabase=QUOTENAME(DatabaseName)

          FROM @whatWeSearch WHERE Database_ID=@ii

       SET @Command=N'Use '+@CurrentDatabase+'

       Select DB_NAME()+''.''+Object_Schema_name(t.object_ID)+''.''

                                +t.name  as [tables without primary keys]

       FROM sys.tables t

       WHERE OBJECTPROPERTY(object_id,''TableHasPrimaryKey'') = 0

       ORDER BY [tables without primary keys]'

       INSERT INTO @Result ([Tables Without Primary Keys])

              EXEC sp_executesql @Command

       SELECT @ii=@ii+1 --and on to the next database

       END

SELECT [Tables Without Primary Keys] FROM @Result

Interrogating Object Dependencies

If you are faced with the difficult task of refactoring code whilst keeping everything running reliably, one of the most useful things you can determine is the chain of dependencies of database objects. You’ll particularly need this if you are considering renaming anything in the database, changing a column in a table, moving a module, or are replacing a data type. Unfortunately, it isn’t particularly reliable.

One problem that SQL Server faces is that some entities used in an application can contain caller-dependent references, or one-part name references (e.g. they don’t specify the Schema). This can cause all sorts of problems because the binding of the referenced entity depends on the schema of the caller and so the reference cannot be determined until the code is run. Additionally, if code is stored in a string and executed, then the entities that the code is referencing cannot be recorded in the metadata.

One thing you can do, if you are checking on the dependencies of a routine (non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger) is to update its metadata. This is because the metadata for these objects, such as data types of parameters, can become outdated because of changes to their underlying objects. This is done by usingsys.sp_refreshsqlmodule, e.g.

 sys.sp_refreshsqlmodule 'dbo.ufnGetContactInformation'

Even then, the information you get back from the metadata about dependencies is to be taken with a pinch of salt. It is reasonably easy to get a list of what objects refer to a particular object, and what objects are referred to by an object. Variations of the following query will do it for you, using the SQL Server 2005 catalog view sys.sql_dependencies.

--list all the dependencies in the database. Normally you'll have a WHERE clausee

--to pick just the object you want.

SELECT

Object_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id), 'unknown')

  +COALESCE('.'+ COL_NAME(object_id, column_id), '') AS [referencer],

 Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)

  +COALESCE('.'+COL_NAME(referenced_major_id, referenced_minor_id), '') AS[Referenced]

FROM

  sys.sql_dependencies

WHERE

  class IN (0, 1) --AND referenced_major_id = OBJECT_ID('HumanResources.Employee')

ORDER BY

  COALESCE(OBJECT_NAME(object_id), 'x'),

  COALESCE(COL_NAME(object_id, column_id), 'a')

You will have spotted that what you often need is not limited to the dependent objects of the object you are re-engineering. If you are altering the behavior of the object, you will need to then need to look in turn at the objects that are dependent on these dependent objects, and so on (and watch out for mutual dependency!). In other words, you need the dependency chains.

ALTER FUNCTION DependencyChainOf (@Object_Name VARCHAR(200))

/**

 summary:   >

            The DependencyChainOf function takes as a parameter either a table

            view, function or procedure name or a column name. It works best

            with the full object name of schema.object(.column). returns a

            table that gives the dependency chain with both forward and

            backward links so that you can see what objects are likely to be

            affected by the changes you make, and what objects your object

            is referencing..

 Revisions:

          - version: 1

               Modification: Created Table-balued function

               Author: Phil Factor

               Date:  01/03/2010          

          - version: 2

               Modification: added catch for mutual dependency

               Author: Phil Factor

               Date:  02/03/2010  

example:

       - code:

              Select distinct * from DependencyChainOf('VEmployee')

                      order by The_level,TheName

       - code:

              EXEC sys.sp_refreshsqlmodule 'MyProc1'

              Select distinct * from DependencyChainOf('MyTable')

                     order by y y y y y y The_level,TheName 

  **/

RETURNS  @Referenced TABLE

  (

   TheName VARCHAR(200), The_Object_ID BIGINT, Column_ID INT,

   Class INT, The_Level INT

  )

 AS

 BEGIN

--identify the object or  column

--get the referencing entity

INSERT INTO

  @referenced (The_Object_ID, Column_ID, Class, The_Level)

  SELECT TOP 1

    object_ID, Column_ID, class, 1

  FROM

    (SELECT

       Object_Schema_name(object_id)+'.'+COALESCE(OBJECT_NAME(object_id),'unknown')

          +COALESCE('.'+COL_NAME(object_id, column_id), '') AS [name],d.object_ID,

       d.column_ID, class

     FROM sys.sql_dependencies d

    ) names

  WHERE

    CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1

              OR OBJECT_NAME([Object_ID])=@Object_Name

IF NOT EXISTS ( SELECT 1 FROM @referenced )

  INSERT INTO

    @referenced   (The_Object_ID, Column_ID, Class, The_Level)

    SELECT TOP 1 object_ID, Column_ID, class, 1

    FROM

      (SELECT

       Object_Schema_name(referenced_major_id)+'.'+OBJECT_NAME(referenced_major_id)

              +COALESCE('.'+COL_NAME(referenced_major_id,referenced_minor_id), '') AS [name],

        d.Referenced_Major_ID AS [object_ID],

        d.Referenced_Minor_ID AS [column_ID], class

       FROM

        sys.sql_dependencies d

        ) names

    WHERE

      CHARINDEX(REVERSE(@Object_Name), REVERSE(names.name))=1

      OR OBJECT_NAME([Object_ID])=@Object_Name

DECLARE  @Currentlevel INT, @RowCount INT

SELECT  @Currentlevel=1, @Rowcount=1

WHILE @Rowcount>0  AND @currentLevel<50--guard against mutual dependency

  BEGIN

    INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)

      SELECT Referenced_Major_ID, Referenced_Minor_ID, d.class, The_Level+1

      FROM @referenced r

        INNER JOIN sys.sql_dependencies d

          ON The_Object_ID=object_ID

             --AND r.column_ID=d.Column_ID

             AND r.class=d.Class

             AND @Currentlevel=The_Level

    SELECT @rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel+1

  END

SELECT @Currentlevel=1, @Rowcount=1

WHILE @Rowcount>0 AND @currentLevel>-50--guard against mutual dependency

  BEGIN

    INSERT INTO @referenced (The_Object_ID, Column_ID, Class, The_Level)

      SELECT Object_ID, d.column_ID, d.class, The_Level-1

      FROM

        @referenced r

        INNER JOIN sys.sql_dependencies d

          ON The_Object_ID=Referenced_Major_ID

             --AND r.column_ID=d.Referenced_Major_ID

             AND r.class=d.Class

             AND @Currentlevel=The_Level

    SELECT

      @rowcount=@@Rowcount, @CurrentLevel=@CurrentLevel-1

  END

UPDATE @Referenced SET TheName=

   DB_NAME()+'.'+Object_Schema_name(The_object_ID)+'.'+OBJECT_NAME(The_object_ID)

              +COALESCE('.'+COL_NAME(The_object_ID, column_id), '')

  RETURN

 END  

It's worth noting that in SQL Server 2008, you would use the sys.sql_expression_dependenciestable, which has a much improved way of working out dependencies. There is a very full discussion, with example code, here at Reporting SQL Dependencies.

Summary

With the various scripts, suggestions and illustrations in this article, I hope I’ve given you a taste for using the Catalog, or Information Schema, views for getting all sorts of useful information about the objects in your databases, and of the dependencies that exist between.

Some of this information is available from the SSMS Object browser but it is slow going. Once you’ve built up your own snippet or template library, you’ll find it quicker and easier to take the Spartan approach, and search your databases’ catalog using SQL.