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;
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.
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