Most frequently used SQL Syntaxes

Most often I were googling to find the simple enough syntexes while writing the sql query.

So I thought to List down all of them in single page and refer to that page while writing a sql query, Stored procedure or functions.

This may helpful to you.......

I will used the following table through out this article.

Create table Product (ProductId int, Category varchar(20) Default(0), ProductDesc varchar(200))

Here is a smart SQL Syntaxes.

1. If table exist, drop it.

Declare @table as varchar(100) SET @table='Product' if Object_ID(@table) is not null Exec('Drop table ' + @table)

2. Create Index on an existing table.

CREATE UNIQUE CLUSTERED INDEX CUnqIdxProductId ON Product (ProductId)

3. Drop Index.

DROP INDEX Product.CUnqIdxProductId

4. Alter Column

ALTER TABLE Product ALTER COLUMN ProductId longint Not null

5. Create the table with Primary Key (Without constraint Name)

CREATE TABLE [dbo].[Product](       [ProductId] [int] NOT NULL,       [Category] [varchar](20) NOT NULL,       [ProductDesc] [varchar](200) NULL,      PRIMARY KEY (       [ProductId] ASC,       [Category] ASC ))

6. Create the table with Primary Key (With constraint Name)

CREATE TABLE [dbo].[Product](   [ProductId] [int] NOT NULL,   [Category] [varchar](20) NOT NULL,   [ProductDesc] [varchar](200) NULL,  CONSTRAINT [pk_Product] PRIMARY KEY CLUSTERED (   [ProductId] ASC,   [Category] ASC ))

7. Alter statement to add the Primary Key Constraint in an existing table (without Constraint Name).

ALTER TABLE Product ADD PRIMARY KEY (ProductId, Category)

8. Alter statement to add the Primary Key Constraint in an existing table (with Constraint Name).

ALTER TABLE Product ADD CONSTRAINT pk_Product PRIMARY KEY (ProductId, Category)

9. Drop the Primary Key Constraint when Constraint Name is not Known.

DECLARE @PKConstraintName VARCHAR(500) SELECT @PKConstraintName=QUOTENAME(constraint_name) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Product' IF ISNULL(@PKConstraintName,'')<>'' EXEC('ALTER TABLE Product DROP CONSTRAINT ' + @PKConstraintName) GO

10. Drop the Default Constraint when Constraint Name is not Known.

DECLARE @table_name NVARCHAR(256) DECLARE @col_name NVARCHAR(256) DECLARE @Command  NVARCHAR(1000) SET @table_name = N'Products' SET @col_name = N'Category' SELECT @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name  FROM sys.tables t      JOIN    sys.default_constraints d           ON d.parent_object_id = t.object_id     JOIN    sys.columns c          ON c.object_id = t.object_id           AND c.column_id = d.parent_column_id  WHERE t.name = @table_name    AND c.name = @col_name EXECUTE(@Command)

11. Alter statement to add the Foreign Key Constraint in an existing table (without Constraint Name).

ALTER TABLE Product ADD FOREIGN KEY(CategoryID) REFERENCES CATEGORY (CategoryId)

12. Alter statement to add the Foreign Key Constraint in an existing table (with Constraint Name).

  

ALTER TABLE PRODUCT ADD CONSTRAINT FK_CATEGORY_CATEGORYID FOREIGN KEY(CategoryID) REFERENCES CATEGORY (CategoryId)

13.Create the table with Foreign Key (Without constraint Name)

CREATE TABLE [dbo].[Product](    [ProductId] [int] NULL,    [CategoryID] [int] NULL references Category(CategoryId),    [ProductDesc] [varchar](200) NULL ) ON [PRIMARY]

14.Create the table with Foreign Key (With constraint Name) 

CREATE TABLE [dbo].[Product](       [ProductId] [int] NOT NULL,       [Category] [int] NOT NULL,       [ProductDesc] [varchar](200) NULL,  CONSTRAINT [fk_Category] Foreign KEY (       [Category] )REFERENCES CATEGORY (CategoryId))

15.Drop the Foreign Key constraint when constraint name is known

Alter table [Product] Drop constraint FK_CATEGORY_CATEGORYID

16.Drop the Foreign Key constraint when constraint name is known

Alter table [Product] Drop constraint FK_CATEGORY_CATEGORYID