MS SQL Server

by

SQL Server – The power of sys.sp_MSforeachTable function in MS SQL Server

If we want to perform an action on each and every tables (as it is self explanatory “for each Table” ) in a database.

Common uses of this store procedure.

  1. Truncate all the tables
  2. Disable check constraints for all tables
  3. Run delete command on all the tables
  4. To get the size of all the tables in the database
  5. To rebuild all indexes of all the tables in the database
  6. Disable all constraints and triggers of all the tables in the database
  7. Delete all the data from all the tables in the database
  8. To RESEED all tables to 0
  9. To get the Number of Rows in all tables in a database
  10. Update the statistics of all the tables in a database
  11. Reclaim space from dropped variable-length columns in tables or indexed views of the database

MS Sql Server provides you with some undocumented stored procedure “sys.sp_MSforeachTable”. It allows you to process some code against each and every table in a single database. This is a system stored procedure which comes with SQL server by default. This function has ability to run single command or multiple commands against tables in a single database.

Syntax

sp_MSforeachtable [ @command1 = ] ‘command1’ [ , [ @replacechar = ] replacechar ] [ , [ @command2 = ] command2 ] [ , [ @command3 = ] command3 ] [ , [ @whereand = ] where_and_Condition ] [ , [ @precommand = ] precommand] [ , [ @postcommand = ] postcommand]

Parameter

Parameter Description
@command1 It is the first command to be executed by this Stored Procedure and the data type is nvarchar(2000).
@replacechar It is a character in the command string that needs to be replaced with the table name being processed. The default value of this parameter is a “?”.
@command2 @command2 and @command3 are two additional commands that can be run for each table. Here first Command1 is executing then command2 and then command3 will execute.
@command3
@whereand This parameter could be used to provide additional constraints to the command for helping to identify the rows in the sysobjects table that will be selected. Its data type is nvarchar(2000).
@precommand This command is to be run before processing any table. Its data type is nvarchar(2000).
@postcommand This command is to be run after the processing of all the tables. Its data type is nvarchar(2000).

sp_MSforeachtable can be used to loop through all the tables in your databases. Here are some common usages of this useful stored procedure:

— Display the size of all tables in a database
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused ‘?'”

— Display Number of Rows in all Tables in a database
EXEC sp_MSforeachtable ‘SELECT ”?”, Count(*) as NumberOfRows FROM ?’

— Diable trigger on all the tables
EXEC sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’

— Disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’

— Enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’

— To RESEED all table to 0, use this script
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)
‘
— Update Statistics of all Tables in a database
EXEC sp_MSforeachtable ‘UPDATE statistics ? WITH ALL’


PaisaBazaar IN CPL - Credit Card

Share this:
Share
by

SQL SERVER – Convert IN to EXISTS for better performance

Today, I have received to optimize a store procedure where I have made changes and gotten good performance.

I can show simple method to convert IN clause to EXISTS clause. Here is the simple example.

USE AdventureWorks
GO
— use of =
SELECT *
FROM HumanResources.Employee E
WHERE E.EmployeeID = ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO
— use of EXISTS
SELECT *
FROM HumanResources.Employee E
WHERE EXISTS ( SELECT EA.EmployeeID
FROM HumanResources.EmployeeAddress EA
WHERE EA.EmployeeID = E.EmployeeID)
GO

It is NOT necessary that every time when IN is replaced by EXISTS it gives better performance. However, in my case listed above it does for sure give better performance.

Click on below image to see the execution plan.

inexists

Share this:
Share
by

SQL Server – How to create, alter, drop, enable, disable the Primary Key Constraint

The Primary Key constraint is used to identify each row uniquely in a database table.

Primary key constraint enforce the following rules.

  1. Primary keys must contain UNIQUE values.
  2. Primary key column cannot contain NULL values.
  3. Most tables should have a primary key, and each table can have only ONE primary key.
  4. Primary key can consist of one or more columns on a table (When multiple columns are used as a primary key, they are called a composite key.)
  5. Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).

Create primary key:
There are several ways to create a primary key in a database table.
At the time of table creation:

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar](50) NULL
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([ID] ASC)
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL PRIMARY KEY,
[StudentName] [nvarchar] (50) NULL
)

Method 3:
CREATE TABLE [dbo].[Table1](
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NULL,
PRIMARY KEY ( [ID] )
)

Create primary key on multiple columns:
For defining a PRIMARY KEY constraint on multiple columns and columns should not nullable.
use the following SQL syntax:


PolicyBazaar IN CPL - Personal Loan

Method 1:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL,
PRIMARY KEY CLUSTERED ( [ID], [StudentName] )
)

Method 2:
CREATE TABLE [dbo].[Table1] (
[ID] [int] NOT NULL,
[StudentName] [nvarchar] (50) NOT NULL
CONSTRAINT [PK_Table1] PRIMARY KEY ( [ID], [StudentName] ASC )
)

Create primary key on existing table:
To create a primary key constraint on the ID columns when Table1 table already exists.

use the following SQL syntax:

ALTER TABLE [Table1]
ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (ID);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column (s) must already have been declared to not contain NULL values (when the table was first created).

Drop primary key constraint:
You can clear the primary key constraints from the table.

use the following SQL syntax:

ALTER TABLE [Table1]
DROP PRIMARY KEY ;

Disable Primary key constraint :
You can disable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
DISABLE;


Voonik CPA

Enable Primary key constraint:
You can enable a primary key using the ALTER TABLE statement

use the following SQL syntax:

ALTER INDEX [PK_Table1] ON [Table1]
REBUILD;


PaisaBazaar IN CPL - Credit Card

Share this:
Share