Dynamic SQL isn’t updating table

Posted on

Question :

So I need to basically get the count of every table in a database daily and insert those counts into a separate database for analysis and reporting purposes. The table the counts need to be inserted into has a column named after each table in the first database. For some reason, the columns aren’t being updated with the counts for their respective tables. If I change the @TableName variable to the a single table name, the count for the last table is correctly updated, so it appears to be an issue with that specific variable. My SQL is below. I have another table with all the table names and an auto increment column that the table names are selected from. If I print the @TableName and @row_count variables instead of trying to update the table, they appear to print the correct table and row counts. Ideas?

DECLARE @Count int;
DECLARE @TableCount int;
DECLARE @TableName varchar(255);
DECLARE @row_count int;
DECLARE @SQLString nvarchar(MAX);
DECLARE @ParmDefinition nvarchar(MAX);

SET @TableCount = 1;

SELECT @Count = COUNT(*) FROM db1.INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'db1';

INSERT INTO db2.[dbo].[row_counts] ([type])
VALUES('total');

WHILE @TableCount <= @Count
BEGIN

SELECT @TableName = [TableName] FROM db2.[dbo].[ListofTables] WHERE TableNumber = @TableCount;

SELECT @SQLString = N'SELECT @CountOUT = COUNT(*) FROM db1.[dbo].[' + @TableName + ']';
SET @ParmDefinition = N'@CountOut int OUTPUT';

EXEC sp_executesql @SQLString, @ParmDefinition, @CountOUT=@row_count OUTPUT;

UPDATE [db2].[dbo].[row_counts]

SET @TableName = @row_count

WHERE creation_time = (SELECT MAX(creation_time) FROM [db2].[dbo].[row_counts]);

SET @TableCount = @TableCount + 1;
END;

Answer :

I didn’t attempt to follow your logic, but the following caught my eye:

   ... UPDATE [db2].[dbo].[row_counts] SET @TableName = @row_count...

This is only updating @TableName variable.
To update the table, you need to SET one of the table columns, not a variable.

HTH

Counting the rows in all the tables is expensive and complicated. How about

select schema_name(o.schema_id) schemaName, object_name(o.object_id) tableName , p.rows
from 
sys.objects o
join sys.partitions p
  on o.object_id = p.object_id
where index_id in (0,1)
  and o.is_ms_shipped = 0

Leave a Reply

Your email address will not be published.