-------在SQLserver 2000中測試
--
查詢所有用戶表所有字段的特征
SELECT ?D.Name? as ?TableName,?A.colorder? AS ?ColOrder,?A.name? AS ?Name,??
?? COLUMNPROPERTY (A.ID,A.Name,? ' IsIdentity ' )? AS ?IsIdentity,??
?? CASE ? WHEN ? EXISTS
??( SELECT ? 1
?? FROM ?dbo.sysobjects
?? WHERE ?Xtype? = ? ' PK ' ? AND ?Name? IN
??( SELECT ?Name
?? FROM ?sysindexes
?? WHERE ?indid? IN
??( SELECT ?indid
?? FROM ?sysindexkeys
?? WHERE ?ID? = ?A.ID? AND ?colid? = ?A.colid)))??
?? THEN ? 1 ? ELSE ? 0 ? END ? AS ?主鍵,??
??B.name? AS ?類型,
??A.length? AS ? [ 長度 ] ,
??A.xprec? AS ? [ 精度 ] ,
??A.xscale? AS ? [ 小數 ] ,
?? CASE ? WHEN ?A.isnullable? = ? 1 ? THEN ? 1 ? ELSE ? 0 ? END ? AS ? [ 可為空 ] ,??
ISNULL (E. text ,? ' ? ' )? AS ? [ 默認值 ] ,??
?? ISNULL (G. [ value ] ,? ' ? ' )? AS ? [ 說明 ]
FROM ?dbo.syscolumns?A? LEFT ? OUTER ? JOIN
??dbo.systypes?B? ON ?A.xtype? = ?B.xusertype? INNER ? JOIN
??dbo.sysobjects?D? ON ?A.id? = ?D.id? AND ?D.xtype? = ? ' U ' ? AND ??
??D.name? <> ? ' dtproperties ' ? LEFT ? OUTER ? JOIN
??dbo.syscomments?E? ON ?A.cdefault? = ?E.id?
?? LEFT ? OUTER ? JOIN ?dbo.sysproperties?G? ON ?A.id? = ?G.id? AND ?A.colid? = ?G.smallid
-- WHERE?D.Name='tablename'?--如果找指定表,把注釋去掉
ORDER ? BY ? 1 ,? 2
SELECT ?D.Name? as ?TableName,?A.colorder? AS ?ColOrder,?A.name? AS ?Name,??
?? COLUMNPROPERTY (A.ID,A.Name,? ' IsIdentity ' )? AS ?IsIdentity,??
?? CASE ? WHEN ? EXISTS
??( SELECT ? 1
?? FROM ?dbo.sysobjects
?? WHERE ?Xtype? = ? ' PK ' ? AND ?Name? IN
??( SELECT ?Name
?? FROM ?sysindexes
?? WHERE ?indid? IN
??( SELECT ?indid
?? FROM ?sysindexkeys
?? WHERE ?ID? = ?A.ID? AND ?colid? = ?A.colid)))??
?? THEN ? 1 ? ELSE ? 0 ? END ? AS ?主鍵,??
??B.name? AS ?類型,
??A.length? AS ? [ 長度 ] ,
??A.xprec? AS ? [ 精度 ] ,
??A.xscale? AS ? [ 小數 ] ,
?? CASE ? WHEN ?A.isnullable? = ? 1 ? THEN ? 1 ? ELSE ? 0 ? END ? AS ? [ 可為空 ] ,??
ISNULL (E. text ,? ' ? ' )? AS ? [ 默認值 ] ,??
?? ISNULL (G. [ value ] ,? ' ? ' )? AS ? [ 說明 ]
FROM ?dbo.syscolumns?A? LEFT ? OUTER ? JOIN
??dbo.systypes?B? ON ?A.xtype? = ?B.xusertype? INNER ? JOIN
??dbo.sysobjects?D? ON ?A.id? = ?D.id? AND ?D.xtype? = ? ' U ' ? AND ??
??D.name? <> ? ' dtproperties ' ? LEFT ? OUTER ? JOIN
??dbo.syscomments?E? ON ?A.cdefault? = ?E.id?
?? LEFT ? OUTER ? JOIN ?dbo.sysproperties?G? ON ?A.id? = ?G.id? AND ?A.colid? = ?G.smallid
-- WHERE?D.Name='tablename'?--如果找指定表,把注釋去掉
ORDER ? BY ? 1 ,? 2
?
------在SQLserver 2005中測試
--
查詢所有用戶表所有字段的特征
SELECT
????
( case ? when ?a.colorder = 1 ? then ?d.name? else ? '' ? end )? as ?表名, -- 如果表名相同就返回空???
?????a.colorder? as ?字段序號,???
?????a.name? as ?字段名,???
?????( case ? when ? COLUMNPROPERTY (?a.id,a.name,? ' IsIdentity ' ?) = 1 ? then ? ' √ ' ? else ? '' ? end )? as ?標識,???
?????( 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 )? as ?主鍵, -- 查詢主鍵END???
b.name? as ?類型,???
a.length? as ?占用字節數,???
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ?)? as ????長度,???
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ?), 0 )? as ?小數位數,???
( case ? when ?a.isnullable = 1 ? then ? ' √ ' ? else ? '' ? end )? as ?允許空,???
isnull (e. text , '' ?)? as ?默認值,???
isnull (g. [ value ] , '' ?)? AS ?字段說明????
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 ?sys.extended_properties?g???
on ?a.id = g.major_id? AND ?a.colid? = ?g.minor_id????
????? -- where?d.name='PE_U_ValliMessage'?--所要查詢的表???
order ? by ?a.id,a.colorder??
( case ? when ?a.colorder = 1 ? then ?d.name? else ? '' ? end )? as ?表名, -- 如果表名相同就返回空???
?????a.colorder? as ?字段序號,???
?????a.name? as ?字段名,???
?????( case ? when ? COLUMNPROPERTY (?a.id,a.name,? ' IsIdentity ' ?) = 1 ? then ? ' √ ' ? else ? '' ? end )? as ?標識,???
?????( 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 )? as ?主鍵, -- 查詢主鍵END???
b.name? as ?類型,???
a.length? as ?占用字節數,???
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ?)? as ????長度,???
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ?), 0 )? as ?小數位數,???
( case ? when ?a.isnullable = 1 ? then ? ' √ ' ? else ? '' ? end )? as ?允許空,???
isnull (e. text , '' ?)? as ?默認值,???
isnull (g. [ value ] , '' ?)? AS ?字段說明????
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 ?sys.extended_properties?g???
on ?a.id = g.major_id? AND ?a.colid? = ?g.minor_id????
????? -- where?d.name='PE_U_ValliMessage'?--所要查詢的表???
order ? by ?a.id,a.colorder??
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
