Performing commands on all tables in SQL Server database using the sp_MSforeachtable store procedure

From time to time I have a need to do an operation such as dropping all records or disabling all triggers for all tables in an database. Scripts using cursors can be used here, but an undocumented stored procedure sp_MSforeachtable residing in the SQL Server master DB can also be used.

Below are a couple of different uses cases for this sproc from disabling all triggers, updating statistics, checking row counts etc. Note, this stored procedure is undocumented so could be dropped or modified anytime so best not to rely on it in any of your production scripts but I’ve found it very handy in adhoc situations like prepping a DB for UAT. If you’ve got other examples of use, please send them on.

Disable all triggers
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

Enable all triggers
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

Disable all constraints
exec sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’

Enable all constraints
exec sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all’

Delete records in all tables
exec sp_MSForEachTable ‘DELETE FROM ?’

Truncate all tables (won’t work if any FKs)
exec sp_MSForEachTable ‘TRUNCATE TABLE ?’

Rows, size, space info for all tables
exec sp_MSforeachtable ‘exec sp_spaceused [?]’

Print all table names
exec sp_MSforeachtable ‘print “?”‘

Update statistics
exec sp_msforeachtable ‘UPDATE STATISTICS ?’

Defragments all indexes
exec sp_msforeachtable ‘dbcc indexdefrag(0, “?”)’

Reseed all tables
exec sp_msforeachtable ‘DBCC CHECKIDENT (“?”, RESEED, 0)’

Reindex all tables
exec sp_msforeachtable ‘dbcc dbreindex(“?”)’

Update a particular column. Column MUST EXIST ON ALL TABLES, otherwise execution will stop upon coming to a table without the column without a rollback.
exec sp_MSforeachtable ‘UPDATE ? SET Updated_by = ”DAVID”’

Add a column to all tables. If column already exists execution will continue.
exec sp_msforeachtable ‘alter table ? add flag bit not null default 0’;

Remove column for all tables. If column doesn’t exist execution will continue. Delete dependencies first.
exec sp_msforeachtable ‘alter table ? drop column [flag]’

Row count for all tables
create table #rowcount (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #rowcount select ”?”,count(*) row_count from ?’
select * from #rowcount order by tbl_name
drop table #rowcount

Number of rows updated since a certain date. Assumes all tables have an auditing date column on it. Tables which don’t have the column will not cause execution to stop.

create table #updateddaterows (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #updateddaterows select ”?”,count(*) from ? where Updated_date > ”20160101” having count(*) > 0’
select * from #updateddaterows order by tbl_name
drop table #updateddaterows

 

Number of rows updated by a user since a certain date.
create table #updatedbyrows (tbl_name varchar(100), row_count int)
exec sp_MSforeachtable ‘insert #updatedbyrows select ”?”,count(*) from ? where Updated_by = ”12345” and Updated_date > ”20160101” having count(*) > 0’
select * from #updatedbyrows order by tbl_name
drop table #updatedbyrows

Related Links

Article looking in detail at the parameters sp_msforeachtable takes

Leave a Reply

Your email address will not be published. Required fields are marked *