2008年4月2日 星期三

找出資料表的欄位型態、大小、描述…等

找出table_name這個table的欄位名稱、型態(大小)、預設值、描述、isNull
Select C.COLUMN_NAME,
   CASE DATA_TYPE WHEN 'nvarchar' THEN DATA_TYPE+'('+CONVERT(NVARCHAR(1000),CHARACTER_MAXIMUM_LENGTH)+')' ELSE DATA_TYPE END,
   ISNULL(C.COLUMN_DEFAULT, ''), ISNULL(D.value, ''), CASE C.IS_NULLABLE WHEN 'NO' THEN '' ELSE 'Y' END,
   D.NAME, D.object_id, D.minor_id,
   C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
From (
  Select A.name, A.object_id, B.minor_id, B.value
  From sys.tables A LEFT JOIN sys.extended_properties B
   on A.object_id = B.major_id
) D RIGHT JOIN Information_Schema.Columns C
ON D.name = C.TABLE_NAME AND D.minor_id = C.ORDINAL_POSITION
Where C.TABLE_NAME = 'table_name'
ORDER BY C.ORDINAL_POSITION

沒有留言: