테이블 레이아웃 문서를 위한 쿼리

테이블 레이아웃 문서를 위한 쿼리

 

< 쿼리 >

SELECT Column_Name 필드,'' 필드명,UPPER(Data_Type) 타입, CASE WHEN Data_Type = 'Numeric' THEN CAST(5 AS VARCHAR) + '(' + CAST(Numeric_Precision AS VARCHAR) + ')' ELSE ISNULL(CAST(Character_Maximum_Length AS VARCHAR),'') END 크기, CASE WHEN IS_Nullable = 'Yes' THEN 'Null' ELSE '' END [Null 여부] FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'Tablename' ORDER BY Ordinal_Position

 

 <결과값>

 

* 컬럼 Description 추가(20190911)

 

SELECT A.Column_Name 필드,'' 필드명,UPPER(A.Data_Type) 타입, CASE WHEN A.Data_Type = 'Numeric' THEN CAST(5 AS VARCHAR) + '(' + CAST(A.Numeric_Precision AS VARCHAR) + ')' ELSE ISNULL(CAST(A.Character_Maximum_Length AS VARCHAR),'') END 크기, CASE WHEN A.IS_Nullable = 'Yes' THEN 'Null' ELSE '' END [Null 여부], B.VALUE 컬럼비고 FROM INFORMATION_SCHEMA.COLUMNS A LEFT JOIN SYS.EXTENDED_PROPERTIES B ON B.MAJOR_ID = OBJECT_ID(A.TABLE_NAME) AND B.MINOR_ID = OBJECT_ID(A.COLUMN_NAME) AND B.NAME = 'MS_Description' WHERE A.Table_Name = 'SA_IVL' ORDER BY A.Ordinal_Position

 

* PK 추가

 

SELECT A.Column_Name 필드, B.VALUE 컬럼비고,UPPER(A.Data_Type) 타입, CASE WHEN A.Data_Type = 'Numeric' THEN CAST(5 AS VARCHAR) + '(' + CAST(A.Numeric_Precision AS VARCHAR) + ')' ELSE ISNULL(CAST(A.Character_Maximum_Length AS VARCHAR),'') END 크기, CASE WHEN C.COLUMN_NAME IS NULL THEN '' ELSE 'Y' END [PK 여부], CASE WHEN A.IS_Nullable = 'Yes' THEN 'NULL' ELSE '' END [Null 여부] FROM INFORMATION_SCHEMA.COLUMNS A LEFT JOIN SYS.EXTENDED_PROPERTIES B ON B.MAJOR_ID = OBJECT_ID(A.TABLE_NAME) AND B.MINOR_ID = A.ORDINAL_POSITION AND B.NAME = 'MS_Description' LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON C.TABLE_NAME = A.TABLE_NAME AND C.COLUMN_NAME = A.COLUMN_NAME AND C.CONSTRAINT_NAME LIKE 'PK%' WHERE A.Table_Name = 'TB_CM_CLIENT_CUST' ORDER BY A.Ordinal_Position