SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb

Microsoft provides two undocumented Stored Procedures that allow you to process through all tables in a database, or all databases in a SQL Server instance. The first Stored Procedure (SP), “sp_MSforeachtable,” allows you to easily process some code against every table in a single database. The other SP, “sp_MSforeachdb,” will execute a T-SQL statement against every database associated with the current SQL Server instance.

USAGE

USE <YOUR_DATABASE_NAME>
GO
EXEC sp_MSForEachTable ‘SELECT ”?”, COUNT(*) FROM ?’

Another way to iterate through database tables and want to know row count of all tables, traditionally you have to write a code like this:

set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
where table_type = ‘base table’
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
set @cmd = ‘select ”’ + @table + ”’, count(*) from ‘ + @table
insert into #rowcount exec (@cmd)
fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
select * from #rowcount
order by tablename
drop table #rowcount

But using  “sp_MSforeachtable”, it is now easier:

create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
‘insert into #rowcount select ”?”, count(*) from ?’
select top 5 * from #rowcount
order by tablename
drop table #rowcount

NOTE:

Some level of testing and care should be taken when using undocumented code from Microsoft. Since these SP’s are not documented, it means that Microsoft might change this code with any new release or patch without notifying customers. Because of this, you need to thoroughly test any code you write that uses these undocumented SPs against all new releases of SQL Server. This testing should verify that your code still functions as it did in old releases.

As you can see, these undocumented Stored Procedures are much easier to use.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s