SQLserver查询所有表和表下面所有列

发布时间：2020-12-12 13:51:44 所属栏目：MsSql教程来源：网络整理

导读：SELECT?(case?when?a.colorder=1?then?d.name?else?null?end)?表名,?? ??a.colorder?字段序号,a.name?字段名,??(case?when?COLUMNPROPERTY(?a.id,a.name,'IsIdentity')=1?then?'√'else?''?end)?标识,? ??(case?when?(SELECT?count(*)?FROM?sysobjects?? ??W

SELECT?(case?when?a.colorder=1?then?d.name?else?null?end)?表名,?? ??a.colorder?字段序号,a.name?字段名,??(case?when?COLUMNPROPERTY(?a.id,a.name,'IsIdentity')=1?then?'√'else?''?end)?标识,? ??(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)?主键,b.name?类型,a.length?占用字节数,?? ?COLUMNPROPERTY(a.id,'PRECISION')?as?长度,?? ?isnull(COLUMNPROPERTY(a.id,'Scale'),0)?as?小数位数,(case?when?a.isnullable=1?then?'√'else?''?end)?允许空,?? ?isnull(e.text,'')?默认值,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 ?left?join?sys.extended_properties?f?on?d.id=f.class?and?f.minor_id=0 ?where?b.name?is?not?null ?--WHERE?d.name='要查询的表'?--如果只查询指定表,加上此条件 ?order?by?a.id,a.colorder

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!