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 ''?'''
About The Author
7 comments:
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.
Thanks! that's a nice tip
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'')'
Thanks Madhivannan!
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
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
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';
Post a Comment