Single Query Instead of a Cursor

  • 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
  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply