全テーブル、全ビュー、全ストアドプロシージャの拡張プロパティを一括削除
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文を組み立てていますが、テーブル、ビュー、ストアドプロシージャで少し異なります。
また、オブジェクトへの拡張プロパティとカラムへの拡張プロパティでは構文が違うので、そこを考慮したつくりになってます。
| SQL Server | 00:07 | comments (0) | trackback (0) |
コメント
コメントする




この記事のトラックバックURL
http://kodawari.sakura.ne.jp/blogn/tb.php/90
トラックバック


  
CALENDAR
S M T W T F S
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31       
<<   10 - 2021   >>
LOGIN
現在のモード: ゲストモード
USER ID:
PASS:
CATEGORIES
ARCHIVES
COMMENTS
TRACBACK
PROFILE
OTHERS
POWERED BY
POWERED BY
ぶろぐん