컴퓨토피아

--이문장을 실행하시면 모든테이블을 대상으로 해당 command가 실행됩니다.
exec sp_MSforeachtable "exec sp_spaceused '?'"

아래 내용은 http://www.bstsoftware.com/tsug/Nov99/sp_MSforeachtable.html 에서 발췌

sp_MSforeachtable (T-SQL)

--------------------------------------------------------------------------------

Syntax

sp_MSforeachtable [@command1 =] 'sql_command'
   [, [@replacechar =] 'replacement_character']
   [, [@command2 =] 'sql_command']
   [, [@command3 =] 'sql_command']
   [, [@whereand =] 'sql_command']
   [, [@precommand =] 'sql_command']
   [, [@postcommand =] 'sql_command']

Arguments

[@command1 =] 'sql_command'     Is the sql command to apply to each user table with the
replacement_character where the table name would normally be placed. 'sql_command' is nvarchar(2000)*,
varchar(255)+ with no default. 

[, [@replacechar =] 'replacement_character']     Is the wild card character that will be replaced with each
user table name. replacement_character is char(1) with a default of '?' 

[, [@command2 =] 'sql_command']     Is the continuation of the sql command entered in @command1 to apply
to each user table with the replacement_character where the table name would normally be placed.
sql_command is nvarchar(2000)*, varchar(255)+ with a default of NULL. (Note: This parameter is not as useful
on 7.0 since there are 4000 characters available in @command1) 

[, [@command3 =] 'sql_command']     Is the continuation of the sql command entered in @command2 to apply
to each user table with the replacement_character where the table name would normally be
placed. 'sql_command' is nvarchar(2000)*, varchar(255)+ with a default of NULL. (Note: This parameter is not
as useful on 7.0 since there are 4000 characters available in @command1) 

[, [@whereand =] 'sql_command']     Is an additional where clause to limit the result set of the user table
select. sql_command is nvarchar(2000)*, varchar(255)+ with a default of NULL. No wild card replacement
occurs. It is added to the select that retrieves the user tables as passed to the procedure. It MUST be a valid
where clause for a select against sysobjects. 

[, [@precommand =] 'sql_command']     Is the sql command to be executed once before @command1 is
processed. sql_command is nvarchar(2000)*, varchar(255)+ with a default of NULL. No wild card replacement
occurs. It is executed as passed to the procedure. 

[, [@postcommand =] 'sql_command']     Is the sql command to be executed once after @command1 is
processed. sql_command is nvarchar(2000)*, varchar(255)+ with a default of NULL. No wild card replacement
occurs. It is executed as passed to the procedure. 

Return Code Values

0 (success) or @@ERROR (failure)

Result Sets

sp_MSforeachtable returns this message if no parameters are specified:

Procedure 'sp_MSforeachtable' expects parameter '@command1', which was not supplied.

Remarks

The procedure sp_MSforeachtable executes a set of commands against all the user tables in the current
database. The sp_MSforeachtable procedure executes the precommand if entered. It then declares a cursor
called hCForEach against sysobjects in the current database and passes the actual commands to be executed
to sp_MSforeach_worker. The post command is then executed if entered.

Examples

Get the number of rows in each user table

sp_MSforeachtable 'print ''?'' select count(*) from ?'

sp_MSforeachtable 'select count(*) "?" from ?'

Grant SELECT permission to the developers on every user table

EXECUTE sp_MSforeachtable 'GRANT SELECT ON ? TO Developers'

Grant all permissions to the database maintenance role on every user meta data table

EXECUTE sp_MSforeachtable @command1    = 'PRINT ''?'' GRANT SELECT , INSERT , UPDATE , DELETE
ON ? TO dbMaintenace'
                        , @whereand    = 'AND name LIKE ''tbl%REF'' ORDER BY uid , name'
                        , @precommand  = 'PRINT ''Granting permission on...'''
                        , @postcommand = 'PRINT ''Complete!'''