Solved

How to switch database dynamicaly in T-sql

Posted on 2007-11-20
8
3,506 Views
Last Modified: 2008-02-01
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
Comment
Question by:mordi
  • 3
  • 3
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20322587
what about using sp_MSForeachDB?
here a small sample:

exec sp_MSForEachDb ' select * from ?..sysfiles '
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 20322689
agree 100% with angelIII- he got me on these... take a look at http://www.databasejournal.com/features/mssql/article.php/3441031
0
 
LVL 3

Author Comment

by:mordi
ID: 20322700
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 31

Expert Comment

by:James Murrell
ID: 20322760
you could cal commands, what is the script you want t use
0
 
LVL 3

Author Comment

by:mordi
ID: 20322802
the script is as follows:
sp_attach_db @dbname = N'dbname' , @filename1 = N'db  file path' @filename2 = N'db  file path', @filename3 = ........
0
 
LVL 3

Author Comment

by:mordi
ID: 20334173
the script is as follows:
sp_attach_db @dbname = N'dbname' , @filename1 = N'db  file path' @filename2 = N'db  file path', @filename3 = ........
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 20334486
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
 
LVL 31

Expert Comment

by:James Murrell
ID: 20334501
Thanks angelIII - my email stopped and did not get notified
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Import New Records From Access Table To SQL Database Table 7 30
Database ERD 4 26
sql server query 18 36
SQL Quer 4 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question