select a.name from sys.objects a inner join sys.columns b on a.object_id=b.object_id and b.name='col'
qianjin036a,补充一下,这个可以同时查到临时表和视图吗?我希望都包含进去
---获取表和视图信息 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
--查询用户表对象信息 select Tab.Name as [表名],Tab.create_date as [创建时间],Tab.modify_date as [最后修改时间], Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度], CASE WHEN pk.is_primary_key= 1 THEN 'Y' ELSE 'N' end as [是否主键], CASE WHEN Col.is_identity = 1 THEN 'Y' else 'N'end as [是否自增] , identity_columns.seed_value as [自增种子],identity_columns.increment_value as [自增步长], case when Col.is_nullable = 1 then 'Y' else 'N' END AS [是否允许为NULL], Def.text as [默认值],case when Col.is_computed = 1 then 'Y' else 'N' END as [是否计算列] , computed_columns.definition as [计算公式],Col_Desc.Value as [列备注] from sys.objects Tab inner join sys.columns Col on Tab.object_id =Col.object_id inner join sys.types Type on Col.system_type_id = Type.system_type_id left join sys.identity_columns identity_columns on Tab.object_id = identity_columns.object_id and Col.column_id = identity_columns.column_id left join syscomments Def on Col.default_object_id = Def.ID left join( select index_columns.object_id,index_columns.column_id,indexes.is_primary_key from sys.indexes indexes inner join sys.index_columns index_columns on indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id where indexes.is_primary_key = 1/*主键*/ ) PK on Tab.object_id = PK.object_id AND Col.column_id = PK.column_id left join sys.computed_columns computed_columns on Tab.object_id =computed_columns.object_id and Col.column_id = computed_columns.column_id left join sys.extended_properties Col_Desc on Col_Desc.major_id=Tab.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1 where Tab.type = 'U' and Tab.Name not like'sys%' order by Tab.create_date--查询所有视图 select views.Name as [视图名],Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度] --,Col_Desc.Value as Col_Description from sys.views views inner join sys.columns Col on views.object_id = Col.object_id inner join sys.types Type on Col.system_type_id = Type.system_type_id --left join sys.extended_properties Col_Desc -- on Col_Desc.major_id=views.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1 order by Create_Date --查询外键约束 select FK_Name as [外键名],Parent_Tab_Name as [外键表], [外键列]=stuff((select ','+[Parent_Col_Name] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U' inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U' inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id and Col.referenced_column_id = Referenced_Col.column_id )t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, ''), Referenced_Tab_Name as [主键表], [主键列]=stuff((select ','+[Referenced_Col_Name] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U' inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U' inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id and Col.referenced_column_id = Referenced_Col.column_id )t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, '') --as [外键列] from ( select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name, Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name from sys.foreign_keys FK inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U' inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id and Col.parent_column_id = Parent_Col.column_id inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U' inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id and Col.referenced_column_id = Referenced_Col.column_id )tb group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name
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
select Tab.Name as [表名],Tab.create_date as [创建时间],Tab.modify_date as [最后修改时间],
Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度],
CASE WHEN pk.is_primary_key= 1 THEN 'Y' ELSE 'N' end as [是否主键],
CASE WHEN Col.is_identity = 1 THEN 'Y' else 'N'end as [是否自增] ,
identity_columns.seed_value as [自增种子],identity_columns.increment_value as [自增步长],
case when Col.is_nullable = 1 then 'Y' else 'N' END AS [是否允许为NULL],
Def.text as [默认值],case when Col.is_computed = 1 then 'Y' else 'N' END as [是否计算列] ,
computed_columns.definition as [计算公式],Col_Desc.Value as [列备注]
from sys.objects Tab inner join sys.columns Col on Tab.object_id =Col.object_id
inner join sys.types Type on Col.system_type_id = Type.system_type_id
left join sys.identity_columns identity_columns on Tab.object_id = identity_columns.object_id and Col.column_id = identity_columns.column_id
left join syscomments Def on Col.default_object_id = Def.ID
left join(
select index_columns.object_id,index_columns.column_id,indexes.is_primary_key
from sys.indexes indexes inner join sys.index_columns index_columns
on indexes.object_id = index_columns.object_id and indexes.index_id = index_columns.index_id
where indexes.is_primary_key = 1/*主键*/
) PK on Tab.object_id = PK.object_id AND Col.column_id = PK.column_id
left join sys.computed_columns computed_columns on Tab.object_id =computed_columns.object_id and Col.column_id = computed_columns.column_id
left join sys.extended_properties Col_Desc on Col_Desc.major_id=Tab.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1
where Tab.type = 'U' and Tab.Name not like'sys%'
order by Tab.create_date--查询所有视图
select views.Name as [视图名],Col.Name as [列名] ,Type.name as [数据类型],Col.max_length as [字段长度]
--,Col_Desc.Value as Col_Description
from sys.views views
inner join sys.columns Col on views.object_id = Col.object_id
inner join sys.types Type on Col.system_type_id = Type.system_type_id
--left join sys.extended_properties Col_Desc
-- on Col_Desc.major_id=views.object_id and Col_Desc.minor_id=Col.Column_id and Col_Desc.class=1
order by Create_Date
--查询外键约束
select FK_Name as [外键名],Parent_Tab_Name as [外键表],
[外键列]=stuff((select ','+[Parent_Col_Name] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, ''),
Referenced_Tab_Name as [主键表],
[主键列]=stuff((select ','+[Referenced_Col_Name] from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)t where FK_Name=tb.FK_Name and Parent_Tab_Name = tb.Parent_Tab_Name and Referenced_Tab_Name = tb.Referenced_Tab_Name for xml path('')), 1, 1, '')
--as [外键列]
from (
select FK.name as FK_Name,Parent_Tab.Name as Parent_Tab_Name,Parent_Col.Name as Parent_Col_Name,
Referenced_Tab.Name as Referenced_Tab_Name,Referenced_Col.Name as Referenced_Col_Name
from sys.foreign_keys FK
inner join sys.foreign_key_columns Col on FK.Object_ID = Col.constraint_object_id
inner join sys.objects Parent_Tab ON Col.parent_object_id = Parent_Tab.Object_ID and Parent_Tab.TYPE = 'U'
inner join sys.columns Parent_Col on Parent_Tab.Object_ID = Parent_Col.object_id
and Col.parent_column_id = Parent_Col.column_id
inner join sys.objects Referenced_Tab ON Col.referenced_object_id = Referenced_Tab.Object_ID and Referenced_Tab.TYPE = 'U'
inner join sys.columns Referenced_Col on Referenced_Tab.Object_ID = Referenced_Col.object_id
and Col.referenced_column_id = Referenced_Col.column_id
)tb
group by FK_Name,Parent_Tab_Name,Referenced_Tab_Name
临时表要到tempdb中去找,视图的也能找到.