SQL - DDL (Data Definition Language)

Tables

Change table name

sp_rename 'tblCustomer', 'Customer'

Remove table

drop table Customer

Create table

CREATE TABLE Supplier
( SupplierID  integer,
  Name        varchar(30),
  Address     varchar(50),
  PostalCode varchar(10)
 CONSTRAINT pkSupplier PRIMARY KEY (SupplierID),
 CONSTRAINT fk_City_Supplier_PostalCode FOREIGN KEY (PostalCode) REFERENCES City(PostalCode)
)

Fields (columns)

Change field name

EXEC sp_rename 'Customer.Margin', 'Rate', 'column'

Add field

ALTER TABLE [Customer] ADD Age numeric(11,3) (not) null default 0
ALTER TABLE [Customer] ADD CustomerID int IDENTITY(1,1) not null

Remove field

ALTER TABLE [Customer] DROP COLUMN Age

Change field

ALTER TABLE [Customer] ALTER COLUMN Age int NULL

Constraints

Add constraint

ALTER TABLE [dbo].[FeeRule] WITH CHECK ADD CONSTRAINT [fkFee_FeeRule_FeeID] FOREIGN KEY([FeeID])
REFERENCES [dbo].[Fee] ([FeeID])
GO
ALTER TABLE [dbo].[FeeRule] CHECK CONSTRAINT [fkFee_FeeRule_FeeID]
go

Rename key

sp_rename 'PK_Levering', 'PK_Delivery'

Remove constraint

ALTER TABLE [dbo].[FeeDescription] DROP CONSTRAINT [fkFeeFeeDescriptionFeeID]

Add primary key

ALTER TABLE Customer ADD PRIMARY KEY (CustomerID)

ALTER TABLE Customer ADD CONSTRAINT
pkCustomer PRIMARY KEY NONCLUSTERED
(
CustomerID
) ON [PRIMARY]

Find all constraints referencing a certain table

EXEC sp_fkeys 'TableName'

Types

http://odetocode.com/articles/79.aspx
http://www.mssqlcity.com/Articles/General/using_constraints.htm
http://msdn.microsoft.com/en-us/library/ms188258.aspx

Stored procedures

Remove stored procedure

DROP PROCEDURE procedurename

Rename stored procedure

EXEC sp_rename 'oldName', 'newName'

Triggers

Remove triggers

DROP TRIGGER TriggerName

Check if trigger exists

IF EXISTS (
SELECT *
FROM sys.objects
WHERE [type] = 'TR' AND [name] = 'blablabla'
)
DROP TRIGGER blablabla;
GO

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License