Stored Proc. Cycling through database

Question

Stored Proc. Cycling through database

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I see the cursor cycling through the database names, but the query doesn't do anything with that so far as I can tell. You aren't actually switching databases just because a variable is set to the name of a database. Or am I missing something?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kbenevit SSC Journeyman Points: 78 More actions · Answer 2

I think you're right. How can i cycle through the database and output information from each database?

GSquared SSC Guru Points: 260824 More actions · Answer 3

The easiest way is make the query into dynamic SQL, plug in the database name as part of a three-part name for the tables, and execute it that way.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kbenevit SSC Journeyman Points: 78 More actions · Answer 4

kbenevit

SSC Journeyman

Points: 78

June 10, 2011 at 8:14 am

#1337024

Thanks

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

simplier: EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

GSquared SSC Guru Points: 260824 More actions · Answer 6

Crispin Proctor (6/10/2011)
simplier: EXECUTE sp_msforeachdb 'USE ? PRINT DB_NAME()'

Considering the query and return results he's trying for, I discounted the system proc. The input command would be complex to build, just because of nested single-quotes issues.

Also, all that proc does is cursor through sys.databases, with some bells and whistles added. It's no more efficient than building your own cursor, and less flexible in its Select clause for the cursor.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon