January 14, 2010

How to exclude databases while using the sp_MSforeachdb stored procedure

Some time back, I had done an article on 6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

So now if you want to use this undocumented stored procedure to list the size of all databases in a SQL Server Instance, you would write the following query:

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

However if you wanted to exclude some databases from the query, then here’s how to do so:

EXEC    sp_msforeachdb 
"IF '?' NOT IN ('master','tempdb', 'model') BEGIN
USE ?; EXEC sp_spaceused
END"

This query will now list the size of all databases except the master, tempdb and model databases.

image


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

7 comments:

jbraes said...

Hi I found your info very useful but I am having some trouble using it with databases using UUID as names.

example one database is named 00075926-0f40-41b1-8695-d7ca3fc7f474

now when I run a command like
EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'

I will get an error : Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '00075926'.

clearly the name of the database . Any Ideas on what I should do ?

Madhivanan said...

Try this



EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

Anonymous said...

Wow thanks madhivana! had the same problem, never knew you could use the USE statement like that

- B. Meyer

Madhivanan said...

Thanks anonymous for the feedback

jbraes said...

madhivana!

Super response you made my day :)

Madhivanan said...

Thanks jbraes for the feedback

SAGA said...

I want to exclude some staging databses from the list and run the query....

i mean

not like '%stg%

can we use it in this way so I can exclude the list of staging databases....