SQL Server: If Table exists drop it

Firstly I had to drop a table if it exists. A simple T-SQL script is
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Mytable') AND TYPE IN (N'U'))
BEGIN
DROP TABLE [Mytable]
END
If the table name which needs to be dropped has to be passed dynamically then
Declare @TableName As varchar(20)
Declare @sql As varchar(4000)
SET @TableName = 'Mytable'
set @SQL = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+ @TableName+''''+') AND TYPE IN (N'+'''U'''+')) BEGIN
DROP TABLE ' + Quotename(@TableName) + ' END'
Exec (@sql)
We can create stored procedure to pass table name and drop it if exists as below.
CREATE PROCEDURE DropTableIfExists @TableName As varchar(20)
AS
BEGIN
DECLARE @SQL As Varchar(4000)
set @SQL = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'+''''+ @TableName+''''+') AND TYPE IN (N'+'''U'''+')) BEGIN
DROP TABLE ' + Quotename(@TableName) + ' END'
EXEC (@sql)
END
If we need to perform some task if table exist else do somethig else the use this script
CREATE PROC [dbo].[TblExistCheck] @ChkTable As varchar(10)
as
BEGIN
declare @sql varchar(1000)

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                                 WHERE  TABLE_NAME = @ChkTable))
BEGIN
 -- Do some thing

END
ELSE
BEGIN
-- Do other thing

END
END

2 comments:

  1. One more short Solution we can use:
    IF OBJECT_ID('dbo.Table_Name)', 'U') IS NOT NULL
    DROP TABLE Table_Name;

    ReplyDelete
    Replies
    1. Hi Reza, small modification. removed small bracket after table name which was not necessary. This works fine. Thanks
      IF OBJECT_ID('dbo.Table_Name', 'U') IS NOT NULL
      DROP TABLE Table_Name;

      Delete