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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

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....