昨日、開発とテストに使用されるライブラリが同じではなく、ストアド プロシージャが頻繁に更新されることにイライラしました。場合によっては、テーブル構造さえも変更されます。
これは多くの問題を引き起こします。しばらくの間、具体的な問題が何なのかわからず、非常に恥ずかしくなりましたが、テストと実装を容易にするために 2 つのライブラリの違いを比較するプログラムを突然書きたくなりました。
今朝、打ち合わせをしながら書いていて、
主に次の2つのSQL文で
昼にテストに合格しました。
クエリ ライブラリ内のテーブルとビューの構造と列属性が異なる SQL は次のとおりです。
select A. [name] as TableName,
B. [名前] を Colname として、
B.xタイプ、
B.xusertype、
B.長さ、
B.コリッド、
B.cデフォルト、
B.ドメイン、
B.番号、
B.オフセット、
B.ステータス、
B型、
B.ユーザータイプ、
B.prec、
B.スケール、
B. 計算され、
B.イソウパラム、
B.は無効化可能です、
C.COLUMN_DEFAULT、
dbo.fnIsColumnPrimaryKey(B. [ID], B. [名前]) として PKey
sysobjects A、syscolumns B、INFORMATION_SCHEMA .COLUMNS C から
ここで、a.id = B.id
そして A.xtype in ('u', 'v')
および A. 名前 = C.TABLE_NAME
および B. 名前 = C.COLUMN_NAME
A.[ID]、B.[名前]の順
///////
各列の意味は以下の通りです
name sysname 列名またはプロシージャパラメータの名前。
id int この列が属するテーブル オブジェクト ID、またはこのパラメータに関連付けられたストアド プロシージャ ID。
xtype tinyint systypes の物理ストレージ タイプ。
typestat tinyint 内部使用のみ。
xusertype smallint 拡張ユーザー定義データ型 ID。
length smallint systype の物理ストレージの最大長。
xprec tinyint 内部使用のみ。
xscale tinyint 内部使用のみ。
colid smallint 列またはパラメータ ID。
xoffset smallint 内部使用のみ。
bitpos tinyint 内部使用のみ。
予約済み tinyint 内部使用のみ。
Colstat smallint 内部使用のみ。
cdefault int この列のデフォルト値 ID。
Domain int この列のルールまたは CHECK 制約 ID。
number smallint プロセスをグループ化した場合のサブプロセス番号 (0 は非プロセス項目を示します)。
colorder smallint 内部使用のみ。
autoval varbinary(255) 内部使用のみ。
offset smallint この列が配置されている行のオフセットは、負の場合は可変長行を示します。
status tinyint 列またはパラメータの属性を記述するビットマップ:
0x08 = 列では NULL 値が許可されます。
0x10 = ANSI パディングは、varchar 列または varbinary 列を追加するときに有効になります。 varchar 列の末尾のスペースと varbinary 列の末尾のゼロを保持します。
0x40 = パラメータは OUTPUT パラメータです。
0x80 = 列は ID 列です。
type tinyint systypes の物理ストレージ タイプ。
usertype smallint systypes のユーザー定義のデータ型 ID。
printfmt varchar(255) 内部使用のみ。
prec smallint この列の精度レベル。
scale int この列の小数点以下の桁数。
iscomputed int 列が計算されたかどうかを示すフラグ。
0 = 計算されません。
1 = 計算済み。
isoutparam int は、プロシージャ パラメータが出力パラメータかどうかを示します。
1 = 真。
0 = 偽。
isnullable int は、列が null 値を許可するかどうかを示します。
1 = 真。
0 = 偽。
COLUMN_DEFAULT デフォルト値
PKey 主キー。
////////
次の関数が使用されます。
------------------------
CREATE FUNCTION dbo.fnIsColumnPrimaryKey(@sTableID int, @nColumnName varchar(128))
--alter FUNCTION dbo.fnIsColumnPrimaryKey(@sTableName varchar(128), @nColumnName varchar(128))
ビットを返す
として
始める
DECLARE @nTableID int,
@nIndexID int、
@i int
SET @nTableID = @sTableID--OBJECT_ID(@sTableName)
SELECT @nIndexID = indid
システムインデックスから
WHERE id = @nTableID
AND indid 1 と 254 の間
AND (ステータス & 2048) = 2048
@nIndexID が Null の場合
リターン0
IF @n列名 IN
(SELECT sc.[名前]
FROM sysindexkeys sik
INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid
WHERE sik.id = @nTableID
かつ sik.indid = @nIndexID)
始める
リターン1
終了
リターン 0
終わり
------------------------
この SQL ステートメントを通じて、データベース内のすべてのテーブルの列属性を確認できます。
この SQL 文をそれぞれ 2 回実行することで、2 つのライブラリの列の属性を取得して比較できます。
-------------------------------------------------- ---------------
ストアド プロシージャと関数の場合は、次の SQL ステートメントを使用します。
select * from sysobjects where xtype in ('P','TF','IF','FN') order by [名前]
すべてのストアド プロシージャ名と関数名をクエリできます。ループクエリの結果、
各ループでは、関数またはストアド プロシージャの名前が文字列 SpobjectName に格納され、ストアド プロシージャが呼び出されます。 sp_helptext は次のとおりです。
exec sp_helptext '" +SpobjectName+"'
SpobjectName ストアド プロシージャ内のテキスト コンテンツを含むテーブルを返します。ストアド プロシージャのテキストの 1 行は、テーブルの 1 行に対応します。
exec sp_helptext を使用してすべての SP と関数を実行し、すべてのストアド プロシージャと関数のコードを取得します。
結果を表にまとめて、類似点と相違点を比較するだけです。
参考資料: SQL Server Books Online
<データベース内のストアド プロシージャの自動生成>( http://www.vckbase.com/document/viewdoc/?id=1111 )
ここでは、テーブルとビュー、およびストアド プロシージャと関数の列属性のみを比較します。
比較テーブル間の制約関係は実装されていません。
後で追加されます。