現場で使えるSQL [SQLServer]

現場で使えるSQLまとめてみました。

指定したカラム名を持つテーブルを検索する

SELECT   T.name AS TableName,
         C.name AS ColumnName
FROM     sys.tables AS T
           INNER JOIN sys.columns AS C
            ON T.object_id = C.object_id
WHERE    C.name = 'カラム名'
ORDER BY T.name,
         C.name;

スキーマから簡易テーブル設計書を作成する

SELECT
    C.TABLE_NAME AS TableName,    
    C.ORDINAL_POSITION AS OrdinalPosition,
    CASE WHEN T1.COLUMN_NAME IS NULL THEN 0 ELSE 1 END AS IsPK,
    C.COLUMN_NAME AS ColumnName,
    C.DATA_TYPE AS DataType,
    CASE C.CHARACTER_MAXIMUM_LENGTH WHEN -1 THEN 'MAX' WHEN NULL THEN '' ELSE ISNULL(CONVERT(VARCHAR(10), C.CHARACTER_MAXIMUM_LENGTH), '') END AS MaximumLength,
    CASE WHEN C.IS_NULLABLE = 'NO' THEN 0 ELSE 1 END AS IsNullable,
    COLUMNPROPERTY(OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME)), C.COLUMN_NAME, 'IsIdentity') AS IsIdentity,
    ISNULL(C.COLUMN_DEFAULT, '') AS ColumnDefault
FROM
    INFORMATION_SCHEMA.COLUMNS AS C
    LEFT OUTER JOIN(
        SELECT 
            K.TABLE_NAME, 
            K.COLUMN_NAME 
        FROM 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K 
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T 
            ON K.TABLE_NAME = T.TABLE_NAME 
            AND K.CONSTRAINT_NAME = T.CONSTRAINT_NAME 
        WHERE 
            CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) AS T1 
    ON C.TABLE_NAME = T1.TABLE_NAME
    AND C.COLUMN_NAME = T1.COLUMN_NAME
ORDER BY
    C.TABLE_NAME,
    C.COLUMN_NAME