프로젝트에서 누군가 만든 쿼리인데 유용하게 쓰일 때가 있어서 포스팅한다.
--######## 스키마정보 마스터조회(마스터쿼리) ########--
SELECT A.TYPE TABLE_TYPE
,(CASE WHEN A.TYPE='U' THEN '테이블' WHEN A.TYPE='V' THEN '뷰' END) TABLE_TYPE_NM
,A.NAME TABLE_ID
,ISNULL(CONVERT(VARCHAR,Z.VALUE),'') TABLE_NAME
,A.CREATE_DATE TABLE_CREATE
,A.MODIFY_DATE TABLE_MODIFY
,A.OBJECT_ID
FROM SYS.OBJECTS A WITH(NOLOCK)
LEFT JOIN SYS.EXTENDED_PROPERTIES Z WITH(NOLOCK)
ON Z.MAJOR_ID = A.OBJECT_ID
AND Z.MINOR_ID = 0
WHERE A.TYPE IN ('U','V')
AND A.TYPE LIKE ? +'%' --조건값(테이블유형 U:테이블,V:뷰)
AND A.NAME = (CASE WHEN ? ='' THEN A.NAME ELSE ? END) --조건값(테이블ID)
AND ISNULL(CONVERT(VARCHAR,Z.VALUE),'') LIKE '%'+?+'%' --조건값(테이블명)
AND EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.COLUMNS S WITH(NOLOCK)
LEFT JOIN SYS.EXTENDED_PROPERTIES T WITH(NOLOCK)
ON T.MAJOR_ID = A.OBJECT_ID
AND T.MINOR_ID = 0
AND T.NAME = 'MS_Description'
LEFT JOIN SYS.EXTENDED_PROPERTIES U WITH(NOLOCK)
ON U.MAJOR_ID = A.OBJECT_ID
AND U.MINOR_ID = S.ORDINAL_POSITION
AND U.NAME = 'MS_Description'
WHERE S.TABLE_NAME = A.NAME
AND ISNULL(CONVERT(VARCHAR,U.VALUE),'') LIKE '%'+?+'%' --조건값(컬럼명)
AND S.COLUMN_NAME = (CASE WHEN ? ='' THEN S.COLUMN_NAME ELSE ? END) --조건값(컬럼ID)
)
ORDER BY A.TYPE, A.NAME
--######## 스키마정보 상세조회(디테일쿼리) ########--
SELECT B.ORDINAL_POSITION SEQ --순번
,B.COLUMN_NAME COLUMN_ID
,ISNULL(CONVERT(VARCHAR,Y.VALUE),'') COLUMN_NAME
,CASE WHEN B.DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar')
THEN B.DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(B.CHARACTER_MAXIMUM_LENGTH, B.NUMERIC_PRECISION)) + ')'
WHEN B.DATA_TYPE IN ('DECIMAL')
THEN B.DATA_TYPE + '(' + CONVERT(VARCHAR(10), B.NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), B.NUMERIC_SCALE) + ')'
ELSE B.DATA_TYPE
END AS COLUMN_TYPE
,CASE WHEN B.COLUMN_NAME IS NOT NULL THEN 'YES' ELSE '' END AS IS_PK
,CASE WHEN B.IS_NULLABLE='NO' THEN 'YES' ELSE '' END AS IS_NOTNULL
,ISNULL(B.COLUMN_DEFAULT,'') AS DEFAULT_VALUE
FROM SYS.OBJECTS A WITH(NOLOCK)
JOIN INFORMATION_SCHEMA.COLUMNS B WITH(NOLOCK)
ON B.TABLE_NAME = A.NAME
LEFT JOIN SYS.EXTENDED_PROPERTIES Y WITH(NOLOCK)
ON Y.MAJOR_ID = A.OBJECT_ID
AND Y.MINOR_ID = B.ORDINAL_POSITION
AND Y.NAME = 'MS_Description'
WHERE A.OBJECT_ID = ?
ORDER BY B.ORDINAL_POSITION
--######## 스키마정보 조회(컬럼정보만조회쿼리) ########--
SELECT A.GUBUN --구분
,A.TABLE_TYPE --테이블유형
,A.TABLE_TYPE_NM --테이블유형명
,A.TABLE_ID --테이블ID
,A.TABLE_NAME --테이블명
,A.TABLE_CREATE --테이블생성일
,A.TABLE_MODIFY --테이블수정일
,A.SEQ --순번
,A.COLUMN_ID --컬럼ID
,A.COLUMN_NAME --컬럼명
,A.COLUMN_TYPE --컬럼타입
,A.IS_PK --PK
,A.IS_NOTNULL --NotNull
,A.DEFAULT_VALUE --디폴트값
FROM (
SELECT 'TABLE' GUBUN
,A.TYPE TABLE_TYPE
,(CASE WHEN A.TYPE='U' THEN '테이블' WHEN A.TYPE='V' THEN '뷰' END) TABLE_TYPE_NM
,A.NAME TABLE_ID
,ISNULL(CONVERT(VARCHAR,Z.VALUE),'') TABLE_NAME
,A.CREATE_DATE TABLE_CREATE
,A.MODIFY_DATE TABLE_MODIFY
,0 SEQ
,'' COLUMN_ID
,'' COLUMN_NAME
,'' COLUMN_TYPE
,'' IS_PK
,'' IS_NOTNULL
,'' DEFAULT_VALUE
FROM SYS.OBJECTS A WITH(NOLOCK)
LEFT JOIN SYS.EXTENDED_PROPERTIES Z WITH(NOLOCK)
ON Z.MAJOR_ID = A.OBJECT_ID
AND Z.MINOR_ID = 0
AND Z.NAME = 'MS_Description'
WHERE A.TYPE IN ('U','V')
UNION ALL
SELECT 'COLUMN' GUBUN
,A.TYPE TABLE_TYPE
,(CASE WHEN A.TYPE='U' THEN '테이블' WHEN A.TYPE='V' THEN '뷰' END) TABLE_TYPE_NM
,A.NAME TABLE_ID
,ISNULL(CONVERT(VARCHAR,Z.VALUE),'') TABLE_NAME
,A.CREATE_DATE TABLE_CREATE
,A.MODIFY_DATE TABLE_MODIFY
,B.ORDINAL_POSITION SEQ
,B.COLUMN_NAME COLUMN_ID
,ISNULL(CONVERT(VARCHAR,Y.VALUE),'') COLUMN_NAME
,CASE WHEN B.DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'char', 'nchar')
THEN B.DATA_TYPE + '(' + CONVERT(VARCHAR(10), ISNULL(B.CHARACTER_MAXIMUM_LENGTH, B.NUMERIC_PRECISION)) + ')'
WHEN B.DATA_TYPE IN ('DECIMAL')
THEN B.DATA_TYPE + '(' + CONVERT(VARCHAR(10), B.NUMERIC_PRECISION) + ', ' + CONVERT(VARCHAR(10), B.NUMERIC_SCALE) + ')'
ELSE B.DATA_TYPE
END AS COLUMN_TYPE
,CASE WHEN C.COLUMN_NAME IS NOT NULL THEN 'YES' ELSE '' END AS IS_PK
,CASE WHEN B.IS_NULLABLE='NO' THEN 'YES' ELSE '' END AS IS_NOTNULL
,ISNULL(B.COLUMN_DEFAULT,'') AS DEFAULT_VALUE
FROM SYS.OBJECTS A WITH(NOLOCK)
JOIN INFORMATION_SCHEMA.COLUMNS B WITH(NOLOCK)
ON B.TABLE_NAME = A.NAME
LEFT JOIN (SELECT A.TABLE_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A WITH(NOLOCK)
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B WITH(NOLOCK)
ON A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE = 'PRIMARY KEY'
) C
ON C.TABLE_NAME = A.NAME
AND C.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN SYS.EXTENDED_PROPERTIES Z WITH(NOLOCK)
ON Z.MAJOR_ID = A.OBJECT_ID
AND Z.MINOR_ID = 0
AND Z.NAME = 'MS_Description'
LEFT JOIN SYS.EXTENDED_PROPERTIES Y WITH(NOLOCK)
ON Y.MAJOR_ID = A.OBJECT_ID
AND Y.MINOR_ID = B.ORDINAL_POSITION
AND Y.NAME = 'MS_Description'
WHERE A.TYPE IN ('U','V')
) A
WHERE A.GUBUN = 'COLUMN'
AND A.TABLE_TYPE LIKE ?+'%' --조건값(테이블유형 U:테이블,V:뷰)
AND A.TABLE_ID = (CASE WHEN ?='' THEN A.TABLE_ID ELSE ? END) --조건값(테이블ID)
AND A.TABLE_NAME LIKE '%'+?+'%' --조건값(테이블명)
AND A.COLUMN_ID = (CASE WHEN ?='' THEN A.COLUMN_ID ELSE ? END) --조건값(컬럼ID)
AND A.COLUMN_NAME LIKE '%'+?+'%' --조건값(컬럼명)
ORDER BY A.TABLE_TYPE, A.TABLE_ID, A.SEQ, A.COLUMN_ID
### 수정 : 9/27(목)
PK 여부값 조회 부분 수정
