Click here to Skip to main content
Click here to Skip to main content

SQL Server Hidden Stored Procedures

, 22 Aug 2008
Rate this:
Please Sign up or sign in to vote.
Powerful SQL Server stored procedures useful for day to day administration

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)

Share

About the Author

Michael Ceranski
Software Developer (Senior) Concepts2Code
United States United States
Michael is the co-founder and master consultant for Concepts2Code, a software consulting company based in Buffalo, New York. He's been programming since the early 1990's. His vast programming experience includes VB, Delphi, C#, ASP, ASP.NET, Ruby on Rails, Coldfusion and PHP. Michael also is a Microsoft Certified Application Developer and a Certified Technology Specialist for SQL Server.
 
Visit his blog.

Comments and Discussions

 
GeneralSHRINKDATABASE PinmemberMember 45889187-May-09 5:53 
GeneralRe: SHRINKDATABASE PinmemberMichael Ceranski7-May-09 6:56 
GeneralRe: SHRINKDATABASE PinmemberWilliamAPP8-May-09 7:33 
GeneralNice Job PinmemberSyed Mehroz Alam26-Aug-08 0:19 
GeneralThanks for sharing PinmemberHemant.Kamalakar25-Aug-08 19:40 
Questionenable Pinmemberkingtwisler25-Aug-08 14:25 
AnswerRe: enable Pinmemberkingtwisler25-Aug-08 15:19 
AnswerRe: enable PinmemberMichael Ceranski26-Aug-08 3:04 
GeneralChange article title PinmemberThe Ruler23-Aug-08 0:06 
GeneralRe: Change article title Pinmembercanozurdo23-Aug-08 2:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 22 Aug 2008
Article Copyright 2008 by Michael Ceranski
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid