SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 List attached databases?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

umayxa3
Starting Member

4 Posts

Posted - 03/12/2007 :  16:35:43  Show Profile  Reply with Quote
Is there a stored procedure to list attached databases?
You can do this:
use Master
exec sp_helpfile
go

Which shows you the config files for the current DB.
What if you want to do a multiple sp_detach_db? If you could get a list of the attached DB's you could recursilvely or conditioinaly detach.

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 03/12/2007 :  16:48:06  Show Profile  Reply with Quote
You could use sp_helpdb, or better would be to query sys.databases where you could check the state column to see the current state of the database. Note that a detached database doesn't show up anywhere, so really you list databases, because all databases are attached.
Of course you must not detach the system databases.
I'm not sure why you want to write code to detach all the databases anyway?!
Go to Top of Page

umayxa3
Starting Member

4 Posts

Posted - 03/12/2007 :  16:53:39  Show Profile  Reply with Quote
I saw a previous post that someone had 900 attached DB's and I thought a conditional script could be written to sp_detach_db, excluding the ones you want to keep.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 03/12/2007 :  17:26:11  Show Profile  Reply with Quote
I guess, but if you have so many databases then I assume they are all being used and then you wouldn't want to just detach them all. I'm really saying, a database is a major level thing, I can't see a lot of times you'd want to automate detaching them, it should surely be a specific one by one choice.
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 03/13/2007 :  11:19:51  Show Profile  Reply with Quote
In order to determine what you want to keep, and what should go, you are probably going to have to create your own table of databases. That will allow you to add any attributes you need to your own databases. How you determine what to detach and keep is probably going to be a political nightmare in itself.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000