Solved

How to switch database dynamicaly in T-sql

Posted on 2007-11-20
8
3,510 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

749 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