Click here to Skip to main content
5,788,961 members and growing! (20,255 online)
Email Password   helpLost your password?
Database » Database » SQL Server     Intermediate License: The Code Project Open License (CPOL)

SQL Server Hidden Stored Procedures

By Michael Ceranski

Powerful SQL Server stored procedures useful for day to day administration
SQL, SQL Server, DBA, Dev

Posted: 22 Aug 2008
Updated: 22 Aug 2008
Views: 5,856
Bookmarked: 29 times
Announcements
Loading...



Search    
Advanced Search
Sitemap
15 votes for this Article.
Popularity: 4.56 Rating: 3.88 out of 5
2 votes, 13.3%
1
2 votes, 13.3%
2
0 votes, 0.0%
3
3 votes, 20.0%
4
8 votes, 53.3%
5

Introduction

sp_msforeachdb and sp_msforeachtable are very powerful stored procedures. They allow you to loop through the databases and tables in your instance and run commands against them. I have used these extensively in my day to day work as a DBA. Both of the stored procedures use a question mark as a variable subsitution character. When using sp_msforeachdb, the "?" returns the databasename, and when using sp_msforeachtable the "?" returns the tablename.

Using the Stored Procedures

sp_msforeachdb

Example #1 - to do a check db on every database in your instance you could issue the following command:

sp_msforeachdb 'dbcc checkdb( ''?'' )'

Example #2 - to change the owner of each database in the instance to sa.

sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN
  print ''?''
  exec [?].dbo.sp_changedbowner ''sa''
END'

**Notice how I used an if statement to filter out the system databases

Example #3 - to do a check db on every table in the database you could issue the following command:

sp_msforeachdb 'dbcc checktable( ''?'' )'

Example #4 - to shrink every database on the instance. Be careful with this one. Not something you want to run on a production server during business hours.

sp_msforeachdb 'dbcc ShrinkDatabase( ?, 10 )'

Example #5 - to make a user db_owner on each user database in the instance. This is commonly done for apps like SharePoint that require db_owner in order to apply service packs.

 sp_msforeachdb 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
   print ''?''

   exec [?].dbo.sp_adduser ''<YOUR DOMAIN NAME HERE>\<YOUR USER ACCOUNT HERE>''
   exec [?].dbo.sp_addrolemember ''db_owner'',''<YOUR DOMAIN NAME HERE>\
        <YOUR USER ACCOUNT HERE''
END'

sp_msforeachtable

The counterpart to sp_msforeachdb. Once again, the procedure uses the "?" character to signify the name of the table that the command is currently being executed on.

Example #1 - to get a list of each index and when the statistics were last updated on each index.

CREATE table #stats(
   table_name nvarchar(255) null,
   index_name nvarchar(255) null,
      statistics_update_date datetime null
)
GO
          
exec sp_msforeachtable
'insert into #stats
 SELECT
    ''?'',
        name AS index_name,
    STATS_DATE(object_id, index_id) AS statistics_update_date
 FROM
    sys.indexes
 WHERE
    object_id = OBJECT_ID(''?'');'
      
select * from #stats where index_name is not null
      
drop table #stats

There are a million different uses for these stored procedures. The possibilities are endless. You can even nest a sp_msforeachtable inside of a sp_msforeachdb! Have fun and use them with caution!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Michael Ceranski


Michael is the co-founder and master consultant for Concepts2Code, a software consulting company based in Amherst, New York. He's been programming since the early 1990's. His vast programming experience includes VB, Delphi, C#, ASP, ASP.NET, Coldfusion and PHP. Michael also is a Microsoft Certified Application Developer and a Certified Technology Specialist for SQL Server.
Occupation: Web Developer
Location: United States United States

Other popular Database articles:

Article Top
Sign Up to vote for this article
You must Sign In to use this message board.
FAQ FAQ Noise ToleranceSearch Search Messages 
 Layout  Per page   
 Msgs 1 to 7 of 7 (Total in Forum: 7) (Refresh)FirstPrevNext
GeneralNice JobmemberSyed Mehroz Alam1:19 26 Aug '08  
GeneralThanks for sharingmemberHemant.Kamalakar20:40 25 Aug '08  
Questionenablememberkingtwisler15:25 25 Aug '08  
AnswerRe: enablememberkingtwisler16:19 25 Aug '08  
AnswerRe: enablememberMichael Ceranski4:04 26 Aug '08  
GeneralChange article titlememberThe Ruler1:06 23 Aug '08  
GeneralRe: Change article titlemembercanozurdo3:10 23 Aug '08  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 22 Aug 2008
Editor: Sean Ewington
Copyright 2008 by Michael Ceranski
Everything else Copyright © CodeProject, 1999-2009
Web19 | Advertise on the Code Project