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;