Windows DB

System Table Field Query

2009.05.14 19:09

dalgri 조회 수:13012

현재 사용하고 있는 Database의 table정보와

필드 가지고오기, 

Please refer to attached file for result of the following query.

 

SELECT
 A.TABLE_CATALOG
 ,A.TABLE_NAME
 ,A.COLUMN_NAME
 ,A.DATA_TYPE
 ,ISNULL(A.CHARACTER_MAXIMUM_LENGTH,'')AS LENGTH
 ,A.IS_NULLABLE
 ,ISNULL(B.CONSTRAINT_NAME,'')AS PK
 ,ISNULL(A.CHARACTER_SET_NAME,'')AS CHARACTER
 ,ISNULL(A.COLLATION_NAME,'')AS COLLATION_NAME
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
order by a.table_name