PEBLOG

特定スキーマ変更用SQL抽出

特定のスキーマを変更するSQLを作るSQL

SELECT
	O.type as [Object type],
	O.type_desc as [Object type_desc],
	S.name as [Schema name],
	O.name as [Object name],
    'alter schema (変更後スキーマ名) transfer ' + S.name + '.' + O.name as [alter SQL]
FROM
    sys.objects O
    INNER JOIN
    sys.schemas S ON
        O.schema_id = S.schema_id
    AND S.name = '（変更前スキーマ名）'
--  WHERE O.type = 'U' -- オブジェクトタイプで絞りたい時