获取MS SQL库数据字典的经典SQL语句

--
获取MS SQL库数据字典的经典SQL语句






SELECT
 sysobjects.name 
AS
 
[
table
]
, sysproperties.
[
value
]
 
AS
 表说明, 

      syscolumns.name 
AS
 field, properties.
[
value
]
 
AS
 字段说明, systypes.name 
AS
 type, 

      syscolumns.length, 
ISNULL
(
COLUMNPROPERTY
(syscolumns.id, syscolumns.name, 

      
'
Scale
'
), 
0
) 
AS
 小数位数, syscolumns.isnullable 
AS
 
isnull
, 

      
CASE
 
WHEN
 syscomments.
text
 
IS
 
NULL
 

      
THEN
 
''
 
ELSE
 syscomments.
text
 
END
 
AS
 
[
Default
]
, 

      
CASE
 
WHEN
 
COLUMNPROPERTY
(syscolumns.id, syscolumns.name, 
'
IsIdentity
'
) 

      
=
 
1
 
THEN
 
'
√
'
 
ELSE
 
''
 
END
 
AS
 标识, 
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 
=
 syscolumns.id 
AND
 colid 
=
 syscolumns.colid))) 

      
THEN
 
'
√
'
 
ELSE
 
''
 
END
 
AS
 主键


FROM
 syscolumns 
INNER
 
JOIN


      sysobjects 
ON
 sysobjects.id 
=
 syscolumns.id 
INNER
 
JOIN


      systypes 
ON
 syscolumns.xtype 
=
 systypes.xtype 
LEFT
 
OUTER
 
JOIN


      sysproperties properties 
ON
 syscolumns.id 
=
 properties.id 
AND
 

      syscolumns.colid 
=
 properties.smallid 
LEFT
 
OUTER
 
JOIN


      sysproperties 
ON
 sysobjects.id 
=
 sysproperties.id 
AND
 

      sysproperties.smallid 
=
 
0
 
LEFT
 
OUTER
 
JOIN


      syscomments 
ON
 syscolumns.cdefault 
=
 syscomments.id


WHERE
 (sysobjects.xtype 
=
 
'
U
'