| 導購 | 订阅 | 在线投稿
分享
 
 
 

獲取sql數據庫所有表結構

來源:互聯網  2008-12-24 08:08:35  評論

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序號',

a.name N'字段名',

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識',

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

WHERE (id = a.id) AND (name = a.name))))))) AND

(xtype = 'PK'))>0 then '√' else '' end) N'主鍵',

b.name N'類型',

a.length N'占用字節數',

COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數',

(case when a.isnullable=1 then '√'else '' end) N'允許空',

isnull(e.text,'') N'默認值',

isnull(g.[value],'') AS N'字段說明'

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

可以將以上代碼放到SQL查詢分析器上運行一下就知道了(當然要打開一個數據庫啦)

SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序號', a.name N'字段名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識', (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) N'主鍵', b.name N'類型', a.length N'占用字節數', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數', (case when a.isnullable=1 then '√'else '' end) N'允許空', isnull(e.text,'') N'默認值', isnull(g.[value],'') AS N'字段說明' FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder 可以將以上代碼放到SQL查詢分析器上運行一下就知道了(當然要打開一個數據庫啦)
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有