October 17, 2017 at 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.
October 17, 2017 at 1:57 pm
sqlguy80 - Tuesday, October 17, 2017 1:36 PMHi 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-2016The timestamp comes from db table. ...from the app. its not a system time.
Is it the same table in each database?
October 17, 2017 at 6:52 pm
yes, same table multiple databases
October 17, 2017 at 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 20, 2017 at 1:05 pm
Jeff Moden - Tuesday, October 17, 2017 8:06 PMThe 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/
October 20, 2017 at 1:27 pm
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.
October 31, 2017 at 3:13 am
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
October 31, 2017 at 9:44 am
patrickmcginnis59 10839 - Friday, October 20, 2017 1:05 PMJeff Moden - Tuesday, October 17, 2017 8:06 PMThe 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
Change is inevitable... Change for the better is not.
November 2, 2017 at 7:31 am
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.
November 3, 2017 at 1:44 am
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.
----------------------------------------------------
November 3, 2017 at 9:00 am
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;
November 3, 2017 at 12:03 pm
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