SQLServer 列出每个表的列和属性

原文: SQLServer 列出每个表的列和属性

       当需要整理一个数据库帮助文档是,可能需要列出库中每个表的列及其属性。这可能在开发一些接口或者外包给别的公司时有帮助。如果需要别人打开SQL Server Management Studio (SSMS)来一个一个查看,无疑是一种折磨。


       解决这个问题可以考虑使用系统的目录视图:sys.tables、sys.all_columns、sys.types


Sys.tables:


       提供数据库中每个表对应的一行数据。包括用户表和系统表。而其中的is_ms_shipped列,代表是否为系统表。这在你需要仅仅显式用户表的时候很有用。而不需要在sys.sysobject兼容性视图中通过type=’U’来筛选。


Sys.all_columns:


       数据库每一个对象的每一列都会返回一行,很多列和sys.type是相同的。但是有些列只能在sys.type中查找。


Sys.types:


      此目录视图存储系统或者用户自定义数据类型及它们的属性。本文中所需的是数据类型的名字,这列在sys.all_columns中是没有的。同时数据库的排序规则会影响sys.types,所以对于系统内置类型如text,ntext,vachar(),char(),nvarchar(),nchar()会因为数据库不同而不同。


       如非必须,最好只查询当前数据库的内容而不要跨数据库,因为这些视图是基于单个数据库的。运行以下语句:


USE AdventureWorks
GO
SELECT  OBJECT_SCHEMA_NAME(T.[object_id], DB_ID()) AS [架构名] ,
        T.[name] AS [表名] ,
        AC.[name] AS [列名] ,
        TY.[name] AS [系统数据类型] ,
        TY.is_user_defined AS [是否用户自定义类型],--1 = 用户定义类型,0 = SQL Server 系统数据类型
        AC.[max_length] [最大长度],
        AC.[precision] [精确度],--如果列包含的是数值,则为该列的精度;否则为0
        AC.[scale] [数值范围],--如果列包含的是数值,则为列的小数位数;否则为0
        AC.[is_nullable] [是否允许为空],
        AC.[is_ansi_padded][是否使用ANSI_PADDING]--1 = 如果列为字符、二进制或变量类型,则该列使用ANSI_PADDING ON 行为
FROM    sys.[tables] AS T
        INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
        INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
                                     AND AC.[user_type_id] = TY.[user_type_id]
WHERE   T.[is_ms_shipped] = 0
ORDER BY T.[name] ,
        AC.[column_id]



可以得到:


SQLServer 列出每个表的列和属性


由于某些原因需要在别的库上查询另外一个库的信息时,需要硬编码,如下,可以得到相同的结果:


USE [master] 
GO
SELECT  OBJECT_SCHEMA_NAME(T.[object_id], DB_ID('AdventureWorks')) AS [架构名] ,
        T.[name] AS [表名] ,
        AC.[name] AS [列名] ,
        TY.[name] AS [系统数据类型] ,
        TY.is_user_defined AS [是否用户自定义类型],--1 = 用户定义类型,0 = SQL Server 系统数据类型
        AC.[max_length] [最大长度],
        AC.[precision] [精确度],--如果列包含的是数值,则为该列的精度;否则为0
        AC.[scale] [数值范围],--如果列包含的是数值,则为列的小数位数;否则为0
        AC.[is_nullable] [是否允许为空],
        AC.[is_ansi_padded][是否使用ANSI_PADDING]--1 = 如果列为字符、二进制或变量类型,则该列使用ANSI_PADDING ON 行为
FROM    AdventureWorks.sys.[tables] AS T
        INNER JOIN AdventureWorks.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
        INNER JOIN AdventureWorks.sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id]
                                     AND AC.[user_type_id] = TY.[user_type_id]
WHERE   T.[is_ms_shipped] = 0
ORDER BY T.[name] ,
        AC.[column_id]



最后,通过这些查询结果,可以把数据导出到excel里面供使用。
上一篇:关涛:接手一个6年的平台型系统,我是如何带领团队破局前行的


下一篇:人工智能进阶心得:在战斗中学习,在学习中战斗