!!!Microsoft SQL Server カタログ ビュー
{{category SQL Server,nolink}}システム カタログ ビュー システム ビュー のメモ
* オブジェクト カタログ ビュー (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