Show you are db tables schema in sql script (查詢欄位名細)

有時候我們需要撰寫資料腳本的文件, 或者是客戶會要求你給一份目前資料庫的明細文件。一
般的說明文件如 : 表格名, 欄名, 欄類型, 長度, 是否NULL, 欄位描述...等等。在這個時候如果表
格多時, 可能會打到死吧! 由其是用Word文件...這時候如果有個查欄位明細的腳本不知該有多好
。
以下就是了

--查詢欄位名細.sql

SELECT

    Sysobjects.name AS [數据表名稱],

    syscolumns.name AS [欄位名稱],

    systypes.name AS [欄位類型],

    syscolumns.length AS [欄位長度],

    sys.extended_properties.[value] AS [欄位描述], 

    syscomments.text AS [Default-Value],

    syscolumns.isnullable AS [IsNull]

FROM syscolumns

    INNER JOIN systypes ON syscolumns.xtype = systypes.xtype

    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id

    LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id )

    LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id

where sysobjects.name in (

    select table_name from information_schema.tables

    where table_catalog='person' -- 這裡是你的資料庫名稱

    and table_type='BASE TABLE'

)

order by sysobjects.name

幸福吧!!