sp_Msforeachtable güzel bir sistem prosedürü bir ara işinize yarayabilir
Sayfadan aldığım örnek ve kullanımı ile ilgili detaylı bilgi
xec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand
Where:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable"
@command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000)
@replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?")
@command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
@whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
@precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
@postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables
As you can see, there are quite a few options for the "sp_MSforeachtable" SP. Let's go through a couple of different examples to explore how this SP can be used to process commands against all the tables, or only a select set of tables in a database.
First let's build on our original example above and return row counts for tables that have a name that start with a "p." To do this we are going to use the @whereand parameter. Here is the code for this example:
use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = 'insert into #rowcount select ''?'',
count(*) from ?',
@whereand = 'and name like ''p%'''
select top 5 * from #rowcount
order by tablename
drop table #rowcount
On my machine, the above code produced the following output:
tablename rowcnt
------------------ -----------
[dbo].[pub_info] 8
[dbo].[publishers] 8
By reviewing the code above, you can see I am now using the @command1, and the @whereand parameter. The @whereand parameter above was used to constrain the WHERE clause and only select tables that have a table name that starts with a "p." To do this I specified "and name like ''p%''" for the @whereand parameter. If you needed to have multiple constraints like all tables that start with "p," and all the tables that start with "a," then the @whereand parameter would look like this:
and name like ''p%'' or name like ''a%''
Note, that in the @command1 string in the above example there is a "?". This "?" is the default replacement character for the table name. Now if for some reason you need to use the "?" as part of your command string then you would need to use the @replacechar parameter to specify a different replacement character. Here is another example that builds on the above example and uses the "{" as the replacement character:
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = 'insert into #rowcount select
''Is the rowcount for table {?'',
count(*) from {',
@replacechar = '{',
@whereand = 'and name like ''p%'''
select tablename as question, rowcnt from #rowcount
order by tablename
drop table #rowcount
Here is the output from this command on my machine:
question rowcnt
------------------------------------------------ -----------
Is the rowcount for table [dbo].[pub_info]? 8
Is the rowcount for table [dbo].[publishers]? 8
There are two more parameters to discuss, @precommand, and @postcommand. Here is an example that uses both of these commands:
exec sp_MSforeachtable
@command1 = 'print ''Processing table ?''',
@whereand = 'and name like ''p%''',
@precommand = 'Print ''precommand execution '' ',
@postcommand = 'Print ''postcommand execution '' '
Here is the output from this command when run on my machine:
precommand execution
Processing table [dbo].[pub_info]
Processing table [dbo].[publishers]
postcommand execution
As you can see, the "PRINT" T-SQL command associated with the "@precommand" parameter was only executed once, prior to processing through the tables. Whereas, the "@postcommmand" statement was executed after all the tables where processed, and was only executed once. Using the pre and post parameters would be useful if I had some processing I wanted done prior to running a command against each table, and/or I needed to do some logic after all tables where processed.
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm
