SP_MSForeachtable

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

NOTE:

Please do not run all these queries in Production environment

Example:

create database UnDocumentedStoredProcedure

use UnDocumentedStoredProcedure

create table Emp

(

ID int identity(1,1),

Name varchar(50),

Salary int

)

insert into Emp(Name,Salary) values('rakesh',8000),('raju',9000)

create table Dept

(

ID int identity(1,1),

DeptName varchar(100)

)

insert into Dept(DeptName) values('CSE'),('IT')

We are created new database and also created some table with some dummy data.

Select all tables data:

exec sp_MSForeachtable 'select * from ?'

Checking all tables 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 table :

exec sp_MSForeachtable 'alter table ? nocheck constraint all'

Enable all constraints from all table :

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.