从数据库中每个表的每一列中获取数据|数据库服务器 [英] Get data from every column in every table in a database | SQL Server

问题描述

关于如何从每个表中检索每一列及其数据类型，以及许多其他信息，这些信息可以用这个查询以最短的方式进行总结，我已经看到了多个问题:

I have seen multiple questions on how to retrieve every column from every table along with its data type, among many other pieces of information which can be summarised in the shortest way with this query:

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS

但是，是否有可能从列和它们所属的行中获取所有数据并获得表格中的第一行?到目前为止，我还没有找到这样做的方法.是否可以这样做，也许还有一个 WHERE 条件，例如在返回之前检查表是否包含特定列的列表，例如:

However, is it possible to get all the data from the columns and the rows they belong to get the first row in the table alongside this? I have not found a way to do so thus far. Is it possible to do such, maybe also having a WHERE condition such as checking if the table contains a list of specific columns before returning it e.g.:

SELECT <AllTablesAndColumns+FirstRow> 
FROM <WhereTheyCanBeSelectedFrom> 
WHERE <TheTableHasTheseSpecificColumns>

这将返回表名、列名以及每行包含在这些列中的数据.

Which would return the table name, column name and the data contained within those columns for each row.

推荐答案

您可以构建动态查询:

DECLARE @sql NVARCHAR(MAX) = 
N'SELECT *
FROM (VALUES (1)) AS s(n)
<joins>';

DECLARE @joins NVARCHAR(MAX)= '';

SELECT @joins += FORMATMESSAGE('LEFT JOIN (SELECT TOP 1 * FROM %s ) AS sub%s
       ON 1=1' + CHAR(10), table_schema + '.' + table_name,
       CAST(ROW_NUMBER() OVER(ORDER BY 1/0) AS VARCHAR(10)))
FROM (SELECT DISTINCT table_schema, table_name
      FROM INFORMATION_SCHEMA.COLUMNS
      -- WHERE ...  -- custom logic based on column type/name/...
     ) s;

SET @sql = REPLACE(@sql, '<joins>', @joins);
PRINT @sql;

EXEC(@sql);

DBFiddle 演示

动态查询具有结构:

SELECT * 
FROM (VALUES (1)) AS s(n)   -- always 1 row
LEFT JOIN (SELECT TOP 1 * FROM dbo.tab1 ) AS sub1 ON 1=1 -- get single row
LEFT JOIN (SELECT TOP 1 * FROM dbo.tab2 ) AS sub2 ON 1=1 
LEFT JOIN (SELECT TOP 1 * FROM dbo.tabC ) AS sub3 ON 1=1 

请将其视为起点.您可以为每个子查询使用 WHERE 条件轻松扩展它并返回特定列而不是 *.

Please treat it as starting point. You could easily extend it with WHERE condition for each subquery and return specific columns instead of *.

带有 UNION ALL 的版本:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = COALESCE(@sql + ' UNION ALL', '') + 
FORMATMESSAGE(' SELECT TOP 1 tab_name=''%s'',col_name=''%s'',col_val=%s FROM %s'+CHAR(10) 
      ,table_name, column_name, column_name, table_schema + '.' + table_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'colV%'; 

PRINT @sql;  
EXEC(@sql);

DBFiddle Demo2

这篇关于从数据库中每个表的每一列中获取数据|数据库服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！