Create procedure s_ProcTable
@TableName varchar(128)
as
declare @sql varchar(4000)
select @sql = 'select rows = count(*) from [' + @TableName + ']'
exec (@sql)
go
Now executing this will give the result.
Note the [] around the name in case it contains invalid characters.
You may also have to deal with the owner.
Using EXECUTE 'tsql_string' with a variableThe following example shows how EXECUTE handles dynamically built strings that contain variables. This example creates the tables_cursor cursor to hold a list of all user-defined tables in the AdventureWorks2008R2 database, and then uses that list to rebuild all indexes on the tables.
USE AdventureWorks2008R2;
GO
DECLARE tables_cursor CURSOR
FOR
SELECT s.name, t.name
FROM sys.objects AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
EXECUTE ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename;
END;
PRINT 'The indexes on all tables have been rebuilt.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO
