query/script/stored procedure which will return a single table with three columns

Question

Post reply

query/script/stored procedure which will return a single table with three columns

Nomvula

Hall of Fame

Points: 3778
More actions
January 13, 2015 at 1:08 am

#294199

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Try this first:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

or wait for someone who will do it for you...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

what's the query you would use to get tat same data from a single database? if you have that, you can automate it across all databases.

you just need to wrap that query in a cursor, and insert the results in a temp table created outside of that cursor.

something like this is my best guess, without the details , and assuming both the stdVersions table and stdChangeLog contain only a single row.

IF OBJECT_ID('tempdb.[dbo].[#Results]') IS NOT NULL

DROP TABLE [dbo].[#Results]

GO

CREATE TABLE [dbo].[#Results] (

[DatabaseName] VARCHAR(128) NULL,

[DatabaseVersion] VARCHAR(128) NULL,

[DateChangedOn] DATETIME NULL)

EXEC sp_msForEachDb '

IF EXISTS (SELECT * FROM [?].sys.objects WHERE NAME = ''stdVersions'')

BEGIN

INSERT INTO #Results

SELECT

''?'' AS [DatabaseName],

v.[DatabaseVersion],

l.[DateChangedOn]

FROM [?].dbo.stdVersions v

CROSS JOIN [?].dbo.stdChangeLog l

END'

SELECT * FROM #Results ORDER BY DateChangedOn

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nomvula Hall of Fame Points: 3778 More actions · Answer 3

hi Lowell

thanks for the quick response.

the query I use to get the list of all the databases is

SELECT s.name FROM sys.sysdatabases AS s

I want to automate this query and be able to select the databaseVersion and changeLog inside the database tables.

and yes the stdVersion table has a single value which stored the version but the stdChangeLog i'll have to select the Max(ChangeOnDate)

Lowell SSC Guru Points: 323518 More actions · Answer 4

great glad i could point you in the right direction then.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!