Common uses for sp_MSforeachtable  

Posted by ReelTym

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:


  1. Display the size of all tables in a database


  2. USE NORTHWIND
    EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

  3. Display Number of Rows in all Tables in a database


  4. USE YOURDBNAME
    EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?'

  5. Rebuild all indexes of all tables in a database


  6. USE YOURDBNAME
    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"


    Note: DBCC DBREINDEX has been deprecated in SQL 2005. Microsoft says "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead."
  7. Disable all constraints of all tables in a database


  8. USE YOURDBNAME
    EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

  9. Disable all Triggers of all tables in a database


  10. USE YOURDBNAME
    EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

  11. Delete all data from all tables in your database


  12. -- disable referential integrity
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
    EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
    DELETE FROM ?
    ELSE
    TRUNCATE TABLE ?
    '
    GO
    -- enable referential integrity again
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    GO

  13. To RESEED all table to 0, use this script


  14. EXEC sp_MSForEachTable '
    IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0)
    '
    GO

    The two tips shown above have been taken from http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx and http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx

  15. Reclaim space from dropped variable-length columns in tables or indexed views


  16. USE YOURDBNAME
    EXEC sp_MSforeachtable 'DBCC CLEANTABLE(0,''?'') WITH NO_INFOMSGS; ';

  17. Update Statistics of all Tables in a database


  18. USE YOURDBNAME
    EXEC sp_MSforeachtable 'UPDATE statistics ? WITH ALL'

This entry was posted on Wednesday, September 15, 2010 and is filed under , . You can leave a response and follow any responses to this entry through the Subscribe to: Post Comments (Atom) .

0 comments