SqlTool/查询/查询数据字典.sql

--按照Schema查询数据字典

select 表名=case when colorder = -1 then name else '' end,

       表说明=case when colorder = -1 then isnull(fvalue, '') else '' end,

       字段名,
       字段说明,
       类型,
       长度,
       主键,
       允许空,
       默认值

from (
         SELECT   d.name,

                  f.value as fvalue,

             字段名=a.name,

             字段说明=isnull(g.[value], ''),

             类型=b.name,

             长度=COLUMNPROPERTY(a.id, a.name, 'PRECISION'),

             主键=case
                    when exists(SELECT 1
                                FROM sysobjects
                                where xtype = 'PK'
                                  and name in (
                                    SELECT name
                                    FROM sysindexes
                                    WHERE indid in (
                                        SELECT indid
                                        FROM sysindexkeys
                                        WHERE id = a.id AND colid = a.colid
                                    ))) then '√'
                    else '' end,

             允许空=case when a.isnullable = 1 then '√' else '×' end,

             默认值=isnull(e.text, ''),

                  a.id,
                  a.colorder

         FROM syscolumns a

                  left join systypes b on a.xtype = b.xusertype

                  inner join sysobjects d on a.id = d.id and d.xtype = 'U' and d.name <> 'dtproperties'

                  left join syscomments e on a.cdefault = e.id

                  left join sys.extended_properties g on a.id = g.major_id and a.colid = g.minor_id

                  left join sys.extended_properties f on d.id = f.major_id and f.minor_id = 0

                  left join sys.tables t on t.object_id = d.id

                  left join sys.schemas s on s.schema_id = t.schema_id

         where s.name = 'SL_pc_Schema'

         union all

         select d.name,
                isnull(f.value, ''),
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                -1,
                -1

         from sysobjects d
                  left join sys.extended_properties f on d.id = f.major_id and f.minor_id = 0

                  left join sys.tables t on t.object_id = d.id

                  left join sys.schemas s on s.schema_id = t.schema_id

         where s.name = 'SL_pc_Schema'
     ) as tmp
order by name, id, colorder