Use A Variable For Table Name

Possible Duplicate: how do find the number of rows in a table when the table name is in a variable? I need to find tables in a SQL Server database (2000) that contain one value

Solution 1:

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

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

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

declare t_cursor cursorlocal fast_forward forselect'select '''+ TABLE_NAME +''', count(*) from '+  TABLE_NAME +' where id = @value'from INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME ='name'select@value=1open t_cursor
fetch t_cursor into@stmt
while @@fetch_status =0begininsertinto #Temp_Results
    exec sp_executesql
        @stmt=@stmt,
        @params= N'@value nvarchar(128)',
        @value=@valuefetch t_cursor into@stmtendclose t_cursor
deallocate t_cursor

select*from #Temp_Results

Solution 2:

You can use the sp_MSforeachtable undocumented stored procedure to make the FROM clause variable.Here is an article that shows how to use it: sp_msForEachTable examples

Solution 3:

If I understand the question, you want a list of tables containing a given column name and a record count from each table, yes? If so

select o.name as"Table Name", i.rowcnt as"Rows"from sysobjects o
inner join sysindexes i on o.id = i.id
where i.indid in (0,1)
and o.id in
    (selectdistinct id from syscolumns where name = 'My_Column')orderby o.name

Requires that you have ability to query system tables.