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变量等,有什么问题可以随时留言,小编会及时回复大家的。感谢朋友们对本站的支持!
您可能感兴趣
- sqlserver2008数据库的备份(sql server 2008 压缩备份数据库20g)
- sqlserver字符串格式化(SQL server中字符串逗号分隔函数分享)
- sqlserver日志被删除(sql server日志处理不当造成的隐患详解)
- 图片如何存放在mysql中(将图片保存到mysql数据库并展示在前端页面的实现代码)
- mysql的innodb引擎数据结构(MySQL InnoDB架构的相关总结)
- mysql 命令与sqlserver的区别大么(MySQL系列之执行SQL 语句时发生了什么?)
- SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待(SQLServer中使用扩展事件获取Session级别的等待信息及SQLServer 2016中Session级别等待)
- mybatis测试出现空指针(Mybatis非配置原因,导致SqlSession was not registered for synchronization异常)
- apache搭建php环境(Windows上安装Apache2、PHP5、MySQL5及与Resin配合实现多系统之整合)
- 常用的mysql查询语句(MySQL查询重写插件的使用)
- oracle和mysql查询所有表(Oracle、MySQL和SqlServe三种数据库分页查询语句的区别介绍)
- docker怎么创建mysql服务(Docker部署MySQL8集群一主二从的实现步骤)
- mysql如何删除整个表及数据(Mysql删除数据以及数据表的方法实例)
- navicat中如何使用sql语句(Navicat Premium操作MySQL数据库执行sql语句)
- thinkphp框架切换数据库(ThinkPHP3.2.3框架实现执行原生SQL语句的方法示例)
- mysql数据类型和使用方法(MySQL数据类型全解析)
- 天热没胃口 这道菜开胃又下饭,2个小技巧新手一学就会(这道菜开胃又下饭)
- 指天椒紫苏爆炒牛肉(指天椒紫苏爆炒牛肉)
- 谷雨前,吃牛羊肉别忘了吃河鲜,除湿还清热,加紫苏一炒特解馋(吃牛羊肉别忘了吃河鲜)
- 紫苏牛肉锅里滚一滚,香的鼻子都要掉了(紫苏牛肉锅里滚一滚)
- 每天都吃水果的好处(每天吃水果的好处与功效)
- 苹果15价格(苹果15价格512g官网)
热门推荐
- vs远程调试
- html5显示中心代码(HTML5中的Web Notification桌面通知功能的实现方法)
- dedecms标签怎么用(dedecms三级栏目调用方法)
- html5添加背景(Html5实现首页动态视频背景的示例代码)
- dede的数据库在哪(dede后台Fatal error: Allowed memory size of 8388608 bytes exhausted问题的解决方)
- kubernetes是如何调度的(云原生技术kubernetes调度单位pod的使用详解)
- mysql not exists用法(mysql中EXISTS和IN的使用方法比较)
- laravel跨域设置(解决Laravel自定义类引入和命名空间的问题)
- 云服务器可以用来做啥(云服务器的作用你清楚吗?)
- laravel数据库配置(Laravel如何同时连接多个数据库详解)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9