Thursday, August 9, 2012

SQL Server: Applying Filter on sp_MSforeachDB


Working on multiple databases on a single instance, sometime you need to execute a query for each database and for that sp_MSforeachdb is the best choice.
Recently talking to my development team I came to know that a very few guys have idea about filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use following simple query
EXEC sp_MSforeachdb '
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'


BUT what if, I need to omit MSDB, TempDB and Model databases for this query. Now I have to apply filter. This can be achieved by simple IF statement.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

You can even use ? sign in WHERE clause.
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
       WHERE name  LIKE ''?%'' -- Only Files starting with DB name
END'

Output can be saved in tables (user, temporary) or table variables
DECLARE   @DatabasesSize TABLE
    (
      name VARCHAR(50),
      physical_name VARCHAR(500),
      state BIT,
      size INT
    )

INSERT  INTO @DatabasesSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,state,size
       FROM ?.sys.database_files
END'

4 comments:

  1. Thank you. Very informative. However, sp_MSforeachDB is undocumented and known to miss DBs.

    Regards,
    Syed Muhammad Yasir

    ReplyDelete
  2. I love that command but for the life of me I can not figure out why it fails if you try to use it to set your RECOVERY model for all you user databases. Here's the script - any insight would solve one of my most puzzling mysteries...


    EXEC sp_MSForEachDB 'if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
    BEGIN
    use [?]
    print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT''
    ALTER DATABASE [?] SET RECOVERY SIMPLE WITH NO_WAIT
    END
    print ''?'' + '': SET RECOVERY SIMPLE WITH NO_WAIT - DONE'';
    '
    GO



    RESULTS:
    master: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
    Msg 5058, Level 16, State 1, Line 5
    Option 'RECOVERY' cannot be set in database 'tempdb'.
    model: SET RECOVERY SIMPLE WITH NO_WAIT - DONE
    msdb: SET RECOVERY SIMPLE WITH NO_WAIT - DONE

    ReplyDelete
  3. Thank you very helpful

    ReplyDelete
  4. A good piece of information.
    Thank you.

    ReplyDelete

All suggestions are welcome