데이터베이스 컬럼정보 가져오기

--테이블 목록 가져오기:
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 in (SELECT name FROM sysobjects WHERE xtype='U') 

ORDER BY A.ORDINAL_POSITION