页面打开中,请稍候...
www.Gzu521.com
| GZU521.COM学习网 |
|
在 sql server 2005 中查询表结构及索引 -- 1. 表结构信息查询 -- =================================================== -- 表结构信息查询 -- 邹建 2005.08(引用请保留此信息) -- ==================================================== select tablename=case when c.column_id=1 then o.name else n’’ end, tabledesc=isnull(case when c.column_id=1 then ptb.[value] end,n’’), column_id=c.column_id, columnname=c.name, primarykey=isnull(idx.primarykey,n’’), [identity]=case when c.is_identity=1 then n’√’else n’’ end, computed=case when c.is_computed=1 then n’√’else n’’ end, type=t.name, length=c.max_length, precision=c.precision, scale=c.scale, nullable=case when c.is_nullable=1 then n’√’else n’’ end, [default]=isnull(d.definition,n’’), columndesc=isnull(pfd.[value],n’’), indexname=isnull(idx.indexname,n’’), indexsort=isnull(idx.sort,n’’), create_date=o.create_date, modify_date=o.modify_date from sys.columns c inner join sys.objects o on c.[object_id]=o.[object_id] and o.type=’u’ and o.is_ms_shipped=0 inner join sys.types t on c.user_type_id=t.user_type_id left join sys.default_constraints d on c.[object_id]=d.parent_object_id and c.column_id=d.parent_column_id and c.default_object_id=d.[object_id] left join sys.extended_properties pfd on pfd.class=1 and c.[object_id]=pfd.major_id and c.column_id=pfd.minor_id -- and pfd.name=’caption’ -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) left join sys.extended_properties ptb on ptb.class=1 and ptb.minor_id=0 and c.[object_id]=ptb.major_id -- and pfd.name=’caption’ -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) left join -- 索引及主键信息 ( select idxc.[object_id], idxc.column_id, sort=case indexkey_property(idxc.[object_id],idxc.index_id,idxc.index_column_id,’isdescending’) when 1 then ’desc’ when 0 then ’asc’ else ’’ end, primarykey=case when idx.is_primary_key=1 then n’√’else n’’ end, indexname=idx.name from sys.indexes idx inner join sys.index_columns idxc on idx.[object_id]=idxc.[object_id] and idx.index_id=idxc.index_id left join sys.key_constraints kc on idx.[object_id]=kc.[parent_object_id] and idx.index_id=kc.unique_index_id inner join -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( select [object_id], column_id, index_id=min(index_id) from sys.index_columns group by [object_id], column_id ) idxcuq on idxc.[object_id]=idxcuq.[object_id] and idxc.column_id=idxcuq.column_id and idxc.index_id=idxcuq.index_id ) idx on c.[object_id]=idx.[object_id] and c.column_id=idx.column_id -- where o.name=n’要查询的表’ -- 如果只查询指定表,加上此条件 order by o.name,c.column_id -- 2. 索引及主键信息 -- ====================================================== -- 索引及主键信息 -- 邹建 2005.08 --  www.knowsky.com-- ====================================================== select tableid=o.[object_id], tablename=o.name, indexid=isnull(kc.[object_id],idx.index_id), indexname=idx.name, indextype=isnull(kc.type_desc,’index’), index_column_id=idxc.index_column_id, columnid=c.column_id, columnname=c.name, sort=case indexkey_property(idxc.[object_id],idxc.index_id,idxc.index_column_id,’isdescending’) when 1 then ’desc’ when 0 then ’asc’ else ’’ end, primarykey=case when idx.is_primary_key=1 then n’√’else n’’ end, [uqique]=case when idx.is_unique=1 then n’√’else n’’ end, ignore_dup_key=case when idx.ignore_dup_key=1 then n’√’else n’’ end, disabled=case when idx.is_disabled=1 then n’√’else n’’ end, fill_factor=idx.fill_factor, padded=case when idx.is_padded=1 then n’√’else n’’ end from sys.indexes idx inner join sys.index_columns idxc on idx.[object_id]=idxc.[object_id] and idx.index_id=idxc.index_id left join sys.key_constraints kc on idx.[object_id]=kc.[parent_object_id] and idx.index_id=kc.unique_index_id inner join sys.objects o on o.[object_id]=idx.[object_id] inner join sys.columns c on o.[object_id]=c.[object_id] and o.type=’u’ and o.is_ms_shipped=0 and idxc.column_id=c.column_id -- inner join -- 对于一个列包含多个索引的情况,只显示第1个索引信息 -- ( -- select [object_id], column_id, index_id=min(index_id) -- from sys.index_columns -- group by [object_id], column_id -- ) idxcuq -- on idxc.[object_id]=idxcuq.[object_id] -- and idxc.column_id=idxcuq.column_id -- |
责任编辑：gzu521
您现在的位置:
