latest timestamp multiple databases

  • Hi  dbas
    can you tell me  a quick way to check timestamp on multiple user databases in a SQL Server Instance.
    For Ex:

    select db_name(), max(doj) as latest_dATE  from tablename 
    need this looped across 70 databases and O/p should be like below:

    dbname  latest_date
    AAA        10-17-2017
    BBB         08-21-2016

    The timestamp comes from db table. ...from the app. its not a system time.

  • sqlguy80 - Tuesday, October 17, 2017 1:36 PM

    Hi  dbas
    can you tell me  a quick way to check timestamp on multiple user databases in a SQL Server Instance.
    For Ex:

    select db_name(), max(doj) as latest_dATE  from tablename 
    need this looped across 70 databases and O/p should be like below:

    dbname  latest_date
    AAA        10-17-2017
    BBB         08-21-2016

    The timestamp comes from db table. ...from the app. its not a system time.

    Is it the same table in each database?

  • yes, same table multiple databases

  • The shortest thing would be to write a query that reads from the table and inserts the results into a common table using the undocumented and unsupported sp_MSForEachDB stored procedure, which really just makes a nasty ol' cursor and While loop behind the scenes.  You'd have to add a filter to exclude the databases that won't have the table or, hopefully, the databases are named in a predictable/filterable fashion.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, October 17, 2017 8:06 PM

    The shortest thing would be to write a query that reads from the table and inserts the results into a common table using the undocumented and unsupported sp_MSForEachDB stored procedure, which really just makes a nasty ol' cursor and While loop behind the scenes.  You'd have to add a filter to exclude the databases that won't have the table or, hopefully, the databases are named in a predictable/filterable fashion.

    I keep hearing hints that sp_msforeachdb is unreliable and I also like getting a list of databases to do stuff from sys.databases. Just an alternative 2 cents worth!

    http://shaunjstuart.com/archive/2012/10/its-time-to-retire-sp_msforeachdb/
    http://johnmccormack.it/an-alternative-to-sp_msforeachdb/

  • I tend to write my own code for looping through databases.  That way I have more control of the process instead of relying on undocumented MS code.

  • If you still want to experiment with sp_msforeachDB, you could try it quick and dirty like this:


    Create table #Results ( DbName sysname not null, Latest_Date date );
    Declare @SQLStmt Nvarchar(max)
    Select @SQLStmt = '
    use [?];
    if ''?'' not in (''master'', ''model'', ''msdb'',''tempdb'')
    begin
    insert into #Results ( DbName, Latest_Date )
    SELECT DB_NAME() AS DbName
    , MAX(doj) AS latest_dATE
    FROM tablename;
    end
    '

    exec sp_MSforeachdb @SQLStmt ;

    Select
    DbName
        
    , Latest_Date
    from #Results
    order by DbName ;

    drop table #Results ;



    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • patrickmcginnis59 10839 - Friday, October 20, 2017 1:05 PM

    Jeff Moden - Tuesday, October 17, 2017 8:06 PM

    The shortest thing would be to write a query that reads from the table and inserts the results into a common table using the undocumented and unsupported sp_MSForEachDB stored procedure, which really just makes a nasty ol' cursor and While loop behind the scenes.  You'd have to add a filter to exclude the databases that won't have the table or, hopefully, the databases are named in a predictable/filterable fashion.

    I keep hearing hints that sp_msforeachdb is unreliable and I also like getting a list of databases to do stuff from sys.databases. Just an alternative 2 cents worth!

    http://shaunjstuart.com/archive/2012/10/its-time-to-retire-sp_msforeachdb/
    http://johnmccormack.it/an-alternative-to-sp_msforeachdb/

    Thanks for those links, Patrick.  I've heard some of the rumors but never dove into them because I don't ever actually use sp_MSForEachDB.  I'll withdraw even mentioning it as a possibility in the future and apologize for making a lazy suggestion on this thread.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • You can join the table with itself i.e on sensor id and add lift. timestamp < right. timestamp as join condition. Then you pick the rows, where right. id is null. Voila, you got the latest entry per sensor.You can join the table with itself i.e on sensor id and add lift. timestamp < right. timestamp as join condition. Then you pick the rows, where right. id is null. Voila, you got the latest entry per sensor.

  • I cant imagine 70 databases/iterations would be a burden on a cursor. 
    I have not tested this but something looking like >

    use master
    go

    create    table #results(dbName varchar(50), latestTimestamp datetime);

    declare @currentDB nvarchar(50);
    declare @sql nvarchar(250);
        
    declare myCur cursor fast_forward
        for
            Select    name
            from    sys.databases
            where    database_ID> 4

    open @cur
    fetch next from myCur into @currentDB;
    while (@@fetch_status =0)
    begin
        Set @sql = '
                    insert into #results
                    Select ' ''+ @currentDB+''' ,max(timestampColumn) /*hope i got my escape quotes correctly*/
                    from '+ @currentDB+'.dbo.tableName;' /* no group by needed with a literal */    
                    
        sp_executeSQL @sql;
        fetch next from myCur into @currentDB;
    end
    close myCur
    deallocate myCur                    


    Of course assuming permissions to the databases is not an issue.

    ----------------------------------------------------

  • No cursor required:

    DECLARE @SQLCmd NVARCHAR(MAX);
    CREATE TABLE #results(dbName varchar(50), latestTimestamp datetime);

    SELECT @SQLCmd = N'INSERT INTO #results' + NCHAR(13) + NCHAR(10) +
                     STUFF((SELECT N'union all' + NCHAR(13) + NCHAR(10) +
                                   N'select ''' + [db].[name] + ''', MAX(timestampColumn) from ' + [db].[name] + '.[dbo].[tableName]' + NCHAR(13) + NCHAR(10)
                            FROM [sys].[databases] AS [db]
                            WHERE [db].[database_id] > 4
                            ORDER BY [db].[name]
                            FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),1,11,'') + ';';
    PRINT @SQLCmd;

    EXEC [sys].[sp_executesql] @SQLCmd;

    SELECT * FROM #results;

  • In case there were other databases on the server that did not have the target table, I thought it would be nice to include a filter for only the databases where the table exists.

    I would probably use the STUFF and FOR XML concatenation technique if I was actually going to deploy this, but I left that out to make the example simpler.

    DECLARE @cmd VARCHAR(MAX), @union VARCHAR(20);
    SET @cmd = '';
    SET @union = '';

    SELECT    @cmd = @cmd + @union + 'SELECT dbname = ''' + d.name + ''', latest_date = MAX(doj) FROM ' + q.objName, @union = '
    UNION ALL '
    FROM sys.databases d
    CROSS APPLY ( SELECT objName = QUOTENAME(d.name) + '.dbo.tablename' ) q
    WHERE OBJECT_ID(q.objName,'U') IS NOT NULL AND d.database_id > 4;

    PRINT @cmd;
    EXEC (@cmd);

Viewing 12 posts - 1 through 11 (of 11 total)

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