--이문장을 실행하시면 모든테이블을 대상으로 해당 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!'''
