Need a quick'n'dirty way to do something to ALL the tables in your db?
Apr
20
Written by:
Monday, April 20, 2009 8:30 PM
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
3 comment(s) so far...
Re: Need a quick'n'dirty way to do something to ALL the tables in your db?
The problem most likely lies in the fact that table variables (like any other variable) are scoped. When your calling an inner stored proc, your technically not within the same scope and thus it "wouldn't know" what variable 'AllTablesRowCounts' is.
By Shane on
Monday, April 20, 2009 10:37 PM
|
Re: Need a quick'n'dirty way to do something to ALL the tables in your db?
I think that's it exactly! Thanks for the confirmation.
By phenry on
Monday, April 20, 2009 10:37 PM
|
Re: Need a quick'n'dirty way to do something to ALL the tables in your db?
VERY COOL! :> I made it onto Jason Haley's Interesting Finds again (http://jasonhaley.com/blog/post/2009/04/21/Interesting-Finds-April-21-2009.aspx)!
If you haven't checked out his blog, I STRONGLY encourage you to. He does a lot of the "heavy lifting" for you when it comes to searching the web for interesting finds. If you read just ONE blog, this should be the one! He's already done a boat load of RSS filtering for you!
Thanks Jason!
By phenry on
Tuesday, April 21, 2009 11:03 AM
|