プログラマが主に少女漫画を語る

SQLServerで既存テーブルのCREATE文を生成すると、通常のCREATE文に続いて下記のようなEXEC文が書き出されることがあります。

EXEC sys.sp_addextendedproperty @name=N'MS_AggregateType', @value=-1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'テーブル名', @level2type=N'COLUMN',@level2name=N'カラム名'

これは拡張プロパティと言われており、データ型や主キー、リレーション以外の固有設定のためにあります。

しかし、私の経験から言うといらないことが多いです。
いつの間にか設定されていることもあり、別サーバにリストアする時などは正直言って邪魔。

というわけで、あるデータベース内の拡張プロパティを一気に削除するスクリプトを作ってみました。
内容は以下で。

DECLARE @name VARCHAR(100),
@TableName VARCHAR(100),
@ColumnName VARCHAR(100),
@type_desc VARCHAR(100)

DECLARE Cur CURSOR FOR

SELECT ep.name, t.name AS [TableName], c.name AS [ColumnName], type_desc
FROM sys.extended_properties AS ep
LEFT JOIN sys.objects AS t ON ep.major_id = t.object_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
ORDER BY t.name

OPEN Cur
FETCH NEXT FROM Cur INTO @name, @TableName, @ColumnName, @type_desc

WHILE (@@FETCH_STATUS <> -1)
BEGIN

--拡張プロパティ削除用exec文の組み立て
IF @type_desc = 'USER_TABLE'

SET @type_desc = 'TABLE'

ELSE IF @type_desc = 'SQL_STORED_PROCEDURE'

SET @type_desc = 'PROCEDURE'

IF @name <> 'microsoft_database_tools_support'
BEGIN

IF @ColumnName<>''

PRINT 'EXEC sys.sp_dropextendedproperty @name=N'''+@name+''', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N'''+@type_desc+''',@level1name=N'''+@TableName+''', @level2type=N''COLUMN'',@level2name=N'''+@ColumnName+''''

ELSE

PRINT 'EXEC sys.sp_dropextendedproperty @name=N'''+@name+''', @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N'''+@type_desc+''',@level1name=N'''+@TableName+''''

END

FETCH NEXT FROM Cur INTO @name, @TableName, @ColumnName, @type_desc

END
CLOSE Cur
DEALLOCATE Cur

実行すれば全テーブル、全ビュー、全ストアドプロシージャの拡張プロパティが削除するEXEC文が生成されます。
コピーしてクエリで実行させてください。

EXEC文を実行じゃなく生成としているのは、必要な拡張プロパティまでいきなり消してしまうと困るからです。

当然ですが、使用は自己責任で。

＜仕組み＞
既存の拡張プロパティはsys.extended_propertiesに収められているので、それをカーソルを使って取り出しています。
拡張プロパティを削除するEXEC文にはテーブルなどオブジェクト名とカラム名も必要なので、sys.objectsとsys.columnsをJOINさせてます。

WHILE文の中ではEXEC文を組み立てていますが、テーブル、ビュー、ストアドプロシージャで少し異なります。
また、オブジェクトへの拡張プロパティとカラムへの拡張プロパティでは構文が違うので、そこを考慮したつくりになってます。

コメント受付を停止します。海外からスパム投稿が多いため。

http://kodawari.sakura.ne.jp/blogn/tb.php/90