sql查看数据库所有表名(SQL提取数据库表名及字段名等信息代码示例)
类别:数据库 浏览量:2059
时间:2022-01-20 00:10:57 sql查看数据库所有表名
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变量等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对本站的支持!
您可能感兴趣
- sqlserver创建表结构时添加约束(SQL Server 通过with as方法查询树型结构)
- docker部署高性能服务器(Docker部署SQL Server 2019 Always On集群的实现)
- sql function函数的用法(SQl Function 创建函数实例介绍)
- sqlserver调试存储过程(sql server2008调试存储过程的完整步骤)
- mysql缓冲池(详解MySQL中的缓冲池buffer pool)
- mysql索引如何使用(MySQL 索引和数据表该如何维护)
- mysql索引原理及调优(MySQL索引机制程序员必知)
- mysqlgroupby用法(MySQL Group by的优化详解)
- mysql开启审计日志会导致性能下降(MySQL 一则慢日志监控误报的问题分析与解决)
- navicat配置远程访问mysql(解决Navicat无法连接 VMware中Centos系统中的 MySQL服务器的问题)
- sql查询地址中包含某个字段(SQL查询字段被包含语句)
- mysql的uuid说明(MySQL GTID全面总结)
- SQL中的cast()函数
- sqlleftjoin详解(SQL JOIN 连接详细介绍及简单使用实例)
- SQLServer中防止并发插入重复数据的方法详解(SQLServer中防止并发插入重复数据的方法详解)
- sql server 报错(sql server利用不同语种语言显示报错错误消息的方法示例)
- 马来西亚旅游攻略(马来西亚旅游攻略自由行攻略)
- 缅甸旅游攻略(缅甸旅游攻略必去景点推荐)
- 《庆余年2》新消息,原班人马,肖战特别出演,这才是最好的安排(庆余年2新消息原班人马)
- 宁夏灵武恐龙化石发现始末(宁夏灵武恐龙化石发现始末)
- 到了岁末 临门一脚 节点,天台综合督评会目标直指 全年红(到了岁末临门一脚)
- 寒假余额不满24小时,不如来一场说走就走的亲子阅读之旅(寒假余额不满24小时)
热门推荐
- 执行mysqldump命令后数据库无反应(关于xampp启动不了mysql数据库的解决方法)
- pythonmatplotlib怎么画区域(python matplotlib实现双Y轴的实例)
- SQLite数据类型
- SqlServer 英文单词全字匹配详解及实现代码(SqlServer 英文单词全字匹配详解及实现代码)
- php逻辑技巧图解(php版本CKEditor 4和CKFinder安装及配置方法图文教程)
- 宝塔mysql怎么设置优化(宝塔面板mysql内存占用高如何优化)
- docker和golang哪个好(使用Golang玩转Docker API的实践)
- 织梦cms怎么调用(织梦CMS采集时keywords字段无法添加过滤规则的解决方法)
- python怎么表示矩阵转置(Python实现的矩阵转置与矩阵相乘运算示例)
- docker集群安装教程(使用docker部署hadoop集群的详细教程)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9