Need a simple way to iterate through all database names.....TSQL
-
What's the best way to iterate through all the names of an existing db, so I can take the dbname and put it in a variable to do further action on it.
I know there are different methods, and will accept a cursor, but thought 2005 will have newer more efficient way. Please provide samplet code. TIA!
-
sp_msforeachdb will allow you to execute the same block of code using each database.
What you want to do is substitute in a question mark for the name of the database....
sp_msforeachdb 'select ''?'' as db_nm, * from ?..sysobjects where xtype = ''P'' '
That would, for instance, list all stored procs in all databases and the name of the db next to them. 😉
-
Thx for the quick reply.
While I appreciate this method, I'd prefer not to use sp_msforeachdb, b/c I need to use the dbname in a sp script.
-
Can we see the script to see if we can change it there?
-
Thx, all - I used a forward-only cursor afterall - doesn't take much resources.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply