Dear All,
Sometimes as DBA’s we need to perform certain action on all the tables in a database. Say, running DBCC Checktable, or disabling all constraints before a bulk load or simply finding the size of each table. There are many ways to do it but one quick way could be using the undocumented stored procedure sp_msforeachtable which can be found in the MASTER database.
For example; running sp_spaceused on all the tables in your database:
USE AdventureWorks EXECUTE sp_MSforeachtable 'sp_spaceused [?];'
You can see that sp_spaceused stored procedure (which does not require any explanation) runs on all the tables.
This undocumented stored procedure can be used in many interesting ways and in my future posts, I shall show a few things.
Regards
Rahul Sharma
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
Hi Rahul,
Gud one, In April even i posted on the same topic but with a diff use. https://www.sqlservergeeks.com/blogs/sarab/sql-server-bi/11/use-of-sp_msforeachtable
Sarabpreet Singh
nice
Powerful !
Hmmm…
Thats one undcoumented SP that I will miss if decides to Microsoft deprecates…..That will be just foolish.
Just imagine doing this(Having NoCheck on all constraint for the tables in a DB) with simple TSQL..
EXEC
sp_msforeachtable @command1 = “ALTER TABLE ? NOCHECK CONSTRAINT all”
;
sp_msforeachdb is for databases similar to what sp_msforeachtable for tables.
Nice