/*
해당 데이터베이스 테이블 조회
*/
SELECT TABLE_NAME AS T_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
/*
해당 테이블의 스키마 조회( 컬럼명, PK, ISNULL, 데이터 타입 )
*/
SELECT COLUMN_NAME AS C_NAME, DATA_TYPE AS C_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'DEPT'
/*
테이블별 상세 조회
*/
SELECT
A.TABLE_CATALOG,A.TABLE_SCHEMA,A.TABLE_NAME,B.ORDINAL_POSITION ,b.COLUMN_NAME,isnull(C.CONSTRAINT_NAME,'') ,
B.IS_NULLABLE ,B.DATA_TYPE ,ISNULL(CONVERT(VARCHAR(10),B.CHARACTER_MAXIMUM_LENGTH ),'')
FROM INFORMATION_SCHEMA.TABLES A
INNER JOIN INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_NAME = B.TABLE_NAME AND A.TABLE_TYPE = 'BASE TABLE'
left outer join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c on a.TABLE_NAME = c.TABLE_NAME and B.COLUMN_NAME = c.COLUMN_NAME and c.CONSTRAINT_NAME like 'PK%'
ORDER BY A.TABLE_CATALOG ,A.TABLE_SCHEMA ,A.TABLE_NAME ,B.ORDINAL_POSITION