Need a quick'n'dirty way to do something to ALL the tables in your db?  Read on for a SQL Server undocumented proc you might be able to use.

Today I read about a cool undocumented proc for SQL Server!  sp_MSForEachTable is a great gem, but be careful since it's undocumented, it officially does not exist and therefore use with extreme caution.  However, it's great for quick'n'dirty little achoc things but again, be VERY careful with it when implemented with a production system. 

ATTENTION: All warranties, guarantees, liabilities, assurances this stuff will work are out the window, read this as "YOU"RE ON YOUR OWN!"

This proc does what it sounds like it does, it'll loop over all your tables executing the command you give it (up to three).  This is a great way to reindex your tables manually, or to check their sizes/row counts, etc.  As you can see from the following example, you place a question mark to be replaced by the table name, kind of like the "{0}" and "{1}" when your use the String.Format command.

For information sake, this is pretty cool.  But what if you wanted to use this but do more, like query, how do you do that?  Cursors to the rescue! :>  However, this comes with one caveat which I'm not completely sure why, so if you know, please let me know!  The caveat, you have to use a #TempTable instead of a @VairableTable.  Let me show you which one works first, then I'll show you the one that doesn't work and the errors I got.

As you can see from the table below, we create a temp table and insert into it, then we drop it.  Ya, ya, I know that last part (dropping) is optional, but when you're playing around with the table and executing the sql over and over again, you have to drop it like this otherwise you'll get errors cause the temp table already exists.

Here's the sql to make it work.

create table #AllTablesRowCounts(TableName varchar(max), NumberOfRows int)
EXEC sp_MSforeachtable 'insert into #AllTablesRowCounts SELECT ''?'' as TableName, Count(*) as NumberOfRows FROM ?'
select * from #AllTablesRowCounts order by NumberOfRows desc
drop table #AllTablesRowCounts

Now for the table variable that fails.

Here's the sql that fails.

declare @AllTablesRowCounts table(
  TableName varchar(max), NumberOfRows int)

EXEC sp_MSforeachtable 'insert into @AllTablesRowCounts SELECT ''?'' as TableName, Count(*) as NumberOfRows FROM ?'

select * from @AllTablesRowCounts order by NumberOfRows desc

Now, the way I THINK it's working, is the sp_MSForEachTable somehow requires there to be a table on the file system (which is one key difference between #TempTables and @TableVariables).  But if you know more, please leave a comment!

 

Resources:

Database Journal: MS SQL sp_MSforeachtable