• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3540
  • Last Modified:

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
0
mordi
Asked:
mordi
  • 3
  • 3
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Guy Hengel [angelIII / a3]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
 
James MurrellProduct SpecialistCommented:
Thanks angelIII - my email stopped and did not get notified
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now