使用变量作为表名

问题描述：

我需要在 SQL Server 数据库 (2000) 中查找包含一个列值的表.

I need to find tables in a SQL Server database (2000) that contain one value for a column.

我可以使用以下查询来输出包含 my_column 的可能候选表的列表:

I can use the following query to output a list of possible candidate tables containing my_column:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'my_column'

我想了解的是以下带有结果的伪查询:

What I would like to get at, is the following pseudo query with result:

select '$TABLE_NAME', count(*) from $TABLE_NAME where my_column = '12345'

table01 1
table02 5
table03 0
table04 3

或者更普遍的表述:是否可以使 FROM 子句变量?

Or more generally formulated: Is it possible to make the FROM-clause variable?

答

唯一可能的方法是使用动态 SQL:

Only way it's possible is by using dynamic SQL:

declare @stmt nvarchar(max), @value nvarchar(max)

select @stmt = isnull(@stmt + ' union all ', '') + '
    select ''' + TABLE_NAME +  ''', count(*) from ' +  TABLE_NAME + ' where my_column = @value' 
from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'my_column'

select @value = '12345'

exec sp_executesql
    @stmt = @stmt,
    @params = N'@value nvarchar(max)',
    @value = @value

更新:
对于 SQL 2000，你可以使用 nvarchar(4000) 如果你有很多表，你可以使用临时表 + 游标:

update:
For SQL 2000 you can use nvarchar(4000) If you have really big number of tables, you can use temporary table + cursor:

create table #Temp_Results (table_name nvarchar(128), cnt int)
declare @stmt nvarchar(4000), @value nvarchar(128)

declare t_cursor cursor local fast_forward for
    select 
        'select ''' + TABLE_NAME +  ''', count(*) from ' +  TABLE_NAME + ' where id = @value'
    from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME = 'name'

select @value = 1

open t_cursor
fetch t_cursor into @stmt
while @@fetch_status = 0
begin
    insert into #Temp_Results
    exec sp_executesql
        @stmt = @stmt,
        @params = N'@value nvarchar(128)',
        @value = @value

    fetch t_cursor into @stmt
end

close t_cursor
deallocate t_cursor

select * from #Temp_Results

相关推荐