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 ''?'''

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.

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 ''?'''

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: