Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
umayxa3
Starting Member
4 Posts |
Posted - 2007-03-12 : 16:35:43
|
Is there a stored procedure to list attached databases?You can do this:use Masterexec sp_helpfilegoWhich 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
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-12 : 16:48:06
|
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?! |
|
|
umayxa3
Starting Member
4 Posts |
Posted - 2007-03-12 : 16:53:39
|
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. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-12 : 17:26:11
|
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. |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-03-13 : 11:19:51
|
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. |
|
|
|
|
|