SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

拡張プロパティを削除するためのクエリ

without comments

SQL Server の各種オブジェクトには拡張プロパティを設定することができます。
このプロパティを説明として自由に情報を入力することができるのですが、Bacpac を作る場合にはこの拡張プロパティが設定されていると作成することができません。

すべての拡張プロパティを削除するわけではありませんが、AdventureWorks で設定されている拡張プロパティの大半を削除するためのクエリを書いてみました。結構適当です。。。。

DECLARE @prop_name sysname
DECLARE @schema_name sysname
DECLARE @name sysname
DECLARE @name2 sysname
DECLARE @sql nvarchar(max)
-- データベースの拡張プロパティの削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT name FROM sys.extended_properties where class = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'USE [master];EXEC ' + QUOTENAME(DB_NAME()) + N'.sys.sp_dropextendedproperty @name=N''' + @prop_name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- スキーマ拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(s.schema_id),  ep.name
FROM sys.extended_properties ep
INNER JOIN sys.schemas s ON s.schema_id = ep.major_id
WHERE ep.class = 3
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ファイルグループの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT f.name,  ep.name
FROM sys.extended_properties ep
INNER JOIN sys.filegroups f ON f.data_space_id = ep.major_id
WHERE ep.class = 20
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''FILEGROUP'',' +
	N'@level0name=N''' + @name + ''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ファイルの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT f.name, sf.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.filegroups f ON f.data_space_id = ep.major_id
INNER JOIN sys.sysfiles sf ON sf.groupid = f.data_space_id
WHERE ep.class = 22
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''FILEGROUP'',' +
	N'@level0name=N''' + @name + ''', ' +
	N'@level1type=N''Logical File Name'',' +
	N'@level1name=N''' + @name2 + ''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- XML スキーマコレクションの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(x.schema_id), x.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.xml_schema_collections x ON x.xml_collection_id = ep.major_id
WHERE ep.class = 10
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''XML SCHEMA COLLECTION'',' +
	N'@level1name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- テーブルの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(t.schema_id), OBJECT_NAME(t.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.tables t ON t.object_id = ep.major_id
WHERE ep.class = 1 AND ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''TABLE'',' +
	N'@level1name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ビューの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(v.schema_id), OBJECT_NAME(v.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.views v ON v.object_id = ep.major_id
WHERE ep.class = 1 AND ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''VIEW'',' +
	N'@level1name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- インデックスの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), i.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id AND o.type IN ('U')
INNER JOIN sys.indexes i ON i.object_id = ep.major_id AND i.index_id = ep.minor_id
WHERE ep.class = 7
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''TABLE'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''INDEX'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ビューのインデックスの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), i.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id AND o.type IN ('V')
INNER JOIN sys.indexes i ON i.object_id = ep.major_id AND i.index_id = ep.minor_id
WHERE ep.class = 7
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''VIEW'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''INDEX'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- 関数の拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id AND o.type IN ('FN', 'TF')
WHERE ep.class = 1 AND ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''FUNCTION'',' +
	N'@level1name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- 関数のパラメーターの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), p.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id AND o.type IN ('FN', 'TF')
INNER JOIN sys.parameters p ON p.object_id = ep.major_id AND p.parameter_id = ep.minor_id
WHERE ep.class = 2
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''FUNCTION'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''PARAMETER'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ストアドプロシージャの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(p.schema_id), OBJECT_NAME(p.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.procedures p ON p.object_id = ep.major_id
WHERE ep.class = 1 AND ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''PROCEDURE'',' +
	N'@level1name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- ストアドプロシージャのパラメーターの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), p.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o  ON o.object_id = ep.major_id AND o.type = 'P'
INNER JOIN sys.parameters p ON p.object_id = ep.major_id AND p.parameter_id = ep.minor_id
WHERE ep.class = 2
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', '+
	N'@level1type=N''PROCEDURE'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''PARAMETER'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- DDLトリガーの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT t.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.triggers t ON t.object_id = ep.major_id AND t.parent_class = 0
WHERE ep.class = 1 AND ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @name, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''TRIGGER'',' +
	N'@level0name=N''' + @name + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @name, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- DML トリガーの拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), p.name, OBJECT_NAME(o.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id AND o.type = 'TR'
INNER JOIN sys.objects p on o.parent_object_id = p.object_id
WHERE ep.class = 1
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + 	@prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', ' +
	N'@level1type=N''TABLE'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''TRIGGER'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- 制約の拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(c.schema_id), OBJECT_NAME(c.parent_object_id), OBJECT_NAME(c.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.check_constraints c ON c.object_id = ep.major_id
WHERE ep.class = 1 and ep.minor_id = 0
UNION
SELECT SCHEMA_NAME(c.schema_id), OBJECT_NAME(c.parent_object_id), OBJECT_NAME(c.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.default_constraints c ON c.object_id = ep.major_id
WHERE ep.class = 1 and ep.minor_id = 0
UNION
SELECT SCHEMA_NAME(c.schema_id), OBJECT_NAME(c.parent_object_id), OBJECT_NAME(c.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.foreign_keys c ON c.object_id = ep.major_id
WHERE ep.class = 1 and ep.minor_id = 0
UNION
SELECT SCHEMA_NAME(c.schema_id), OBJECT_NAME(c.parent_object_id), OBJECT_NAME(c.object_id), ep.name
FROM sys.extended_properties ep
INNER JOIN sys.key_constraints c ON c.object_id = ep.major_id
WHERE ep.class = 1 and ep.minor_id = 0
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + @prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', ' +
	N'@level1type=N''TABLE'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''CONSTRAINT'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR
-- 列の拡張プロパティを削除
DECLARE PROP_CURSOR CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id), c.name, ep.name
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON o.object_id = ep.major_id
INNER JOIN sys.columns c ON c.object_id = o.object_id AND c.column_id = ep.minor_id
WHERE ep.class = 1
OPEN PROP_CURSOR
FETCH NEXT FROM PROP_CURSOR
INTO @schema_name, @name, @name2, @prop_name
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = N'EXEC sys.sp_dropextendedproperty ' +
	N'@name=N''' + 	@prop_name + N''', ' +
	N'@level0type=N''SCHEMA'',' +
	N'@level0name=N''' + @schema_name + ''', ' +
	N'@level1type=N''TABLE'',' +
	N'@level1name=N''' + @name + N''', ' +
	N'@level2type=N''COLUMN'',' +
	N'@level2name=N''' + @name2 + N''''
	EXEC (@sql)
	FETCH NEXT FROM PROP_CURSOR
	INTO @schema_name, @name, @name2, @prop_name
END
CLOSE PROP_CURSOR
DEALLOCATE PROP_CURSOR

Written by Masayuki.Ozawa

1月 4th, 2014 at 6:32 pm

Posted in SQL Server

Tagged with

Leave a Reply