[MS-SQL] 테이블명, 컬럼명 검색

-- 테이블명 검색

SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = '테이블명'

-- 컬럼명 검색

SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME = '컬럼명'

-- PK 검색

SELECT * FROM information_schema.KEY_COLUMN_USAGE

-- 전체 테이블명 및 컬럼명 검색

SELECT B.name + '.' + A.name [TableName], D.value [TableDesc], C.name [ColumnName], E.value [ColumnDesc]    

FROM SYSOBJECTS A

INNER JOIN SYSUSERS B ON A.uid = B.uid

INNER JOIN SYSCOLUMNS C ON A.id = C.id

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES D ON A.id = D.major_id AND D.minor_id = 0 AND D.name = 'MS_Description'

LEFT OUTER JOIN SYS.EXTENDED_PROPERTIES E ON C.id = E.major_id AND C.colid = E.minor_id AND E.name = 'MS_Description'

WHERE A.type = 'U'

ORDER BY A.name, C.colorder

/*

SELECT * FROM SYSOBJECTS

SELECT * FROM SYSUSERS

SELECT * FROM SYSCOLUMNS

SELECT * FROM SYS.EXTENDED_PROPERTIES

*/