在 SQL Server 2005 中查询表结构及索引

在 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(引用请保留此信息)
-- ========================================================================
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
--