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

幸福吧!!

Copyright © Bruce Huang All rights reserved.