sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable:  Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.

sp_MSforeachtable is one of the undocumented stored procedure in Microsoft SQL-Server 2005. It has an amazing facility of auto iteration. It means that it will run T-SQL command for each and every single table of your database. As other system SP, you can find this SP in MASTER DB. If you are a member of sysadmin then you can use this SP very beautifully for your task.

Let’s look at its example:

Note: Create one dummy database and use this example in it only as it will take effect throughout the database and will affect all tables resides in that database.

USE DummyDB

GO

EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

GO

 

Above query will disable all the constraints of all tables in DummyDB. “?”  In above query will be replaced automatically by table name internally and will iterate the loop for all table automatically.

How easy is this??????

 

Reference: Ritesh Shah

Advertisements

3 Responses to “sp_MSforeachtable: Undocumented but useful stored procedure for auto iteration in SQL-Server 2005.”

  1. Tim Early Says:

    If you have a free minute….

    Hoping you can help me. The following script allows me to loop through each database to print something.

    declare @dbname varchar(255)

    declare c_dblist cursor for

    select name from master.dbo.sysdatabases order by name

    open c_dblist

    fetch next from c_dblist into @dbname

    while @@fetch_status = 0

    begin

    print @dbname

    exec(‘use ‘ + @dbname)

    –execute sp_MSforeachtable @command1=’DBCC DBREINDEX ([?], ””, 100)’ –TRYING TO GET THIS TO RUN ON EACH DATABASE ON A SERVER

    select name from sysusers

    fetch next from c_dblist into @dbname

    end

    close c_dblist

    deallocate c_dblist

    now the moment uncomment the following line

    –execute sp_MSforeachtable @command1=’DBCC DBREINDEX ([?], ””, 100)’, @command2=’print [?]’

    I can only get the master database to show and the execution will not work.

    How can I loop through each database on a server and perform the DBCC task using the sp_MSforeachtable procedure?

    I have included my partner Melvin in the CC

    Thank you

    Tim

    Timothy P. Early CIW-P, CIW-I, CIW-A

    Torrance Memorial Medical Center

    3330 W. Lomita Blvd.

    Torrance, CA 90505

    Office 310-325-9110 x2038

    Mobile 310-901-0163

    During the battle of Lookout Mountain, Tennessee, a surgeon asked a soldier where he was hurt. The wounded soldier answered,”Right near the top of the mountian.” He was not thinking of his gaping wound but was remembering that he had won the ground near the top of the mountain..

    Streams in the Desert by L.B. Cowman

    • riteshshah Says:

      can you try using this script?

      declare @dbname varchar(255)

      declare c_dblist cursor for

      select name from master.dbo.sysdatabases order by name

      open c_dblist

      fetch next from c_dblist into @dbname

      while @@fetch_status = 0

      begin

      print @dbname

      exec(‘use [‘ + @dbname + ‘]’)

      execute sp_MSforeachtable @command1=’DBCC DBREINDEX ([?], “”, 100)’ –TRYING TO GET THIS TO RUN ON EACH DATABASE ON A SERVER

      –select name from sysusers

      fetch next from c_dblist into @dbname

      end

      close c_dblist

      deallocate c_dblist


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: