SQL Server: Applying Filter on sp_MSforeachDB

Introduction

While working on multiple databases on a single instance, sometimes, you need to execute a query for each database for which sp_MSforeachdb is the best choice.

Recently talking to my development team, I came to know that very few guys have an idea about a filter for sp_MSforeachDB.

For example, if I need to get database physical files information for each database on my instance, I will use the 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 a filter. This can be achieved by a 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'