系统视图，系统表，系统存储过程的使用
获取数据库中用户表信息
1、获取特定库中所有用户表信息
select * from sys.tables
select * from sys.objects where type='U' --用户表
第二条语句中当type='S'时是系统表
2、获取表的字段信息
select * from sys.columns where object_id=object_id('表名')
select * from syscolumns where id=OBJECT_ID('表名' )
3、获取当前库中表的字段及类型信息
（1）select '字段名'=a.name,
'类型名'=b.name,
'字段长度'=a.max_length,
'参数顺序'=a.column_id
from sys.columns a left join sys.types b
on a.user_type_id=b.user_type_id
where object_id=object_id('表名')
syscolumns与sys.columns表用法类似。
获取索引或主键信息
获取对象及对应的索引的信息
select '对象名'=A.name,
'对象类型'=a.type,
'索引名'=B.name,
'索引类型'=case b.type when 1 then '聚集索引'
when2 then '非聚集索引'
when3 then 'xml索引'
else'空间索引' end,
'主键否'=case when b.is_primary_key=1 then '主键'
else'' end
FROM sys.objects A JOIN sys.indexes B ON A.object_id=B.object_id
WHERE A.type='U' AND B.name IS NOT NULL order by a.name
获取表的主键及对应的字段
（1）select '表名'=d.name ,'主键名'=a.name,'字段名'=c.name
from sys.indexes a join sys.index_columns b
on a.object_id=b.object_id and a.index_id=b.index_id
join sys.columns c on a.object_id=c.object_id and
c.column_id=b.column_id
join sys.objects d on d.object_id=c.object_id
where a.is_primary_key=1
（2）SELECT '表名'=OBJECT_NAME(b.parent_obj),
'主键名'=c.name,
'字段名'=a.name
FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d
WHERE b.xtype = 'PK' AND b.parent_obj = a.id AND c.id = a.id
AND b.name =c.name AND d.id = a.id
AND d.indid = c.indid AND a.colid = d.colid
（3）select '所属架构'=s.name ,
'表名'=t.name,
'主键名'=k.name ,
'列名'=c.name,
'键列序数'=ic.key_ordinal
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id where k.type = 'pk';
（4）使用系统存储过程获取指定表的主键信息
EXEC sp_pkeys '表名' --表名只能是当前数据库下的单独表名不能带上架构名
查询哪些表创建了主键
select '表名'=a.name from
(select name,object_id from sys.objects where type='u') a
left join
sys.indexes b
on a.object_id=b.object_id and b.is_primary_key=1
where b.name is not null
注：查询哪些表没有创建主键，将where条件改成 is null 即可。
查找视图信息
查看视图属性信息
exec sp_help '视图名'
查看创建视图脚本
exec sp_helptext '视图名'
查看当前数据库所有视图基本信息
select * from sys.views
select * from sys.objects where type='V'
select * from INFORMATION_SCHEMA.VIEWS
查看视图对应的字段及字段属性
select '视图名'=a.name,
'列名'=b.name,
'字段类型'=TYPE_NAME(b.system_type_id),
'字段长度'=b.max_length
from sys.views a join sys.columns b
on a.object_id=b.object_id order by a.name
获取视图中的对象信息
exec sp_depends '视图名'
查看存储过程信息
1、基本信息
select * from sys.procedures
select * from sys.objects where type='P'
2、查看存储过程创建文本
sp_helptext 存储过程名称
select text from syscomments where id=object_id (存储过程名称)
3、查看存储过程的参数信息
(1)select '参数名称' = name,
'类型' = type_name(xusertype),
'长度' = length,
'参数顺序' = colid
from syscolumns
where id=object_id(存储过程名称)
(2)select '参数名称' = name,
'类型' = type_name(system_type_id),
'长度' = max_length,
'参数顺序' =parameter_id
from sys.parameters
where object_id=object_id(存储过程名称)
返回当前环境中可查询的指定表或视图的列信息。
exec sp_columns 表名
select * from sys.columns where object_id=OBJECT_id(表名)
select * from sys.syscolumns where id=OBJECT_ID(表名)
select * from information_schema.columns where TABLE_NAME=表名
查询存储过程或函数的参数的详细信息
select * from sys.p