Sql Server 查询表结构汇总

--1、看一下该数据库有哪些表
--方法1：
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%TabelDemo%'
--方法2：
SELECT * FROM sys.tables WHERE name LIKE '%TabelDemo%'

--2、表中包含有哪些字段
--方法1：
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%TabelDemo%' ORDER BY ORDINAL_POSITION
--方法2：
SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('TabelDemo') ORDER BY column_id
--方法3：
SELECT * FROM syscolumns where id=object_id('TabelDemo')  ORDER BY colorder

--3、查询表的所有字段以及字段类型
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '%TabelDemo%' ORDER BY ORDINAL_POSITION

-- 4、查看表的字段以及字段的注释
SELECT a.name 表名,b.name 字段名,C.value 字段注释 FROM sys.tables a 
INNER JOIN sys.columns b ON b.OBJECT_ID = a.OBJECT_ID
LEFT JOIN sys.extended_properties c ON c.major_id = b.OBJECT_ID and c.minor_id = b.column_id
WHERE a.name = 'TabelDemo'

--5、查看存储过程是否包含字段内容
SELECT obj.Name 过程名称, sc.TEXT 内容 FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%LabelName%' AND obj.xtype = 'P' 

--6、查看视图是否包含字段内容
SELECT obj.Name 视图名称, sc.TEXT 内容 FROM syscomments sc
INNER JOIN sysobjects obj ON sc.Id = obj.ID
WHERE sc.TEXT LIKE '%LabelName%' AND obj.xtype = 'V' 

--7、查看所有数据内容
SELECT TOP 10000 * FROM sysobjects obj