Search code examples
sql-servert-sql

How can I use a variable to drop a table such as: DROP TABLE @dbname?

How can I use a variable to drop a table such as shown below?

This is a small part of a larger *.sql script, not a stored procedure. I could move it into one, although I'd like to avoid it if possible.

As is, it gives the error Incorrect syntax near '@dbname'.

DECLARE @dbname varchar(128) = 'Some_Name';

IF EXISTS (SELECT * FROM sys.objects WHERE name = @dbname)
DROP TABLE @dbname
Solution

  • You can use dynamic sql:

    DECLARE @tblname SYSNAME = 'Some_Name';
DECLARE @dropDDL NVARCHAR(1000) = 'DROP TABLE ' + QUOTENAME(@tblname);

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = @tblname)
EXEC(@dropDDL)