Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: All
Aug 4, 2000

SQL Stored Procedure to Run Single Command on All Tables

There is an undocumented stored procedure that you can use to run a single command on all tables in a database. It is very useful when doing routine jobs on all the tables. For example, re-indexing the tables would be done like this:
 sp_MSforeachtable [@command1 =] 'sql_command' 
   [, [@replacechar =] 'replacement_character'] 
   [, [@command2 =] 'sql_command'] 
   [, [@command3 =] 'sql_command'] 
   [, [@whereand =] 'sql_command'] 
   [, [@precommand =] 'sql_command'] 
   [, [@postcommand =] 'sql_command'] 
 
Typical Call 
 
sp_Msforeachtable "DBCC DBREINDEX ('?')",  
   @precommand = 'print ''Indexing ...''',
   @postcommand = 'print ''Completed ...'''
Enjoy and use this with care. It will do any command on all the tables.
Steve Smith
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap