当前位置： 移动技术网 > IT编程>数据库>MSSQL > SQL提取数据库表名及字段名等信息代码示例

SQL提取数据库表名及字段名等信息代码示例

2017年11月02日  | 移动技术网IT编程  | 我要评论

突击敢死队,知花梅莎qvod,大连万达影城影讯

本文向大家介绍了使用sql语句提取数据库所有表的表名、字段名的实例代码，在sqlserver 中进行了测试，具体内容如下：

--查询所有用户表所有字段的特征
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

------提取某个表的字段名

select
(case when a.colorder=1 then d.name else '' end) n'表名',
a.colorder n'字段序号',
a.name n'字段名',
isnull(g.[value],'') as 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'默认值'

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条件输入表名
where d.name='consumebillbody'
order by object_name(a.id),a.colorder

总结

以上就是本文关于sql提取数据库表名及字段名等信息代码示例的全部内容，希望对大家有所帮助。感兴趣的朋友可以继续参阅本站：sql注入原理与解决方法代码示例、mysql删除有外键约束的表数据方法介绍、几个比较重要的mysql变量等，有什么问题可以随时留言，小编会及时回复大家的。感谢朋友们对本站的支持！

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网