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]

可以得到：

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

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里面供使用。