Search
Monday, November 19, 2018 ..:: Home ::.. Register  Login
   Calendar  
     
  
   Search  
     
  
   Blogroll  
     
  
   Disclosure  
All blog entries are the opinions of the author and do not necessarily reflect the opinions of their employer. All the code presented is for explanation and demonstration purposes only. Any damages incurred to your site and/or data are not the responsibility of the author. Every effort is taken to ensure the code properly compiles, however sometimes there are some hiccups and you might be required to do your own debugging.
     
  
   TechTidBits (Blog)  

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  RssIcon

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.

Straight proc results

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.

Done more with the proc

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.

Done more but breaking

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

Tags:
Categories:
Location: Blogs Parent Separator TechTidBits

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

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Add Comment   Cancel 
     
  
Copyright 1999-2012 by PCHenry.com   Terms Of Use  Privacy Statement