MSSQL/Catalog

Microsoft SQL Server カタログ ビュー

システム カタログ ビュー システム ビュー のメモ

• オブジェクト カタログ ビュー (Transact-SQL)
  • https://docs.microsoft.com/ja-jp/sql/relational-databases/system-catalog-views/object-catalog-views-transact-sql

  SQL Server オブジェクト一覧

-- SQL Server オブジェクト一覧 (データベースダイアグラム 関連除外)
-- xtype U:ユーザテーブル、V:ビュー、P:プロシージャ、FN:ファンクション
SELECT o.type AS [種類], s.name AS [スキーマ名], o.name AS [オブジェクト名]
, o.create_date AS [作成日], o.modify_date AS [変更日]
FROM sys.objects o JOIN sys.schemas s ON (s.schema_id=o.schema_id)
WHERE o.type IN ('U','V','P','FN') AND o.name NOT IN 
('sysdiagrams','sp_alterdiagram','sp_creatediagram','sp_dropdiagram'
,'sp_helpdiagramdefinition','sp_helpdiagrams','sp_renamediagram','sp_upgraddiagrams')
ORDER BY o.type, s.name, o.name

  SQL Server テーブル項目一覧

-- SQL Server テーブル項目一覧 (データベースダイアグラム 関連除外)
SELECT o.name AS [テーブル名], c.column_id AS [順番], c.name AS [カラム名]
, t.name AS [データ型]
, c.max_length AS [最大バイト長]
, CASE WHEN c.precision=t.precision AND c.scale=t.scale THEN NULL ELSE c.precision END AS [有効桁数]
, CASE WHEN c.precision=t.precision AND c.scale=t.scale THEN NULL ELSE c.scale     END AS [小数点以下桁数]
, CASE c.is_nullable WHEN 1 THEN 'NULL' ELSE 'NOT NULL' END AS [NULL 値]
, c.collation_name AS [照合順序]
FROM sys.tables o 
INNER JOIN sys.columns c ON (c.object_id=o.object_id) 
INNER JOIN sys.types t ON (t.user_type_id=c.user_type_id)
WHERE o.type = 'U' AND NOT o.name IN ('sysdiagrams')
ORDER BY o.name, c.column_id

  SQL Server インデックス一覧

-- SQL Server インデックス一覧 (データベースダイアグラム 関連除外)
SELECT s.name AS [スキーマ名], o.name AS [テーブル名], i.name AS [インデックス名]
, CASE is_unique WHEN 1 THEN N'一意' ELSE N'' END AS [一意か]
, CASE is_primary_key WHEN 1 THEN N'PRIMARY KEY' ELSE N'' END AS [PRIMARY KEY 制約]
, CASE is_unique_constraint WHEN 1 THEN N'UNIQUE' ELSE N'' END AS [UNIQUE 制約]
, CASE is_disabled WHEN 1 THEN N'無効' ELSE N'' END AS [無効か]
FROM sys.indexes i
JOIN sys.objects o ON (o.object_id=i.object_id)
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
WHERE i.index_id>0 AND o.type IN ('U') AND o.name NOT IN ('sysdiagrams')
ORDER BY s.name, o.name, i.name

  SQL Server インデックス項目一覧

-- SQL Server インデックス項目一覧 (データベースダイアグラム 関連除外)
SELECT s.name AS [スキーマ名], o.name AS [テーブル名], i.name AS [インデックス名]
, NULLIF(d.key_ordinal,0) AS [順番], c.name AS [カラム名]
, CASE d.is_descending_key WHEN 1 THEN N'降順' ELSE N'' END AS [降順か]
, CASE d.is_included_column WHEN 1 THEN N'付加列' ELSE N'' END AS [付加列か]
FROM sys.indexes i
JOIN sys.index_columns d ON (d.object_id=i.object_id)
JOIN sys.columns c ON (c.object_id=d.object_id AND c.column_id=d.column_id) 
JOIN sys.objects o ON (o.object_id=i.object_id)
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
WHERE i.index_id>0 AND o.type IN ('U') AND o.name NOT IN ('sysdiagrams')
ORDER BY s.name, o.name, i.name, d.is_included_column, d.key_ordinal, c.column_id

  SQL Server ビュー定義

-- SQL Server ビュー定義
SELECT s.name AS [スキーマ名], o.name AS [ビュー名], m.definition AS [定義]
FROM sys.sql_modules m 
JOIN sys.views o ON (m.object_id = o.object_id)
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
ORDER BY s.name, o.name

  SQL Server ストアド定義

-- SQL Server ストアド定義 (データベースダイアグラム 関連除外)
SELECT s.name AS [スキーマ名], o.name AS [プロシージャ名], m.definition AS [定義]
FROM sys.sql_modules m 
JOIN sys.procedures o ON (m.object_id = o.object_id)
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
WHERE NOT o.name IN ('sp_alterdiagram','sp_creatediagram','sp_dropdiagram',
'sp_helpdiagramdefinition','sp_helpdiagrams','sp_renamediagram','sp_upgraddiagrams')
ORDER BY s.name, o.name

  SQL Server ファンクション定義

-- SQL Server ファンクション定義 
SELECT s.name AS [スキーマ名], o.name AS [ファンクション名], m.definition AS [定義]
FROM sys.sql_modules m 
JOIN sys.objects o ON (m.object_id = o.object_id)
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
WHERE o.type IN ('FN')
ORDER BY s.name, o.name

  SQL Server シノニム一覧

-- SQL Server シノニム一覧
SELECT s.name AS [スキーマ名], o.name AS [シノニム名]
, o.base_object_name AS [完全引用名]
FROM sys.synonyms o
JOIN sys.schemas s ON (s.schema_id=o.schema_id)
ORDER BY s.name, o.name