---获取表和视图信息
select * from information_shcema.tables---查看当前数据库中当前用户有权限查看的所有表和视图信息
---获取视图信息
select * from information_schema.views
---获取列信息
select * from information_schema.columns
如果需要查看指定表或视图的列情况,可以使用下面的语句。
select
*
from
information_schema.columns
where
table_catalog='数据库名'
and
table_name='表名'
---获取视图中列的信息
select * from information_schema.view_column_usage
---获取列信息
select * from sys.columns
将sys.columns与系统视图sys.objects和sys.types关联,获得列的一些详细信息,例如
select
o.name as 表名,
c.name as 列名,
t.name as 数据类型,
c.max_length as 长度,
c.precision as精度,
c.scale as 小数位数,
case c.is_nullable when 1 then '是' else '否' end as 是否允许空,
case c.is_identity when 1 then '是' else '否' end as 标识列,
from
sys.columns c inner join sys.objects o
on
o.object_id=c.object_id
inner join
sys.types t
on
c.system_type_id=t.system_type_id
where
o.name='表名' and t.name<>'sysname'
order by
c.column_id
go
--获取视图中包含表的信息
select * from information_schema.view_table_usage
go
--获取所有数据库对象的信息
select * from sys.objects
--绑定规则
exec sp_bindrule '规则名','对象名'
例如
exec sp_bindrule 'sexrule','employees.sex'
--解除绑定规则
exec sp_unbindrule '对象名'
--删除规则
在删除规则前,需要调用sp_unbindrule存储过程解除该规则的绑定,例如
exec sp_unbindrule 'employees.sex'
drop rule sexrule
--查看表的索引信息
exec sp_helpindex tb
--结合sys.indexes和sys.index_columns,sys.objects,sys.columns查询索引所属的表或视图的信息
select
o.name as 表名,
i.name as 索引名,
c.name as 列名,
i.type_desc as 类型描述,
is_primary_key as 主键约束,
is_unique_constraint as 唯一约束,
is_disabled as 禁用
from
sys.objects o
inner join
sys.indexes i
on
i.object_id=o.object_id
inner join
sys.index_columns ic
on
ic.index_id=i.index_id and ic.object_id=i.object_id
inner join
sys.columns c
on
ic.column_id=c.column_id and ic.object_id=c.object_id
go
--根据表名和列名查看索引信息
selectobject_Name(a.object_id) as TableName,a.Name as IndexName,c.Name as ColName, description=a.type_desc,a.is_unique,a.is_primary_key,a.is_unique_constraintfrom sys.indexes a join sys.index_columns b on a.Object_id=b.Object_idand a.index_id=b.index_id join sys.columns c on c.object_id=a.object_idand c.column_id=b.column_idwhereobjectproperty(a.object_id,'IsUserTable')=1and a.Object_id=object_id('表名') AND c.Name='列名'
---获取表和视图信息select * from information_shcema.tables---查看当前数据库中当前用户有权限查看的所有表和视图信息---获取视图信息select * from information_schema.views---获取列信息select * from information_schema.columns如果需要查看指定表或