-- dropping a table IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U')) DROP TABLE [dbo].[TestTable] -- dropping a view IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[TestView]')) DROP VIEW [dbo].[TestView] -- dropping a stored procedure IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestProcedure]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[TestProcedure] -- dropping a fucntion IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestFunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[TestFunction]
-- dropping an index IF EXISTS (SELECT Name FROM sysindexes WHERE Name = 'IX_TestTable') DROP INDEX dbo.TestTable.IX_TestTable
Even if Copy-Paste does its magic and SQL Server allows generating scripts for existing objects, there’s still some work do be done in order to drop an object. Fortunately, with SQL Server 2016 Microsoft introduced a simplified syntax for dropping an object, namely DROP IF EXISTS.
The general syntax:
DROP <object_type> [ IF EXISTS ] <object_name>
The above statements can be written as follows:
-- dropping the table DROP TABLE IF EXISTS dbo.TestTable -- dropping the view DROP VIEW IF EXISTS dbo.TestView -- dropping the procedure DROP PROCEDURE IF EXISTS dbo.TestProcedure -- dropping the function DROP FUNCTION IF EXISTS dbo.TestFunction
-- dropping the index DROP INDEX IF EXISTS dbo.TestTable.IX_TestTable
Similarly can be dropped aggregates, assemblies, roles, triggers, rules, databases, schemas, users, sequences, synonyms, etc. The scripts will run also when the objects don’t exist.
An object can't be dropped if explicit dependencies exist on them, e.g. when the table is referenced by a FOREIGN KEY constraint. For each object there are specific rules that apply, therefore for more details check the documentation.
To explore the functionality here are the definitions of the above objects and the further scripts to test them:
-- creating the test table CREATE TABLE dbo.TestTable(City nvarchar(50) , PostalCode nvarchar(50)) -- creating the test view CREATE VIEW dbo.TestView AS SELECT 'Test' as Result -- creating the test stored procedure CREATE PROCEDURE dbo.TestProcedure AS BEGIN SELECT 'Test' as Result END -- creating the test function CREATE FUNCTION dbo.TestFunction() RETURNS nvarchar(50) BEGIN RETURN 'Test' END
-- creating the test index CREATE NONCLUSTERED INDEX [IX_TestTable] ON [dbo].[TestTable] ( [PostalCode] ASC ) --testing the table SELECT * FROM dbo.TestTable -- testing the view SELECT * FROM dbo.TestView -- testing the procedure EXEC dbo.TestProcedure -- testing the function SELECT dbo.TestFunction() as Result
Moreover, the IF EXISTS can be used when dropping the constraint or column of a table:
-- adding a new column
ALTER TABLE dbo.TestTable
ADD DateFrom datetime2(0)
-- adding a constraint on it
ALTER TABLE dbo.TestTable
ADD CONSTRAINT [DF_DateFrom_Default] DEFAULT (GetDate()) FOR [DateFrom]
-- inserting test data
INSERT INTO dbo.TestTable(City, PostalCode)
VALUES ('New York', 'XZY')
--testing the changes
SELECT *
FROM dbo.TestTable
-- dropping the constraint
ALTER TABLE dbo.TestTable
DROP CONSTRAINT IF EXISTS DF_DateFrom_Default
-- dropping a column from a table
ALTER TABLE dbo.TestTable
DROP COLUMN IF EXISTS DateFrom
--testing the changes
SELECT *
FROM dbo.TestTable
If a constraint exists on the column first must be dropped the constraint and after that the column, like in the above example.
Happy coding!
No comments:
Post a Comment