January 26, 2015 at 2:54 am
Hi
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
I'll appreciate the help I can get.
Regards
January 26, 2015 at 4:18 am
Hi,
Try this. It's untested obviously but should do what you need. You'll need to add a filter in the first insert to exclude other databases if there are any. Also assumes there's only 1 row in each stdVersions table.
Cheers
Gaz
-- Generic db loop - use to execute queries against multiple databases and return in one set.
USE master;
GO
SET NOCOUNT ON;
-- Table to store list of databases
declare @dbs table (id int identity(1,1), dbname nvarchar(255));
insert into @dbs (dbname)
select name from sys.databases where state = 0 -- online
and database_id > 4 -- exclude system databases
and name not like 'ReportServer%'; -- exclude reporting services
-- setup variables for loop
declare @pos int = 1;
declare @maxid int = (select MAX(id) from @dbs);
declare @dbname nvarchar(255);
-- Command that will be run
declare @sqlstring nvarchar(4000);
-- add fields needed to this table
declare @results table (dbname nvarchar(255), DatabaseVersion NVARCHAR(100), DateChangedOn DATETIME);
-- loop
while @pos <= @maxid
begin
select @dbname = dbname from @dbs where id = @pos;
set @sqlstring =
N'USE [' + @dbname + ']
SELECT DB_NAME() AS DbName, v.DatabaseVersion, c.DateChangedOn
FROM stdVersions v
OUTER APPLY (SELECT MAX(ChangedOn) AS DateChangedOn FROM stdChangeLog) c
';
--print @sqlstring;
insert into @results (dbname, DatabaseVersion, DateChangedOn)
exec (@sqlstring);
set @pos = @pos + 1;
end;
-- select results
select dbname AS DatabaseName, DatabaseVersion, DateChangedOn
from @results
order by DateChangedOn DESC;
January 26, 2015 at 4:24 am
hi SSCrazy
thanks for the quick response, i'm gonna try it now.
thanks
January 26, 2015 at 4:32 am
hi again
you are such a star, I can kiss you right now.
thank you so much the query did exactly what I was looking for.
regards
January 26, 2015 at 5:16 am
No problem!
January 29, 2015 at 11:52 pm
hi
I wonder if you can still help me. I've modified the query but not I don't want to hard code in my above select.
the reason I hard coded it is because all database that starts with OK5 have tables name stdSystemsOption and stdChangeLogBatches whereas the DB's that starts with OK4 have stdVersion and stdChangeLog tables so I want it to work even if the naming conversion is different as long at they have the mention tables.
I know somewhere in the query I must use something like
IF EXISTS (SELECT * FROM sys.objects WHERE NAME = ''stdVersions'')
BEGIN
--DBCC FREESESSIONCACHE
--DBCC FREEPROCCACHE
--DBCC DROPCLEANBUFFERS
-- Generic db loop - use to execute queries against multiple databases and return in one set.
USE master;
GO
SET NOCOUNT ON;
-- Table to store list of databases
DECLARE @dbs TABLE
(
id INT IDENTITY(1,1)
,dbname NVARCHAR(255)
,DatabaseType INT
);
INSERT INTO @dbs
( dbname,DatabaseType
)
--get all onkey databases
SELECT name, 1 AS DatabaseType
FROM sys.databases
WHERE state = 0 -- online
AND database_id > 4 -- exclude system databases
AND --name not like 'ReportServer%' AND
name LIKE 'OK5%'
AND name NOT IN ( 'OK5_BREEDEVALLEI_SAMRAS_SIMMULATION',
'OK5_AGBAOU_TW','OK5_Saldanha_SAMRAS',
'OK5_TIGERBRANDS_DEMO' )
UNION ALL
SELECT name,0 AS DatabaseType
FROM sys.databases AS d
WHERE state = 0 -- online
AND database_id > 4 -- exclude system databases
AND name LIKE 'OK4%' OR name LIKE 'Saldanha%' OR name LIKE 'OK5_TIGERBRANDS_DEMO';
-- setup variables for loop
DECLARE @pos INT = 1;
DECLARE @maxid INT = ( SELECT MAX(id)
FROM @dbs
);
DECLARE @dbname NVARCHAR(255);
-- Command that will be run
DECLARE @sqlstring NVARCHAR(4000);
-- adding fields for the table
DECLARE @results TABLE
(
dbname NVARCHAR(255)
,DatabaseVersion NVARCHAR(100)
,DateChangedOn DATETIME
);
-- loop through
WHILE @pos <= @maxid
BEGIN
SELECT @dbname = dbname
FROM @dbs
WHERE id = @pos;
IF @dbname LIKE 'OK5%'
BEGIN
SET @sqlstring = N'USE [' + @dbname
+ ']
SELECT DB_NAME() AS DbName, v.DatabaseVersion, c.DateChangedOn
FROM stdSystemOptions v
OUTER APPLY (SELECT MAX(ChangedOn) AS DateChangedOn FROM stdChangeLogBatches) c
'
END
ELSE
BEGIN
SET @sqlstring = N'USE [' + @dbname
+ ']
SELECT distinct DB_NAME() AS DbName, (v.VerNo), c.DateChangedOn
FROM stdVersion v
OUTER APPLY (SELECT MAX(TimeStamp) AS DateChangedOn FROM stdChangeLog) c
'
END;
--print @sqlstring;
INSERT INTO @results
( dbname
,DatabaseVersion
,DateChangedOn
)
EXEC ( @sqlstring
);
SET @pos = @pos + 1;
END;
-- select results
SELECT dbname AS DatabaseName
,DatabaseVersion
,DateChangedOn
FROM @results
ORDER BY DateChangedOn DESC;
January 30, 2015 at 8:30 am
Looks about right to me, is it not working?
Be careful using DBCC FREESESSIONCACHE, DBCC FREEPROCCACHE, DBCC DROPCLEANBUFFERS, particularly if you're running this against production servers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply