2016-10-06 20:27:38 (Thu);
オブジェクト抽出SQLまとめ
設計や移行等で何かと利用するSQLメモ
全てのテーブルのカウントSQLを作成。 最後の union all だけ消して使う。
select 'select ''' + name + ''',count(*) from ' + name + ' union all'
from sys.sysobjects
where xtype = 'U'
order by name
カラムの定義情報を取得
Thanks. devlights.hatenablog.com/entry/20090911/p1
※素晴らしい出来なのでお借りしました。
SELECT
DB_NAME() AS db_name
,SCHEMA_NAME(tables.schema_id) AS schema_name
,tables.name AS table_name
,columns.name AS column_name
,columns.column_id AS ordinal_position
,CAST(CASE WHEN pk_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_primary_key
,pk_cols.constraint_name AS pk_constraint_name
,pk_cols.key_ordinal AS pk_key_ordinal
,CAST(CASE WHEN uq_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_unique_key
,uq_cols.constraint_name AS uq_constraint_name
,uq_cols.key_ordinal AS uq_key_ordinal
,TYPE_NAME(columns.system_type_id) AS column_data_type
,defaultConstraints.definition AS column_default
,columns.is_nullable AS is_nullable
,extProp.value AS column_comment
,CASE
WHEN TYPE_NAME(columns.system_type_id) IN ('int', 'tinyint','bigint','smalldatetime','datetime2','bit') THEN NULL
WHEN TYPE_NAME(columns.system_type_id) IN ('nvarchar', 'nchar') THEN columns.max_length / 2
WHEN columns.precision = 0 THEN columns.max_length
ELSE columns.precision END AS max_length
,CASE WHEN columns.scale = 0 THEN NULL ELSE columns.scale END AS scale
,columns.max_length AS byte_size
,columns.collation_name AS collation_name
FROM
sys.tables tables
INNER JOIN sys.columns columns
ON
tables.object_id = columns.object_id
/* DEFAULT制約定義を取得する為に以下の二つのテーブルを外部結合 */
LEFT OUTER JOIN sys.sysconstraints constraints
ON
columns.object_id = constraints.id
AND columns.column_id = constraints.colid
/* DEFAULT制約を表す疑似ビットマスク値 (マスクしていないと取得できない場合があるとのこと. (thanks murasukeさん) */
AND (constraints.status & 2069) = 2069
LEFT OUTER JOIN sys.default_constraints defaultConstraints
ON
constraints.constid = defaultConstraints.object_id
AND tables.schema_id = defaultConstraints.schema_id
/* コメントデータは拡張プロパティシステムビューに存在する */
LEFT OUTER JOIN sys.extended_properties extProp
ON
/* カラムの場合、classの値は常に1 (OBJECT_OR_COLUMN) */
extProp.class = 1
AND columns.object_id = extProp.major_id
AND columns.column_id = extProp.minor_id
/* プライマリーキーの情報を取得する為に以下の情報を外部結合 */
LEFT OUTER JOIN (
SELECT
key_const.name AS constraint_name
,idx_cols.key_ordinal AS key_ordinal
,cols.name AS col_name
,cols.object_id AS col_object_id
,cols.column_id AS col_column_id
FROM
sys.tables tbls
/* PKの情報を結合 */
INNER JOIN sys.key_constraints key_const
ON
tbls.object_id = key_const.parent_object_id
AND key_const.type = 'PK'
/* 対応するインデックス情報からカラムと特定 */
INNER JOIN sys.index_columns idx_cols
ON
key_const.parent_object_id = idx_cols.object_id
AND key_const.unique_index_id = idx_cols.index_id
INNER JOIN sys.columns cols
ON
idx_cols.object_id = cols.object_id
AND idx_cols.column_id = cols.column_id
) pk_cols
ON
columns.object_id = pk_cols.col_object_id
AND columns.column_id = pk_cols.col_column_id
/* ユニークキーの情報を取得する為に以下の情報を外部結合 */
LEFT OUTER JOIN (
SELECT
key_const.name AS constraint_name
,idx_cols.key_ordinal AS key_ordinal
,cols.name AS col_name
,cols.object_id AS col_object_id
,cols.column_id AS col_column_id
FROM
sys.tables tbls
/* UQ(ユニークキー)の情報を結合 */
INNER JOIN sys.key_constraints key_const
ON
tbls.object_id = key_const.parent_object_id
AND key_const.type = 'UQ'
/* 対応するインデックス情報からカラムと特定 */
INNER JOIN sys.index_columns idx_cols
ON
key_const.parent_object_id = idx_cols.object_id
AND key_const.unique_index_id = idx_cols.index_id
INNER JOIN sys.columns cols
ON
idx_cols.object_id = cols.object_id
AND idx_cols.column_id = cols.column_id
) uq_cols
ON
columns.object_id = uq_cols.col_object_id
AND columns.column_id = uq_cols.col_column_id
ORDER BY
db_name, schema_name, table_name, columns.column_id
プライマリーキー、ユニークキー一覧
SELECT
tbls.name AS table_name
,key_const.type AS key_type
,key_const.name AS constraint_name
,idx_cols.key_ordinal AS key_ordinal
,cols.name AS col_name
FROM
sys.tables tbls
INNER JOIN sys.key_constraints key_const
ON tbls.object_id = key_const.parent_object_id
INNER JOIN sys.index_columns idx_cols
ON key_const.parent_object_id = idx_cols.object_id
AND key_const.unique_index_id = idx_cols.index_id
INNER JOIN sys.columns cols
ON idx_cols.object_id = cols.object_id
AND idx_cols.column_id = cols.column_id
WHERE
key_const.type in ('PK','UK')
ORDER BY
tbls.object_id, idx_cols.key_ordinal, idx_cols.column_id
全インデックス
SELECT
idx.name AS index_name
,obj.name AS table_name
,col.name AS column_name
FROM
sysindexkeys keys
INNER JOIN sysobjects obj
ON keys.id = obj.id
INNER JOIN syscolumns col
ON keys.id = col.id
AND keys.colid = col.colid
INNER JOIN sysindexes idx
ON keys.id = idx.id
AND keys.indid = idx.indid
WHERE
obj.xtype = 'U'
ORDER BY
idx.name
,keys.id
,keys.indid
,keys.keyno
外部キー一覧
SELECT
name
, OBJECT_NAME(parent_object_id) parent_object_id
, OBJECT_NAME(referenced_object_id) referenced_object_id
FROM
sys.foreign_keys
最終更新：2016年10月06日 20:27
