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 thenBEGIN
DROP TABLE [Mytable]
END
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.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)
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 scriptAS
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
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
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
One more short Solution we can use:
ReplyDeleteIF OBJECT_ID('dbo.Table_Name)', 'U') IS NOT NULL
DROP TABLE Table_Name;
Hi Reza, small modification. removed small bracket after table name which was not necessary. This works fine. Thanks
DeleteIF OBJECT_ID('dbo.Table_Name', 'U') IS NOT NULL
DROP TABLE Table_Name;