SQL提取数据库表名及字段名等信息代码示例
本文向大家介绍了使用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变量等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对本站的支持!
栏 目:MsSql
本文地址:https://www.xiuzhanwang.com/a1/MsSql/10414.html
您可能感兴趣的文章
- 01-10SQLServer存储过程实现单条件分页
- 01-10SQL Server 2012降级至2008R2的方法
- 01-10SQLServer中防止并发插入重复数据的方法详解
- 01-10SQL Server数据库定时自动备份
- 01-10SQL Server性能调优之缓存
- 01-10实现SQL Server 原生数据从XML生成JSON数据的实例代码
- 01-10Sql Server 死锁的监控分析解决思路
- 01-10SqlServer 在事务中获得自增ID的实例代码
- 01-10SqlServer快速检索某个字段在哪些存储过程中(sql 语句)
- 01-10SQLServer性能优化--间接实现函数索引或者Hash索引
阅读排行
本栏相关
- 01-10SQLServer存储过程实现单条件分页
- 01-10SQLServer中防止并发插入重复数据的方
- 01-10SQL Server 2012降级至2008R2的方法
- 01-10SQL Server性能调优之缓存
- 01-10SQL Server数据库定时自动备份
- 01-10Sql Server 死锁的监控分析解决思路
- 01-10实现SQL Server 原生数据从XML生成JSON数
- 01-10SqlServer快速检索某个字段在哪些存储
- 01-10SqlServer 在事务中获得自增ID的实例代
- 01-10SQLServer性能优化--间接实现函数索引或
随机阅读
- 01-11Mac OSX 打开原生自带读写NTFS功能(图文
- 01-11ajax实现页面的局部加载
- 01-10delphi制作wav文件的方法
- 01-10使用C语言求解扑克牌的顺子及n个骰子
- 01-10SublimeText编译C开发环境设置
- 04-02jquery与jsp,用jquery
- 08-05织梦dedecms什么时候用栏目交叉功能?
- 01-10C#中split用法实例总结
- 08-05dedecms(织梦)副栏目数量限制代码修改
- 08-05DEDE织梦data目录下的sessions文件夹有什