[SQL] Table 간단 명세 쿼리

SELECT
 TBLS.name
 , COLS.name
 , TYPS.name
 , CASE WHEN TYPS.name IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR') THEN COLS.max_length
  WHEN TYPS.name IN ('NUMERIC') THEN COLS.precision ELSE '' END
 , CASE WHEN TYPS.name IN ('CHAR', 'VARCHAR', 'NCHAR', 'NVARCHAR', 'DATETIME') THEN NULL
  WHEN TYPS.name IN ('NUMERIC') THEN COLS.scale ELSE '' END
 , CASE WHEN COLS.is_nullable IN ('0') THEN ''
  WHEN COLS.is_nullable IN ('1') THEN 'NULL' END
 , CASE WHEN INCOLS.key_ordinal IS NULL AND FKCOL.constraint_object_id IS NULL THEN ''
  WHEN INCOLS.key_ordinal IS NOT NULL THEN 'PK'
  WHEN FKCOL.constraint_object_id IS NOT NULL THEN 'FK' END
 
FROM
 SYS.tables TBLS
 INNER JOIN SYS.columns COLS ON TBLS.object_id = COLS.object_id
 INNER JOIN SYS.types TYPS ON COLS.system_type_id = TYPS.system_type_id AND COLS.user_type_id = TYPS.user_type_id
 LEFT JOIN SYS.index_columns INCOLS ON COLS.object_id = INCOLS.object_id AND COLS.column_id = INCOLS.column_id
 LEFT JOIN SYS.foreign_key_columns FKCOL ON COLS.object_id = FKCOL.parent_object_id AND COLS.column_id = FKCOL.parent_column_id
-- LEFT JOIN SYS.objects FK_OBJ ON FK_OBJ.object_id = FKCOL.constraint_object_id
ORDER BY
 TBLS.name, COLS.column_id