Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’  and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee (   ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY ,   NAME VARCHAR ( 20 ),   SALARY INT ) INSERT INTO Employee   ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...
1 comment
Read more

Variables in T-SQL

Variables can be used to store the data in temporally based on data type. Variable name begin with @ symbol. There are two types of variables in T-SQL. 1.   Local Variables (It must begin @ symbol). 2.   Global Variables (it must begin @@ symbol) this variables also known as system variables. Syntax Declare a Variable DECLARE   @Variable-Name   DATATYPE EXAMPLE: DECLARE   @Name   VARCHAR ( 100 ) In before 2008 versions of SQL SERVER we declare a variable and assign a value in 2 lines.                 DECLARE   @Name   VARCHAR ( 100 ) SET   @Name = 'LeadFirstSQL' In 2008 and Later versions of SQL SERVER we declare a variable and assign a value in single line. DECLARE   @Name   VARCHAR ( 100 )   = 'LeadFirstSQL' Variables must be declare and used with the same batch. DECLARE   @Name   VARCHAR ( 100 ) ...
Post a Comment
Read more