数据库表信息及表字段信息查询——SQL视图表

 

一、SYSxxx 与SYS.xx 与 INFORMATION_SCHEMA.xx 的区别：(xx 表示后缀查询的表名）

    1.本质不同

      SYSxx 是SQLServer微软自带系统表或函数

      sys.xx 是SQL Server从2005版本起引入的新的系统级视图或函数

      其实INFORMATION_SCHEMA.xx 和 sys.xx 二者从本质来说都是视图或函数，但前者是信息架构视图，是符合ISO标准的 

   2.存储数据不同,查询是的字段就不同

  例如都是存储数据的所有表

   SYScolumns 的字段名称是 ‘name’，

   sys.columns 的字段名称是 ‘name’

   INFORMATION_SCHEMA.table 的字段名称是 ‘table_name’ 

    

*同理其他表一样和columns后缀一样使用，只是每张表的存储数据内容不一样

其他表详见微软系统视图或函数 https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms187997(v=sql.90)

二、存储数据库的所有表查询（以SYSxx为例）

1.sysobjects —— 存储此数据库里的所有对应类型（XTYPE）的表、函数或者存储过程等。

例如：查询数据库创建的所有表的表名

  select * from sysobjects where XTYPE='U'     

--------------------------------------------------------------------------------------------

sysobjects 表中 xtype字段包含的值的意思如下：

AF = Aggregate function (CLR) 聚合函数(CLR) 

C = CHECK constraint CHECK约束 

D = Default or DEFAULT constraint 默认或DEFAULT约束 

F = FOREIGN KEY constraint 外键约束 

L = Log 日志 

FN = Scalar function 标量函数 

FS = Assembly (CLR) scalar-function 组装(CLR)标量函数 

FT = Assembly (CLR) table-valued function 程序集(CLR)表值函数 

IF = In-lined table-function 内嵌表函数 

IT = Internal table 内部表 

P = Stored procedure 存储过程 

PC = Assembly (CLR) stored-procedure 组装(CLR)存储过程 

PK = PRIMARY KEY constraint (type is K) 主键约束（类型是K） 

RF = Replication filter stored procedure 制筛选存储过程 

S = System table 系统表 

SN = Synonym 同义词 

SQ = Service queue 服务序列 

TA = Assembly (CLR) DML trigger 装配(CLR) DML触发器 

TF = Table function 表函数 

TR = SQL DML Trigger 触发器 

TT = Table type 

U = User table用户表 

UQ = UNIQUE constraint (type is K) 唯一约束（类型是K） 

V = View 视图 

X = Extended stored procedure 扩展存储过程

-------------------------------------------------------------------------------------

2.syscolumns —— 存储此数据库里的所创建表里的所有字段名称，此名称对应sysobjects的id列。

例如：查询数据库'TB' 表里所有的字段名称

select name from syscolumns where id=(SELECT id FROM SYSOBJECTS WHERE name='tb')

例如：我们要判断某个表是否存在某个字段，如果不存在则添加该字段并且绑定一个默认值

使用syscolumns 查询：

if not exists
(
select * from syscolumns where id=(SELECT id FROM SYSOBJECTS WHERE name='T') and name='ID'     --查询ID字段在T表中是否创建有
)
ALTER TABLE T
ADD ID VARCHAR(10) NULL DEFAULT ' ';                                                                                                        --如果没有则创建ID表字段，并设置可为空，默认空值

 

使用INFORMATION_SCHEMA.columns 查询：

if not exists
(
select column_name from INFORMATION_SCHEMA.columns where table_name = 'T' and column_name = 'ID'     --查询ID字段在T表中是否创建有
)
ALTER TABLE T
ADD ID VARCHAR(10) NULL DEFAULT ' ';                                                                                                                 --如果没有则创建ID表字段，并设置可为空，默认空值

 

 

3.systypes ——存储此数据库里syscolumns表里的所有字段类型

syscolumns表里面查询得到时xtype就是数据类型,但是这个xtype是数字的,通过数字来与systypes表的xtype数据类型配对出name字段就是字段类型

例如：查询数据库'TB' 表里所有的字段的数据类型

select c.name,c.xtype,t.name from syscolumns c,systypes t
where c.id=(select id from sysobjects where name='tb' and xtype = 'U')
and c.xtype=t.xtype

 

根据syscolumns表查询'TB'表的数据：

Sql ——构造动态SQL语句执行

DECLARE @sql VARCHAR(max)
DECLARE @col VARCHAR(1000),
@pid varchar(50),
@xtype varchar(10),
@name varchar(50),
@name1 varchar(50)
set @xtype='u'
set @name='tb'
set @pid='007'
set @name1 ='龙华'
SELECT @col =STUFF((SELECT ','+name FROM syscolumns where id=(select max(id) from sysobjects where xtype=@xtype and name=@name)
AND sys.syscolumns.xtype= 167 for xml path('')),1,1,'')
--select id,pid,name from tb where pid=007and name LIKE '龙华%'
SET @sql='select '+@col +' from tb where pid=''' +@pid+''' and name LIKE '''+convert(varchar,@name1)+ '%'''
PRINT(@SQL)
EXEC(@sql)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------