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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

7 Responses to "How to exclude databases while using the sp_MSforeachdb stored procedure"
  1. jbraes said...
    February 25, 2011 at 9:01 AM

    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 ?

  2. Madhivanan said...
    February 27, 2011 at 10:17 PM

    Try this



    EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

  3. Anonymous said...
    March 1, 2011 at 8:55 PM

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

    - B. Meyer

  4. Madhivanan said...
    March 2, 2011 at 1:37 AM

    Thanks anonymous for the feedback

  5. jbraes said...
    March 14, 2011 at 5:31 AM

    madhivana!

    Super response you made my day :)

  6. Madhivanan said...
    March 14, 2011 at 6:39 AM

    Thanks jbraes for the feedback

  7. SAGA said...
    October 1, 2012 at 8:31 AM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions