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

最新推荐文章于 2025-08-14 19:30:08 发布

新田

最新推荐文章于 2025-08-14 19:30:08 发布

阅读量758

点赞数

CC 4.0 BY-SA版权

分类专栏：笔记文章标签： sql join properties table null

本文链接：https://blog.youkuaiyun.com/tem168/article/details/6327822

笔记专栏收录该内容

3 篇文章

订阅专栏

本文提供了一条用于获取MSSQL数据库中所有表及其字段详细信息的经典SQL语句，包括表说明、字段名、字段说明、字段类型、长度、小数位数、是否允许为空、默认值、是否为标识字段及是否为主键。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--
获取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
'