Find all user databases - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find all user databases

Author

Topic

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-04 : 07:52:38

Hi,

I need to find all the only user database and datafile information and log file information, space used, remaining and total space.

Any one can help me to build a query please

Yogesh V. Desai. | SQLDBA|

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 07:54:41

sp_spaceused

EXEC sp_msforeachdb 'use ? ; EXEC sp_spaceused'

E 12°55'05.25"
N 56°04'39.16"

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-04 : 07:58:25

quote:
Originally posted by YogeshDesai

Hi,

I need to find all the only user database and datafile information and log file information, space used, remaining and total space.

Any one can help me to build a query please

Yogesh V. Desai. | SQLDBA|

You could use profiler to snipe the taskpad view query and modify it.

------------------------
Future guru in the making.

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-04 : 09:01:28

Thank you very much for valuable help.
It's really useful and I have tried it already, but I need location of mdf and log files too.For each database.

Yogesh V. Desai. | SQLDBA|

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-04 : 09:08:34

quote:
Originally posted by YogeshDesai

Thank you very much for valuable help.
It's really useful and I have tried it already, but I need location of mdf and log files too.For each database.

Yogesh V. Desai. | SQLDBA|

You should be able to use sp_MSforeachDB procedure and query the information from the system tables or information schema views.

------------------------
Future guru in the making.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 15:36:34

sp_helpdb 'dbname' --put that inside your loop...

--------------------
keeping it simple...

YogeshDesai
Posting Yak Master

136 Posts

Posted - 2007-09-07 : 10:49:07

wil you help me to build a loop, I am trying to build it.

Yogesh V. Desai. | SQLDBA|

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-08 : 00:00:47

Peso gave you the code above.