Hot For Coding

SQLServer读取库里所有表名

在SQLServer里要获取库中所有表名,并不能像MySQL那样直接show table就搞定

mysql> use demo
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| a              |
| b              |
| c              |
+----------------+
3 rows in set (0.00 sec)

在SQLServer中要读取库中所有表名需要先了解sysobjects系统表,什么是sysobjects系统表呢?根据谷歌搜索有这么一段话

SQL Server sysobjects 表结构 在数据库内创建的每个对象(约束、默认值、日志、规则、存储过程等)在表中占一行。只有在tempdb内,每个临时对象才在该表中占一行

根据描述,既然是一张表,我们就可以select下它有那些内容

1> select top 1 * from sysobjects 
2> go
name    id      xtype   uid     info    status  base_schema_ver replinfo        parent_obj      crdate  ftcatid schema_ver      stats_schema_ver        type    userstat        sysstat indexdel        refdate version deltrig instrig updtrig     seltrig category        cache
sysobjects      1       S       1       25      -536870909      96      0       0       2000-08-06 01:29:12     0       96      0       S       1       113     0       2000-08-06 01:29:12     0       0       0       0       0       2  0
(1 row affected)

查询结果有好多信息,跟前面表格列出的信息一一对应,这里我们只想读取所有表名,那么我们可以加个where条件

1> select name from sysobjects where xtype='u'      
2> go
name
s_honor
s_probability
s_room_allow
s_room_day_jifen
s_room_jifen
s_room_jifen_log
s_sign_count
s_sign_day_room
s_sign_log
s_sign_room_daycount
s_sign_tmp
s_udian_log
s_udian_total
s_user_day_jifen
s_user_honor
s_user_jifen
s_user_jifen_log
(17 rows affected)

这样就可以把库中所有表的名称都列出来了

分享

TITLE: SQLServer读取库里所有表名

LINK: https://www.qttc.net/284-sqlserver-read-all-the-tables-name.html

NOTE: 原创内容,转载请注明出自琼台博客