Post
Ask Question

SP_MSForeachtable in Sql

Sometimes we need to query on the all the tables in one database single statement. We use SP_MSForeachtable this is known as undocumented stored procedures. These all are system stored procedures. These stored procedures is placed in the Master database.

NOTE: Please do not run all these queries in Production environment.

Example

  1. create database UnDocumentedStoredProcedure  
  2.   
  3. use UnDocumentedStoredProcedure  
  4.   
  5. create table Emp  
  6. (  
  7. ID int identity(1,1),  
  8. Name varchar(50),  
  9. Salary int  
  10. )  
  11.   
  12. insert into Emp(Name,Salary) values('rakesh',8000),('raju',9000)  
  13.   
  14. create table Dept  
  15. (  
  16. ID int identity(1,1),  
  17. DeptName varchar(100)  
  18. )  
  19.   
  20. insert into Dept(DeptName) values('CSE'),('IT')  
We have created a new database and also created some table with some dummy data.

Select all tables data:

exec sp_MSForeachtable 'select * from ?'

Checking all table names with number of rows:

exec sp_MSForeachtable 'select ''?'',count(*) from ?'

Delete all tables data:

exec sp_MSForeachtable 'delete from ?'

Truncate all tables data:

exec sp_MSForeachtable 'truncate table ?'

Disable all constraints from all tables:

exec sp_MSForeachtable 'alter table ? nocheck constraint all'

Enable all constraints from all tables:

exec sp_MSForeachtable 'alter table ? Check constraint all'

Note

Disabling and enabling constraints works only when check constraint or foreign key constraint on top table.