How to switch database dynamicaly in T-sql

I have a cursor loop on sysdatabases and i need to query the sysfiles table for each database in system to build an attach script for all databases.
How can i switch the current database dynamicaly inside the cursor. Use keyword doesn't work
LVL 3
mordiAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please check out this script:
exec sp_msforeachdb '
declare @res varchar(8000) 
set nocount on
set @res = ''exec sp_attach_db N''''?'''' ''
select @res = @res +  '','''''' + rtrim(cast(filename as varchar(100))) + ''''''''
from ?..sysfiles 
order by fileid asc
select @res 
'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about using sp_MSForeachDB?
here a small sample:

exec sp_MSForEachDb ' select * from ?..sysfiles '
0
 
James MurrellProduct SpecialistCommented:
agree 100% with angelIII- he got me on these... take a look at http://www.databasejournal.com/features/mssql/article.php/3441031
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
mordiAuthor Commented:
Fine.
But there are are several files for each database and i need to build the attach command with all the file names in one line.
0
 
James MurrellProduct SpecialistCommented:
you could cal commands, what is the script you want t use
0
 
mordiAuthor Commented:
the script is as follows:
sp_attach_db @dbname = N'dbname' , @filename1 = N'db  file path' @filename2 = N'db  file path', @filename3 = ........
0
 
mordiAuthor Commented:
the script is as follows:
sp_attach_db @dbname = N'dbname' , @filename1 = N'db  file path' @filename2 = N'db  file path', @filename3 = ........
0
 
James MurrellProduct SpecialistCommented:
Thanks angelIII - my email stopped and did not get notified
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.