SQL Server查询以获取表中的列列表，包括编号、数据类型、非空以及其来源。

23 浏览
0 Comments

SQL Server查询以获取表中的列列表，包括编号、数据类型、非空以及其来源。

我需要在 SQL Server 上编写一个查询，以获取特定表中的列列表，包括生成的编号、关联的数据类型（带长度）、是否为空（是则为Yes，否则为No），以及最后的来源，包括数据库和表名。

例如，查看AdventureWorksDW中的表[dbo].[DimProduct]，我可以看到：

CREATE TABLE [dbo].[DimProduct]
(
    [ProductKey] [INT] IDENTITY(1,1) NOT NULL,
    [ProductAlternateKey] [NVARCHAR](25) NULL,
    [ProductSubcategoryKey] [INT] NULL,
    [WeightUnitMeasureCode] [NCHAR](3) NULL,
    [SizeUnitMeasureCode] [NCHAR](3) NULL,
    [EnglishProductName] [NVARCHAR](50) NOT NULL,
    ...

我想创建一个选择语句来提供以下结果：

编号   名称                     类型            是否为空     来源
---------------------------------------------------------------------------------------------
1       ProductKey              int             否           AdventureWorksDW.dbo.DimProduct
2       ProductAlternateKey     nvarchar(25)    是           AdventureWorksDW.dbo.DimProduct
3       ProductSubcategoryKey   int             是           AdventureWorksDW.dbo.DimProduct
…       …                       …               …           …

0

1 答案

0 Comments

问题的原因是需要获取一个表中的列的列表，包括列的编号、数据类型、是否为空以及来源。解决方法是使用SQL Server查询来获取这些信息。

首先，在单个数据库中生成所有表的列表非常简单，只需要使用以下查询语句：

SELECT 
  [Number] = f.column_ordinal, 
  Name = f.name, 
  [Type] = f.system_type_name, 
  [Is Null] = CASE f.is_nullable WHEN 1 THEN 'Yes' ELSE 'No' END,
  Source = QUOTENAME(DB_NAME()) + N'.' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name)
FROM sys.tables AS t 
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_exec_describe_first_result_set
(
  N'SELECT * FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
  N'', 
  0
) AS f
ORDER BY Source, [Number];

如果只需要某个表的列列表，可以添加以下条件：

WHERE s.name = N'dbo'
  AND t.name = N'DimProduct'

但是，这样输出的Source列就没有实际用处，因为已经知道检索到的数据库、模式和表的信息。

对于未知数量和名称的数据库，稍微复杂一些。可以使用sp_ineachdb存储过程来处理（part 1，part 2）。

0