Looping through multiple dbs in a view

Question

Looping through multiple dbs in a view

Guras

SSCertifiable

Points: 6518
More actions
November 21, 2013 at 7:17 am

#301566

CREATE VIEW [dbo].[VW_PERSON_DETAIL]
AS
SELECT p.PTNO, p.[NAME], p.SSN FROM table1 p
INNER JOIN table2 c ON p.idcode = c.idcode
WHERE c.RV = 1
I need to loop thorugh all the dbs in the server since table1 ( same shema) exists in all the dbs.
How can I rewrite this view to loop through the multiple dbs?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Views can only be single SELECT statements. If you need looping or anything more than SELECT ... then consider a stored procedure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

with a set list of databases, you could create a view that does multi database calls like this:

CREATE VIEW [dbo].[VW_PERSON_DETAIL]

AS

SELECT

'Database1' AS DB,

p.PTNO,

p.[NAME],

p.SSN

FROM Database1.dbo.table1 p

INNER JOIN Database1.dbo.table2 c

ON p.idcode = c.idcode

WHERE c.RV = 1

UNION ALL

SELECT

'Database2' AS DB,

p.PTNO,

p.[NAME],

p.SSN

FROM Database2.dbo.table1 p

INNER JOIN Database2.dbo.table2 c

ON p.idcode = c.idcode

WHERE c.RV = 1

UNION ALL

SELECT

'Database3' AS DB,

p.PTNO,

p.[NAME],

p.SSN

FROM Database3.dbo.table1 p

INNER JOIN Database3.dbo.table2 c

ON p.idcode = c.idcode

WHERE c.RV = 1

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!