Wildcard DB name in VIEW of Procedure - Possible?

Question

Post reply

Wildcard DB name in VIEW of Procedure - Possible?

Dave-993364

SSC-Addicted

Points: 483
More actions
April 16, 2013 at 9:31 am

#273605

Hi there,
I've been trying to figure out whether it's possible to have a Wildcard DB name in a SELECT query.
eg. Database1, Database2, Database3, Database4, Database5, Database6 all have the same schema - but for different aspects of the business.
What I would like to figure out to do is to run something like this:
SELECT Col1, Col2, Col3, Col4, Col5 FROM AllDatabases (where AllDatabases is a concatenation of Database1-6)
At the moment, the best solution i've come up with is to loop round all the DB's and place the results into a temporary table, then select the results from the temporary table.
HOWEVER - the list of DB's *could* change. Not often, but it's possible. Ideally it needs to be as dynamic as possible.
Am I barking up the wrong tree here, or should I just stick with the looped concatenation of the DB's ?
Dave

Viewing 7 posts - 1 through 6 (of 6 total)

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

Neeraj Dwivedi SSCertifiable Points: 6768 More actions · Answer 1

Neeraj Dwivedi

SSCertifiable

Points: 6768

April 16, 2013 at 9:36 am

#1606878

Try using sp_msforeachdb.

Sean Lange SSC Guru Points: 286573 More actions · Answer 2

Many times using a cursor for this sort of thing seems like the only way to pull this off. sp_msforeachdb is just a cursor too. Neither of these approaches is bad but if you have a lot of databases it can be really slow. As another option you can use FOR XML to build a dynamic string.

Something like this. Once you are sure the dynamic string is correct just uncomment the sp_executesql. 🙂

declare @SQL nvarchar(max)

;with DatabaseList as

(

select name

from sys.databases

where database_id > 4

and name not in ('ReportServer', 'ReportServerTempDB')

)

select top 1 @SQL =

stuff((

select 'union all select Col1, col2, col3, Col4, col5 from ' + name + '.dbo.YourTable '

from DatabaseList

FOR XML PATH('')), 1, 10, '')

from DatabaseList

select @SQL

--exec sp_executesql @statement = @SQL

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Dave-993364 SSC-Addicted Points: 483 More actions · Answer 3

Thank you both for your prompt replies.

I will try these options and let you know which worked for me.

Thank you again

Dave

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 4

Considering the title of this thread, please keep in mind that none of them will work in a VIEW.

--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)

Dave-993364 SSC-Addicted Points: 483 More actions · Answer 5

Jeff - Thanks I'll bear this in mind.

I've now hit a minor hiccup.

I'm using the FOR XML route as this seems to be 99% ideal for wehat I need. but when I specify a special character, like '>', in the query, it doesn't reproduce the character, it simply just has '&gt' instead.

Same goes for '>' too (ie. &lt)

Any way round this?

Dave

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I'm not using the special characters you listed, but look closely at the code below at how I am using FOR XML PATH to concatenate the values. Incorporate what you see there into your code and see if that helps.

declare @TestData table(id int);

with e1(n) as (select row_number() over (order by (select null)) from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n))

insert into @TestData

select n from e1;

select * from @TestData;

-- Concatenate the data:

select

stuff((select ',' + cast(id as varchar)

from @TestData

order by id

for xml path(''),TYPE).value('.','varchar(max)'),1,1,'');