User Tools

Site Tools


notes:sql:drop

DROP object

Drop a database:

-- Another database must be selected before dropping the current one.
USE master
GO
DROP DATABASE MyDatabase
GO

Drop a table:

-- Drop a table if it exists.
IF OBJECT_ID('Books') IS NOT NULL DROP TABLE Books
GO

Drop a column:

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' 
    AND TABLE_NAME='Customer' AND COLUMN_NAME='CustomerHomePhone') 

Drop a stored procedure:

-- Method #1
IF OBJECT_ID('sp_SprocName') IS NOT NULL DROP PROC sp_SprocName
GO
 
-- Method #2
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'sp_SprocName') DROP PROC dbo.sp_SprocName
GO
 
-- Method #3
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[sp_SprocName]') 
    AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [sp_SprocName]

Drop a constraint:

-- Drop a unique constraint UN_Ticket_TicketNo on a table Ticket
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[UN_Ticket_TicketNo]') 
    AND OBJECTPROPERTY(id, N'IsUniqueCnst') = 1)
ALTER TABLE Ticket DROP CONSTRAINT UN_Ticket_TicketNo
notes/sql/drop.txt · Last modified: 2015/06/24 by admin