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
- 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 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.