Android Robot

DnnForge - NewsArticles

12

If you have ever needed to write code to loop though each table in a database, or each database on a server, you most likely needed to build complicated, hard to read while cursors. Microsoft provides two undocumented Stored Procedures that will enable you to loop though all databases, or tables. The procedures are located in the masters table and the permissions default to members of the sysadmin server role, the db_owner database role and the owner of the database.

sp_MSforeachdb

  • @return is an integer that is set to the return value associated with SP
  • @command1 is a nvarchar(2000) field for specifying the first command to run against each database
  • @replaceChar is a nchar(1) field that represents the character used in the commands you are executing that will be replaced with the database name prior to being executed
  • @command2 is a nvarchar(2000) field for specifying the second command to run against each database
  • @command3 is a nvarchar(2000) field for specifying the third command to run against each database
  • @precommand is a nvarchar(2000) field for specifying a command to be run prior to processing any commands (@command1, @command2, @command3) against any databases
  • @postcommand is a nvarchar(2000) field for specifying a command to be run after all the commands against all database have been processes.

Â

The example below will run CHECKDB on all databases on the server

EXEC sp_MSforeachdb @command1='DBCC CHECKDB (''*'')' , @replacechar='*'

Â

sp_MSforeachtable

  • @return is an integer that will be set to the return code of the SP
  • @command1 is a nvarchar(2000) field used for specifying the first command to be executed
  • @replaceChar is a char(1) field to be used to identify the character in the command strings that will be replaced with the table name
  • @command2 and @command3 fields are nvarchar(2000) fields to specify the second and third commands, respectively, to be executed
  • @whereas command is a nvarchar(2000) field used to add additional constraints to further identify which objects in the sysobjects table that this SP will process
  • @precommand is a nvarchar(2000) field used to specify a command to be executed prior to processing each of the @command parameters, and
  • @postcommand, is a nvarchar(2000) field used to specify a command that will be processed after all the @command's have been processed against all tables.

Â

The example below will rebuild all the indexes in the database

EXEC sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')' , @replacechar='*'