SQL Server 2000

SELECT
??? 表名?????? = case when a.colorder=1 then d.name else '' end,
??? 表說(shuō)明???? = case when a.colorder=1 then isnull(f.value,'') else '' end,
??? 字段名???? = a.name,
??? 主鍵?????? = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
???????????????????? SELECT name FROM sysindexes WHERE indid in(
??????????????????????? SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
??? 類(lèi)型?????? = b.name,
??? 占用字節(jié)數(shù) = a.length,
??? 長(zhǎng)度?????? = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
??? 默認(rèn)值???? = isnull(e.text,''),
??? 字段說(shuō)明?? = isnull(g.[value],'')
FROM
??? syscolumns a
left join
??? systypes b
on
??? a.xusertype=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?
left join
??? sysproperties f
on
??? d.id=f.id and f.smallid=0
where
??? d.name='LW_Equipment_Stock_List'??? --如果只查詢指定表,加上此條件

?

SQL Server 2005

SELECT?
??? TableName?????????????? =?? CASE WHEN A.COLORDER=1 THEN D.NAME ELSE ' ' END,
??? TableDescription??????? =?? CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE, ' ') ELSE?? ' '?? END,
??? [ColumnSort]??????????? =?? A.COLORDER,
??? FieldName??????????????? =?? A.NAME,
??? IsIdentity??????????????? =?? CASE?? WHEN?? COLUMNPROPERTY(?? A.ID,A.NAME, 'ISIDENTITY ')=1?? THEN?? '√ 'ELSE?? ' '?? END,
??? IsPrimaryKey??????????? =?? CASE?? WHEN?? EXISTS(SELECT?? 1?? FROM?? SYSOBJECTS?? WHERE?? XTYPE= 'PK '?? AND?? PARENT_OBJ=A.ID?? AND?? NAME?? IN?? (
??????? SELECT?? NAME?? FROM?? SYSINDEXES?? WHERE?? INDID?? IN(
??????? SELECT?? INDID?? FROM?? SYSINDEXKEYS?? WHERE?? ID?? =?? A.ID?? AND?? COLID=A.COLID)))?? THEN?? '√ '?? ELSE?? ' '?? END,
??? DataType?????????????? =?? B.NAME,
??? [SetLength]?????????????? =?? A.LENGTH,
??? [DataLength]?????????? =?? COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '),
??? [DecimalDigit]?????????? =?? ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0),
??? [IsNull]?????????????? =?? CASE?? WHEN?? A.ISNULLABLE=1?? THEN?? '√ 'ELSE?? ' '?? END,
??? [DefaultValue]???????? =?? ISNULL(E.TEXT, ' '),
??? [ColumnDescription]?????? =?? ISNULL(G.[VALUE], ' ')
FROM?
??? SYSCOLUMNS A
??? LEFT JOIN?
??? SYSTYPES B?
ON?
??? A.XUSERTYPE=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?
??????? sys.extended_properties G?
ON?
??? A.ID=G.major_id AND A.COLID=G.minor_id???
??? LEFT JOIN?
??????? sys.extended_properties F?
ON?
??? D.ID=F.major_id AND F.minor_id=0
ORDER BY?
??? A.ID,A.COLORDER

轉(zhuǎn)載于:https://www.cnblogs.com/wjonjon/archive/2009/10/26/1589734.html

總結(jié)

以上是生活随笔為你收集整理的在SQL Server 2000 和SQL Server 2005中导出表结构的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。