SQL Server 如何使用 information_schema.columns 或 sys.tables 从表列表中进行选择? [英] SQL Server how to select from a list of tables using information_schema.columns or sys.tables?

问题描述

将数据库的所有表作为行放入Table_List.

putting into Table_List all tables of the database as rows.

SELECT [table_name]
INTO Table_List
FROM INFORMATION_SCHEMA.TABLES

用一个表从多个表中选择

selecting from multiple tables with a table

SELECT [common column in each table ]
FROM [Table_List]

此查询无效.

我还尝试将行放入字符串列表(表名)中，然后使用 FROM 来选择它们.但效果不佳.

I also tried with putting the rows into a list of strings (table names) and then using FROM to select them. But didn't work as well.

有人知道有没有办法使用存储在表或列表中的SELECT * FROM tables names?

Does someone know if there is a way to use the SELECT * FROM tables names stored into a table or list?

我需要这个是因为表的名称或每个月都在变化，因此使用 sys.table 或 INFORMATION_SCHEMA.TABLES 关键字进行调用?

I need this because the tables names or changing every month and are thus called with the sys.table or INFORMATION_SCHEMA.TABLES keywords?

推荐答案

假设我理解了这个问题，您想从该列所在的所有表中选择特定列的所有值.
为此，您需要使用动态 SQL.
这是一种方法:

Assuming I understand the question, you want to select all the values of a specific column from all the tables where this column exists.
For that, you need to use dynamic SQL.
Here is one way to do it:

DECLARE @Sql nvarchar(4000) -- You might need nvarchar(max) here

SELECT @Sql = STUFF(
        (       
            SELECT ' UNION ALL SELECT Id, '''+ TABLE_NAME +''' As TableName FROM '+ TABLE_NAME
            FROM Information_schema.Columns
            WHERE Column_Name = 'ID'
            FOR XML PATH('')
        ), 1, 11, '')

EXEC(@Sql)

这将返回所有存在 Id 列的表中的所有 Id 值，以及该列所在的表名.

This will return all Id values from all tables where there is an Id column, along with the table name where it exists.

请注意，它要求所有 id 列都具有相同的数据类型，或者至少是可以相互隐式转换的数据类型.

Please note that it requires that all the id columns will have the same data type, or at least data types that can be implicitly converted to each other.

这篇关于SQL Server 如何使用 information_schema.columns 或 sys.tables 从表列表中进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！