Click here to Skip to main content
14,689,507 members
Articles » Database » Database » SQL Server
Posted 22 Aug 2008

Tagged as


45 bookmarked

SQL Server Hidden Stored Procedures

Rate me:
Please Sign up or sign in to vote.
4.37/5 (18 votes)
22 Aug 2008CPOL
Powerful SQL Server stored procedures useful for day to day administration


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


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'') 
  print ''?''
  exec [?].dbo.sp_changedbowner ''sa''

**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'')
   print ''?''

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


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
exec sp_msforeachtable
'insert into #stats
        name AS index_name,
    STATS_DATE(object_id, index_id) AS statistics_update_date
    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!


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


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

WilliamAPP7-May-09 6:53
MemberWilliamAPP7-May-09 6:53 
Michael Ceranski7-May-09 7:56
MemberMichael Ceranski7-May-09 7:56 
WilliamAPP8-May-09 8:33
MemberWilliamAPP8-May-09 8:33 
GeneralNice Job Pin
Syed Mehroz Alam26-Aug-08 1:19
MemberSyed Mehroz Alam26-Aug-08 1:19 
GeneralThanks for sharing Pin
Hemant.Kamalakar25-Aug-08 20:40
MemberHemant.Kamalakar25-Aug-08 20:40 
Questionenable Pin
kingtwisler25-Aug-08 15:25
Memberkingtwisler25-Aug-08 15:25 
AnswerRe: enable Pin
kingtwisler25-Aug-08 16:19
Memberkingtwisler25-Aug-08 16:19 
AnswerRe: enable Pin
Michael Ceranski26-Aug-08 4:04
MemberMichael Ceranski26-Aug-08 4:04 
GeneralChange article title Pin
Viresh Shah23-Aug-08 1:06
MemberViresh Shah23-Aug-08 1:06 
GeneralRe: Change article title Pin
canozurdo23-Aug-08 3:10
Membercanozurdo23-Aug-08 3:10 

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

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