テーブルやストアド プロシージャの情報を一覧として取得する

SQL Server で、テーブルおよび列の情報や、ストアド プロシージャおよびパラメーターの情報 (メタデータ) を
一覧として取得する方法について記述します。

■ テーブルおよび列の一覧を取得する

sys.tables および sys.columns からテーブルおよび列のメタデータを取得できます。
さらに、型やキーの情報を取得するためには sys.types や sys.indexes と結合します。
最大サイズ (max_length) についてはバイト数を取得するので、
Unicode 文字列型 (nchar または nvarchar) の場合には 2 で除算するとよいです。

次のようなクエリでメタデータを取得できます。

---

SELECT          T.name AS TableName, C.name AS ColumnName, U.name AS TypeName,
                CASE WHEN U.name IN (N’nchar’, N’nvarchar’) THEN C.max_length / 2 ELSE C.max_length END AS [MaxLength],
                IC.index_column_id AS [PrimaryKeyIndex],
                FT.name AS [RefTableName], FC.name AS [RefColumnName],
                C.is_nullable, C.is_identity
FROM            sys.columns AS C
INNER JOIN      sys.tables AS T
ON              C.object_id = T.object_id
INNER JOIN      sys.types AS U
ON              C.user_type_id = U.user_type_id
LEFT OUTER JOIN sys.indexes AS I
ON              C.object_id = I.object_id
AND             I.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns AS IC
ON              C.object_id = IC.object_id
AND             C.column_id = IC.column_id
AND             I.index_id = IC.index_id
LEFT OUTER JOIN sys.foreign_key_columns AS F
ON              C.object_id = F.parent_object_id
AND             C.column_id = F.parent_column_id
LEFT OUTER JOIN sys.tables AS FT
ON              F.referenced_object_id = FT.object_id
LEFT OUTER JOIN sys.columns AS FC
ON              F.referenced_object_id = FC.object_id
AND             F.referenced_column_id = FC.column_id
ORDER BY        TableName, C.column_id

---

Northwind データベースに対して実行すると、下の図のような結果が得られます。

注意点
(1) 上記のクエリでは、列が 0 個のテーブルは取得されません。

■ ストアド プロシージャおよびパラメーターの一覧を取得する

先ほどのテーブル情報と同様にして、sys.procedures および sys.parameters から
ストアド プロシージャおよびパラメーターのメタデータを取得できます。

---

SELECT     P.name AS ProcedureName, Q.name AS ParamName, U.name AS TypeName,
           CASE WHEN U.name IN (N’nchar’, N’nvarchar’) THEN Q.max_length / 2 ELSE Q.max_length END AS [MaxLength],
           Q.is_output
FROM       sys.parameters AS Q
INNER JOIN sys.procedures AS P
ON         Q.object_id = P.object_id
INNER JOIN sys.types AS U
ON         Q.user_type_id = U.user_type_id
ORDER BY   ProcedureName, Q.parameter_id

---

Northwind データベースに対して実行すると、下の図のような結果が得られます。

注意点
(1) 上記のクエリでは、パラメーターが 0 個のストアド プロシージャは取得されません。

バージョン情報
SQL Server 2008, 2008 R2

参照
sys.tables (Transact-SQL)
sys.columns (Transact-SQL)
sys.procedures (Transact-SQL)
sys.parameters (Transact-SQL)