June 05, 2010

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

6 Responses to "How to skip a Table or Database while using sp_MSforeachdb or sp_MSforeachtable"
  1. Anonymous said...
    June 14, 2010 at 1:44 AM

    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.

  2. Suprotim Agarwal said...
    June 14, 2010 at 3:31 AM

    Thanks! that's a nice tip

  3. Madhivanan said...
    July 7, 2010 at 12:28 AM

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

  4. Suprotim Agarwal said...
    July 8, 2010 at 8:05 PM

    Thanks Madhivannan!

  5. Michael "BickiBoy" Rybicki said...
    October 17, 2012 at 7:47 AM

    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

  6. Madhivanan said...
    October 17, 2012 at 10:52 PM

    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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions