How to pass table name as parameter in select statement in SQL Sever

Question

Table:

Col 
------
Table1 
table2 
table3

Query:

select count(*) 
from @tablename

I wanted to pass table1, table2, table3 as parameters for @tablename in the select query and get the count for each table

Desired output:

 2 (table 1 count)  3 (table 2 count)  4 (table 3 count)

If you want to parametrize a table name, you need to use dynamic SQL. — Gordon Linoff, Oct 11 '17 at 12:04
You want your output in all one row, or is that the result of someone else's edit? — SqlZim, Oct 11 '17 at 12:26
For row counts this may be okay but if there's any other "common" querying against these tables, this may be a sign of a broken data model. If you're doing "common" querying, it's likely that they should in fact be a single table, and also likely that something that should be modelled as *data* has instead ended up as (less queryable) *metadata*, such as being embedded in the table *name*. — Damien_The_Unbeliever, Oct 11 '17 at 12:37

score 2 · Accepted Answer · answered Oct 11 '17 at 12:13

you can use dynamic sql and a cursor to run through them:

Create temp table for testing:

DECLARE @tablenametable TABLE(tablename VARCHAR(100));
INSERT INTO @tablenametable
VALUES('table1'), ('table2'), ('table3');

Use a cursor to run through all tablenames in the table

DECLARE @tablename VARCHAR(100);
DECLARE dbcursor CURSOR
FOR
    SELECT tablename
    FROM @tablenametable;
OPEN dbcursor;
FETCH NEXT FROM dbcursor INTO @tablename;
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @sql VARCHAR(MAX);
        SET @sql = 'select count(*) from '+@tablename;
        PRINT(@sql);
        FETCH NEXT FROM dbcursor INTO @tablename;
    END;
CLOSE dbcursor;
DEALLOCATE dbcursor;

Give the following results:

select count(*) from table1
select count(*) from table2
select count(*) from table3

Just change PRINT(@SQL) to EXEC(@SQL) when your happy with it

Ullas · Answer 2 · 2017-10-11T12:23:38.260

0

You can use dynamic sql query.

Query

declare @sql as varchar(max);

select @sql = stuff((
    select ' union all '
    + 'select cast(count(*) as varchar(100)) 
    + ' + char(39) + '(' + [Col] +' Count)' + char(39) 
    + ' as [table_counts] '
    + ' from ' + [col]
    from [your_table_name]
    for xml path('')
  )
  , 1, 11, ''
);

exec(@sql);

`Find a demo here`

edited Oct 11 '17 at 12:23

answered Oct 11 '17 at 12:15

Ullas

11,450
4
33
50

score 0 · Answer 3 · answered Oct 11 '17 at 12:34

As mentioned here, you have to use dynamic SQL.

First approach is where you specify table name yourself:

declare @tablename varchar(30), @SQL varchar(30)
set @tablename = 'Table1' --here you specify the name
set @SQL = concat('SELECT COUNT(*) FROM ', @tablename) --here you build the query

EXEC(@SQL)

Second approach lets you use table with names of tables:

declare @SQL varchar(8000)
set @SQL = ''
declare @TableNames table(name varchar(30))
insert into @TableNames values ('Table1'), ('Table2'), ('Table3')
--here you build the query
select @SQL = @SQL + ' SELECT ''' + name + ''' AS [TableName], COUNT(*) AS [Count] FROM ' + name + ' UNION ALL' from @TableNames
-- get rid of last "UNION ALL"
set @SQL = LEFT(@SQL, LEN(@SQL) - 10)
--execute the query
EXEC(@SQL)

The result of it will be:

TableName    Count
Table1       3
Table2       6
Table3       4

shA.t · Answer 4 · 2017-10-11T13:34:56.147

You can use sys.dm_db_partition_stats like this ^[1]:

select
    t.col tableName, sum(s.row_count) tableCount
from 
    yourTable t
join
    sys.dm_db_partition_stats s
  on
    (object_name(s.object_id) = t.col )
 and 
    (s.index_id < 2)
group by 
    t.col;

[1]. Related answer

One line output version will be:

select
    sum(s.row_count), ' ('+t.col +' count) '
from 
    yourTable t
join
    sys.dm_db_partition_stats s
  on
    (object_name(s.object_id) = t.col )
 and 
    (s.index_id < 2)
group by 
    t.col
for xml path('');

output:

2 (Table1 count) 3 (table2 count) 4 (table3 count)

How to pass table name as parameter in select statement in SQL Sever

4 Answers4

Find a demo here

`Find a demo here`