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

at

7 comments:

  1. jbraesFebruary 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 ?

    ReplyDelete
  2. MadhivananFebruary 27, 2011 at 10:17 PM

    Try this



    EXEC sp_MSforeachdb 'USE [?]; EXEC sp_spaceused'

    ReplyDelete
  3. AnonymousMarch 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

    ReplyDelete
  4. MadhivananMarch 2, 2011 at 1:37 AM

    Thanks anonymous for the feedback

    ReplyDelete
  5. jbraesMarch 14, 2011 at 5:31 AM

    madhivana!

    Super response you made my day :)

    ReplyDelete
  6. MadhivananMarch 14, 2011 at 6:39 AM

    Thanks jbraes for the feedback

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

    ReplyDelete