Is it possible to rewrite this routine to be a single query, instead of a cursor. We take a daily recordcount of our tables, and insert them into a table called "HistoricalRecordCounts".
What I am trying to do is compare the record count in the current tables, to what was there yesterday, but I only want a list of tables where the current recordcount in the tables matches the previous days count that was stored in the HistoricalRecordCounts table.
So the cursor gets the list of tables we monitor based on DatabaseName, SchemaName and TableName. It then takes that information and queries the sys tables to get the current recordcount (rows). It then queries the HistoricalRecordCounts table for the recordcount from yesterday. If those two numbers match, I print the results.
I need to instead return the list of records in a recordset instead of printing them to the results window. The only way I can think of doing this, is to create a #temp table and the line where I print the results, simply do an insert into the #temp table, then after the cursor, select the records from this table. I feel there has to be a way to do this with a single query, but my skills are just not there. Hoping someone knows of way to accomplish this?
DECLARE cur3 CURSOR FOR
SELECT DISTINCT DatabaseName, SchemaName, TableName FROM [HistoricalRecordCounts]
PRINT 'The Following Tables Do Not Seem to Have Been Updated The Last Day.'
OPEN cur3
FETCH NEXT FROM cur3 INTO @databaseName, @schemaName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @recordCount = p.[Rows]
FROM
DataLake.sys.tables t
INNER JOIN DL.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN DL.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN DL.sys.allocation_units a ON p.partition_id = a.container_id
INNER JOIN DL.sys.schemas s on t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND i.OBJECT_ID > 255
AND i.index_id <= 1
AND s.name = @schemaName
AND t.NAME = @tableName
GROUP BY
s.Name, t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
SELECT @prevDayRecordCount = RecordCount FROM [HistoricalRecordCounts] WHERE DatabaseName = @databaseName AND SchemaName = @schemaName AND TableName = @tableName AND CAST(ReportDate AS DATE) = CAST(GETDATE() - 2 AS DATE)
IF @recordCount = @prevDayRecordCount
PRINT 'DatabaseName: ' + @databaseName + 'SchemaName: ' + @schemaName + char(9) + 'TableName: ' + @tableName + 'RecordCount: ' + CAST(@recordCount AS CHAR(10)) + 'Prev Day Count: ' + CAST(@prevDayRecordCount AS CHAR(10))
FETCH NEXT FROM cur3 INTO @databaseName, @schemaName, @tableName
END
CLOSE cur3
DEALLOCATE cur3
June 24, 2021 at 2:44 pm
Since I don't have your table, I couldn't check it, but I think that even if it doesn't work, it can point you to the direction that you need
PRINT 'The Following Tables Do Not Seem to Have Been Updated The Last Day.'
SELECT DB_NAME() as DBName, OBJECT_SCHEMA_NAME(object_id) as SchemaName, object_name(object_id) as ObjectName, rows
FROM sys.partitions p
WHERE index_id <= 1 and object_name(object_id) NOT LIKE 'dt%'
EXCEPT
SELECT DatabaseName, SchemaName, TableName, RecordCount
FROM HistoricalRecordCounts
WHERE CAST(ReportDate AS DATE) = CAST(GETDATE() - 2 AS DATE)
Adi
SELECT
'DataLake' AS DatabaseName, SchemaName, TableName,
p.Rows AS Row_Count, 'Row count same as yesterday''s row count' AS Message
FROM
DataLake.sys.tables t
INNER JOIN DL.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN DL.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN DL.sys.schemas s on t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND i.index_id <= 1
AND p.Rows = (
SELECT HRC.RecordCount AS prevDayRecordCount
FROM [HistoricalRecordCounts] HRC
WHERE HRC.DatabaseName = 'DataLake' AND HRC.SchemaName = s.name AND HRC.TableName = t.name AND
CAST(HRC.ReportDate AS DATE) = CAST(GETDATE() - 2 AS DATE)
)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply