문서의 이전 판입니다!
목차
INFORMATION_SCHEMA
TABLES
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES
VIEWS
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE FROM INFORMATION_SCHEMA.VIEWS
COLUMNS
SELECT *--COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS
각종 Database정보 얻기
테이블 목록 가져오기:
SELECT * FROM sysobjects WHERE xtype='U'
뷰
SELECT * FROM sysobjects WHERE xtype='V'
프로시져
SELECT * FROM sysobjects WHERE xtype='P'
테이블 리스트
SELECT * FROM INFORMATION_SCHEMA.TABLES
뷰 리스트
SELECT * FROM INFORMATION_SCHEMA.VIEWS
컬럼 리스트
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
컬럼 키값
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
프로시져
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
프로시져 파라메타값
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
컬럼 정보 가져오기
SELECT
A.TABLE_CATALOG
,A.TABLE_NAME
,A.ORDINAL_POSITION
,A.COLUMN_NAME
,A.DATA_TYPE
,ISNULL(A.CHARACTER_MAXIMUM_LENGTH,)
,ISNULL(A.NUMERIC_PRECISION,)
,A.IS_NULLABLE
,ISNULL(A.COLUMN_DEFAULT,)
,ISNULL(B.CONSTRAINT_NAME,)
,ISNULL(A.CHARACTER_SET_NAME,)
,ISNULL(A.COLLATION_NAME,)
,CASE WHEN ISNULL(C.NAME,) = THEN '' ELSE 'Identity' END auto
FROM
INFORMATION_SCHEMA.COLUMNS A
LEFT OUTER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT OUTER JOIN
syscolumns C
ON C.ID = object_id(A.TABLE_NAME) AND A.COLUMN_NAME = C.NAME AND C.COLSTAT & 1 = 1
WHERE
A.TABLE_NAME = '테이블명'
ORDER BY A.ORDINAL_POSITION