Selecting from a table where the name is passed as a variable

I am trying to write a simple stored proc which takes three arguments 'database name one', 'database name two' and 'table name'. The sql will then perform a row count for the defined table in each database and store it.

Working on it piecemeal I have hit the first problem in that you can't do

select * from @tablename

I know you can use dynamic sql with the 开发者_JS百科exec command but this is not ideal as I can't return values.

The following example looks like it should work but doesn't.

declare @tablename as nvarchar(500)
declare @sqlstring as nvarchar(500)
declare @parmdefinition as nvarchar(500)
declare @numrows as bigint

set @tablename = N'dummy_customer'

set @parmdefinition = N'@tablenameIN nvarchar(500), @numrowsOUT as bigint OUTPUT'

select @sqlstring = 'select @numrowsOUT = count(*) from @tablenameIN'

select @sqlstring

exec sp_executesql @sqlstring, @parmdefinition, @tablenameIN = @tablename, @numrowsOUT = @numrows OUTPUT

select @numrows

The error message given is

Msg 1087, Level 16, State 1, Line 1
Must declare the table variable "@tablenameIN".

Currently using SQL Server 2008 SP2.

Edit: We're doing this because we are doing a migration and the customer wants a report which shows the row count for each table in the source and destination database. As there are many tables being able to use sp_MSForEachTable to call the stored proc seems ideal.

Edit:

The final solution for future reference is

declare @tablename as nvarchar(500)
declare @sqlstring as nvarchar(500)
declare @parmdefinition as nvarchar(500)
declare @numrows as bigint

set @tablename = N'dummy_customers'

set @parmdefinition = N'@tablename nvarchar(500), @numrowsOUT as bigint OUTPUT'

select @sqlstring = 'select @numrowsOUT = count(*) from ' + quotename(@tablename)

exec sp_executesql @sqlstring, @parmdefinition, @tablename = @tablename, @numrowsOUT = @numrows OUTPUT

select @numrows

You'd have to use dynamic sql, and concatenate the table name into the SQL string to then execute via sp_executsql:

select @sqlstring = 'select @numrowsOUT = count(*) from ' + QUOTENAME(@tablename)
EXECUTE sp_executesql ....