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