How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable

sp_MSforeachdb and sp_MSforeachtable are undocumented stored procedures in the SQL Server Databases and they can be quite useful when you need to quickly retrieve some information on your tables or databases. Do not use them too often as they may be

[Check these posts to see how these undocumented stored procedure can be used

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

List of Undocumented Stored Procedures in SQL Server]

Now while using the stored procedures, if you need to skip an object from the query, then here’s how to do so:

Skip a Database while using sp_MSforeachdb

Here’s how to skip a database while using sp_MSforeachdb. The following command prints the size all databases in a SQL Server instance

EXEC sp_MSforeachdb
'USE ?;
EXEC sp_spaceused'

If you want to skip a database say ‘tempdb’, then here’s how to do so

EXEC sp_MSforeachdb
'USE ?;
IF DB_NAME() != ''tempdb''
EXEC sp_spaceused'



Skip a Table while using sp_MSforeachtable

If you run this command, 13 rows are returned

USE NORTHWIND
EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''

image 

To skip a table, let us say ‘Categories’, rewrite the following command as

USE NORTHWIND
EXEC sp_MSforeachtable
@command1='IF ''?'' != ''[dbo].[Categories]'' EXEC sp_spaceused ''?'''

This time only 12 rows are returned with the table Categories skipped.

image

To skip multiple tables, you can rewrite the same query with the NOT IN clause

USE NORTHWIND
EXEC sp_MSforeachtable
@command1='IF ''?'' NOT IN (''[dbo].[Categories]'',''[dbo].[Customers]'')
EXEC sp_spaceused ''?'''


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:

Anonymous said...

you could use the "@WhereAnd" parameter to skip tables (this is what it is designed for).
Like this:
exec sp_msForEachTable @command1=N'EXEC sp_spaceused ''?''', @whereand=N'and name not in (''Categories'',''Customers'')'

Behinf the scenes, sp_MSForEachTable creates a cursor which selects tables from sys.objects. The "@WhereAnd"parameter is appended to the cursor query, so you can reference any of the fields in the sys.objects view and restrict the tables returned.

Suprotim Agarwal said...

Thanks! that's a nice tip

Madhivanan said...

There is a small change in the code posted by anonymous

ie name should be ''?''

exec sp_msForEachTable @command1=N'EXEC sp_spaceused ''?''', @whereand=N'and ''?'' not in (''Categories'',''Customers'')'

Suprotim Agarwal said...

Thanks Madhivannan!

Michael "BickiBoy" Rybicki said...

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

Madhivanan said...

Michael "BickiBoy" Rybicki , try this code

EXEC sp_MSForEachDB '
if ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
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

LGuevara said...

To Skip a Database with Status of Recovering or Offline

exec sp_msforeachdb '
IF ( DATABASEPROPERTYEX(''?'', ''Status'') NOT IN (''RESTORING'',''OFFLINE'') )
BEGIN
use [?];
exec sp_helpdb ?
END';