>USE AP > >DECLARE @DynamicSQL VARCHAR(300) >DECLARE @TableNameVar VARCHAR(20) >SET @TableNameVar = 'SELECT MIN(name) from sys.tables' > >SET @DynamicSQL = > 'SELECT COUNT(*) AS Countof' + (@TableNameVar) + ' FROM ' + @TableNameVar > >EXEC (@DynamicSQL) >To get record counts in all tables in the Database:
DECLARE @DynamicSQL NVARCHAR(max) SELECT @DynamicSQL = coalesce(@DynamicSQL + CHAR(13) + ' UNION ALL ' + CHAR(13),'') + 'SELECT ' + quotename(Table_Name,'''') + ' as TableName, COUNT(*) AS [RecordsCount] FROM ' + quotename(Table_Name) from information_schema.Tables order by Table_Name --print (@DynamicSQL) -- we may want to use PRINT to debug the SQL EXEC (@DynamicSQL)