sp_MSforeachtable stored procedure provided by Microsoft, is used for smooth processing on all tables of a single database. So why to use complex query when we have already that procedure.
How and when to use sp_MSFooreachtable :
Parameters
sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
- @command1 nvarchar(2000) – is the first command to be executed
- @replacechar nchar(1) – is a character in the command string that will be replaced with the table name being processed (default character is “?”)
- @command2 nvarchar(2000)- additional command that can be run for each table. It runs after @ command1
- @command2 nvarchar(2000)- another additional command that can be run for each table. It runs after @ comman2
- @whereand nvarchar(2000)- you can specify the where clause in this parameter
- @precommand nvarchar(2000) - specifies a command to be run prior to processing any table
- @postcommand nvarchar(2000)- command to be executed after all commands have been processed
| Eg 1 : Retriving all tables starting with 'c' and their row count CREATE TABLE #rowcount (tablename varchar(128), cnt int) EXEC sp_MSforeachtable @command1 = 'Insert into #rowcount select ''?'', count(*) from ?', @whereand = 'and o.name like ''p%''', @postcommand = 'SELECT * from #rowcount' DROP TABLE #rowcount Eg 2: Disabling all constraints on all tables |
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' |
Eg 3: Enabling all constraints on all tables
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Eg 4: Deleting rows from all tables
EXEC sp_MSForEachTable 'DELETE FROM ?' |
Eg 5: Reseed identity column
If any table doesn't contain identity column, it raise error and as a quality of stored procedure it will move on
EXEC sp_MSforeachtable 'DBCC
CHECKIDENT ( ''?'', RESEED, 0)'
|
