ステップ数を取得する WSH スクリプト。SQL Server の DB オブジェクト（ビュー、ストアド プロシージャ、ユーザー定義関数、トリガー）のステップ数を取得。

SQL Server 2008

弊社の SQL Server コンサルティング サービスでは、最初に DB 内のオブジェクト（ビュー、ストアド プロシージャ、ユーザー定義関数、トリガー）のステップ数をお伺いすることが多いので、そのときに利用しているスクリプトです。
多くの環境で実行できるようにするために WSH スクリプト（VBScript）として作成しています（SQL Server 2000 対応版は、下のほうに添付しています）。


■ 説明
SQL Server 内に作成したデータベース オブジェクト（ビュー、ストアド プロシージャ、ユーザー定義関数、トリガー）のステップ数を取得する WSH スクリプト（VBScirpt）。
以下のコードをメモ帳へ貼り付けて、「.vbs」という拡張子で保存するか、添付のファイルをダウンロードすれば、各種オブジェクトのステップ数を確認することができます。

ファイルのダウンロード： GetStepCountsW.vbs 直

' ------------------------------------------------------------
' 接続先の SQL Server 名とデータベース名、認証方法、出力ファイル名の指定
' ------------------------------------------------------------
Dim srvName, dbName, credential, filePath
srvName = InputBox("SQL Server の名前を入力してください", ,"localhost")
dbName = InputBox("データベースの名前を入力してください", ,"Northwind")
credential = InputBox("認証方法を入力してください。" & vbNewLine _
		& "SQL Server 認証の場合は" & vbNewLine _
		& " User ID=sa;Password=〜; 形式" , , _
		  "Integrated Security=SSPI;")
filePath = InputBox("出力結果を格納するファイルの名前を入力してください" _
		    , ,"D:\kekka.csv")

' 接続文字列
Dim cnstr
cnstr = "Provider=SQLOLEDB;Server=" & srvName _
	& ";Database=" & dbName & ";" & credential

' 実行する SQL
' DB 内オブジェクト（ビュー、ストアド、UDF、トリガー）の一覧を取得
Dim selectStr
selectStr = "SELECT " & vbCrLf _
   & "  SCHEMA_NAME(schema_id) As schName, name, type" & vbCrLf _
   & " FROM sys.objects" & vbCrLf _
   & " WHERE type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR')" & vbCrLf _
   & "  AND NOT (name LIKE 'dt_%' OR name LIKE '%_%diagram%')" & vbCrLf _
   & "   ORDER BY type, name"

' 出力ファイルの作成。実行ごとに上書き保存
Dim fso, f
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set f = fso.CreateTextFile(filePath, True)
f.WriteLine ("objType, xtype, objName, stepCount")

' ADO で実行
Dim cn, rsObj
Set cn = WScript.CreateObject("ADODB.Connection")
cn.Open cnstr
Set rsObj = WScript.CreateObject("ADODB.Recordset")
rsObj.CursorLocation = 3  'adUseClient

' 総ステップ数、総オブジェクト数の計算用の配列
' 0：total、1：View。2：SP、3：UDF、4：Trigger
Dim totalStepCount(4), totalObjCount(4)
For i = 0 to 4
	totalStepCount(i) = 0
	totalObjCount(i) = 0
Next

Dim schName, objName, objType, xtype

' オブジェクトごとにループ処理
rsObj.Open selectStr, cn
Do Until rsObj.EOF
	schName = rsObj("schName")
	objName = rsObj("name")
	objType = ""
	xtype = rsObj("type")
	xtype = Trim(xtype)

	Dim rsTxt, stepCount
	Set rsTxt = cn.Execute( _
		     "sp_helptext '" & schName & "." & objName & "'")
	stepCount = 0
	Do Until rsTxt.EOF
		'WScript.Echo rsTxt(0)
		stepCount = stepCount + 1
		rsTxt.MoveNext
	Loop
	rsTxt.Close
	Set rsTxt = Nothing
	'WScript.Echo stepCount

	' 総ステップ数、総オブジェクト数の計算
	totalStepCount(0) = totalStepCount(0) + stepCount
	totalObjCount(0) = totalObjCount(0) + 1

	Select Case xtype
	Case "V"
		objType = "View"
		totalStepCount(1) = totalStepCount(1) + stepCount
		totalObjCount(1) = totalObjCount(1) + 1
	Case "P"
		objType = "SP"
		totalStepCount(2) = totalStepCount(2) + stepCount
		totalObjCount(2) = totalObjCount(2) + 1
	Case "FN", "IF", "TF"
		objType = "UDF"
		totalStepCount(3) = totalStepCount(3) + stepCount
		totalObjCount(3) = totalObjCount(3) + 1
	Case "TR"
		objType = "Trigger"
		totalStepCount(4) = totalStepCount(4) + stepCount
		totalObjCount(4) = totalObjCount(4) + 1
	End Select

	f.WriteLine (objType & "," & xtype & "," _
			& objName & "," & stepCount)
	rsObj.MoveNext
Loop

' Close 処理
f.Close
Set f = Nothing
Set fso = Nothing
cn.Close
Set rsObj = Nothing
Set cn = Nothing

WScript.Echo "実行完了" & vbNewLine _
	& vbNewLine & "総オブジェクト数： " & totalObjCount(0) _
	& ", 総ステップ数： " & totalStepCount(0) _
	& vbNewLine & "　ビュー数： " & vbTab & totalObjCount(1) _
	& ", 総ステップ数： " & totalStepCount(1) _
	& vbNewLine & "　ストアド数： " & vbTab & totalObjCount(2) _
	& ", 総ステップ数： " & totalStepCount(2) _
	& vbNewLine & "　UDF 数： " & vbTab & totalObjCount(3) _
	& ", 総ステップ数： " & totalStepCount(3) _
	& vbNewLine & "　Trigger 数： " & vbTab & totalObjCount(4) _
	& ", 総ステップ数： " & totalStepCount(4)


■ 実行方法
このスクリプトは、「.vbs」ファイルをダブルクリックするか、
コマンドプロンプトから「CScript パス\〜.vbs」のように入力して実行することができます。

実行すると、InputBox が 4個表示されて、

  • SQL Server の名前（既定は localhost）
  • データベース名（既定は Northwind）
  • 認証方法（既定は Windows 認証）
  • 出力ファイル名とパス（既定は D:\kekka.csv）

の入力が求められるので、適宜変更して実行することができます。
CScript で実行した場合は、次のように総ステップ数（サマリ結果）をコマンドプロンプト上で確認することができます。

実行完了

総オブジェクト数： 26, 総ステップ数： 181
　ビュー数：    16, 総ステップ数： 103
　ストアド数：  7, 総ステップ数： 60
　UDF 数：      3, 総ステップ数： 18
　Trigger 数：  0, 総ステップ数： 0

また、InputBox の 4個目で、出力ファイル名で指定したファイル（既定は D:\kekka.csv）に、以下のように CSV 形式でオブジェクトごとのステップ数が出力されています。

objType, xtype, objName, stepCount
UDF,FN,MULTI,6
UDF,FN,TRIM,6
UDF,FN,tsql_trim,6
SP,P,CustOrderHist,7
SP,P,CustOrdersDetail,9
        ：

これを Excel で開けば、簡単にまとめられます。


過去の弊社のお客様環境での実行結果例（総ステップ数 252,603。約 25万。SP が約 1,700個）

総オブジェクト数： 2167, 総ステップ数： 252603
　ビュー数：    413, 総ステップ数： 16157
　ストアド数：  1691, 総ステップ数： 227146
　UDF 数：      63, 総ステップ数： 9300
　Trigger 数：  0, 総ステップ数： 0


■ スクリプトの簡単な説明
このスクリプトでは、sys.objects を SELECT してオブジェクトの一覧を取得して、sp_helptext でオブジェクトの定義（CREATE VIEW 〜 や CREATE PROCEDURE 〜 など）を取得しています。オブジェクト定義の行数をカウントして、それをステップ数（stepCount）としています（冒頭の CREATE VIEW や AS などもステップ数に含めてカウントしています）。
sys.objects を SELECT しているクエリは、以下のとおりです。

SELECT  
    SCHEMA_NAME(schema_id) As schName, name, type 
   FROM sys.objects 
   WHERE type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR') 
    AND NOT (name LIKE 'dt_%' OR name LIKE '%_%diagram%') 
     ORDER BY type, name

sys.objects は、SQL Server 2005 以降から利用できるシステム カタログ ビューなので、SQL Server 2000 以前を利用している場合は、次のように SELECT ステートメントを変更する必要があります。

SELECT  
    USER_NAME(uid) As schName, name, xtype As type
   FROM sysobjects 
   WHERE type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR') 
    AND NOT (name LIKE 'dt_%' OR name LIKE '%_%diagram%') 
     ORDER BY type, name

SQL Server 2000 版の .vbs ファイルのダウンロード： GetStepCountsW_sysobject版.vbs 直


■ オブジェクトの種類（type／xtype）の説明
sys.objects を SELECT しているオブジェクトでは、次のようにオブジェクトの種類を指定しています。

type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR')

それぞれの意味は、次のとおりです（SQL CLR（アセンブリ）オブジェクトを対象外としています）。

・ビュー
V = ビュー

・ストアド プロシージャ
P = SQL ストアド プロシージャ
対象外： PC = アセンブリ (CLR) ストアド プロシージャ

・UDF（ユーザー定義関数）
FN = SQL スカラー関数
IF = SQL インライン テーブル値関数
TF = SQL テーブル値関数
対象外： AF = 集計関数 (CLR)
対象外： FS = アセンブリ (CLR) スカラー関数
対象外： FT = アセンブリ (CLR) テーブル値関数

・トリガー
TR = SQL DML トリガー
対象外： TA = アセンブリ (CLR) DML トリガー


オブジェクトの種類の詳細は、オンライン ブックの sys.objects のところに記載されています。
http://msdn.microsoft.com/ja-jp/library/ms190324.aspx


■ その他
SQL Server 2008 以降からは、オブジェクトの依存関係を簡単に取得できるようになったので、それを組み合わせることで、特定の夜間バッチや日中バッチなどに関連するオブジェクトのみのステップ数なども計算することができます。