--테이블 목록 가져오기: 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
|