Using sp_executesql drop multiple tables

Question

I’m trying to write a script to purge all the tables (and views and functions and procedures) from the current dbo schema in the current database.

Bolting together what I have learned from other sources, I get something like this:

USE whatever_db;    

DECLARE @object NVARCHAR(255);

DECLARE curses CURSOR FOR 
    SELECT name 
    FROM sysobjects 
    WHERE type = 'U';

DECLARE @sql NVARCHAR(MAX);

OPEN curses;
FETCH NEXT FROM curses INTO @object;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT N'SELECT * FROM '+@object;
    SET @sql=N'DROP TABLE IF EXISTS @thing;';

    EXECUTE sp_executesql @sql, N'@thing VARCHAR(MAX)', @thing=@object;

    FETCH NEXT FROM curses INTO @object;
END;

CLOSE curses;
DEALLOCATE curses;

I understand that sysobjects has a list of objects, with type U naturally being the tables.

Using a cursor, I loop through these objects and try to execute a dynamic statement in the form:

DROP TABLE IF EXISTS something;

From Microsoft’s support site https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16 I understand that we can execute a parameterised string with sp_executesql, but it’s not exactly clear exactly how the following parameters work, though it appears that I follow the SQL string with a declaration and an assignment.

Except this doesn’t seem to work. I get a list of messages such as:

Msg 0, Level 0, State 1, Line 9
SELECT * FROM things

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@thing'

The PRINT statement outputs what I’m expecting, but the rest is, apparently, wrong.

What is the correct way to use sp_executesql with parameters to drop tables?

Update

From the comments below I gather that the parameterised version is for DML or DSL statements and not DDL. Fair enough, since, in real life it’s the DML and DSL where you might attempt to insert values from outside.

It appears that the solution is simply to concatenate the value:

BEGIN
    SET @sql = N'DROP TABLE IF EXISTS ' + quotemane(@object, '"');
    EXECUTE sp_executesql @sql;
    FETCH NEXT FROM curses INTO @object;
END;

I’ve also noted that using sys.objects is better than sysobjects, and I also reverse the order to drop dependent tables before the others:

DECLARE curses CURSOR FOR
SELECT name
FROM sys.objects
WHERE schema_id=1 AND is_ms_shipped=0 AND type='U'
ORDER BY object_id DESC;

You can't do this, in any relational database. Tables and columns aren't parameter values. They're equivalent to types in strongly typed languages. Parameter on the other hand are equivalent to .... parameters. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 9 at 8:00
SET @sql=N'DROP TABLE IF EXISTS @thing;'; should be this SET @sql=N'DROP TABLE IF EXISTS ' + @thing; i.e. the dynamic string you create must now be static SQL. — Dale K
– Dale K, Commented Apr 9 at 8:03
In any database product, SQL isn't executed as-is. It's compiled into an execution plan that specifies what table to access, what indexes to use, whether to use seek or scan, what join strategies to use, whether to cache (spool) temporary results. That execution plan depends on the actual tables, columns, indexes and even data statistics. Obviously you can't have an execution plan if you don't know the tables and columns involved. Query parameters are used to create an execution plan that accepts parameters. They aren't just value substitutions, even if they're strongly typed — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 9 at 8:06

Charlieface · Accepted Answer · 2025-04-09 09:47:18Z

5

sp_executesql doesn't magically make something parameterizable. And the DROP statements are not parameterizable. And if it was parametrizable then you wouldn't need dynamic SQL in the first place.

Also:

Don't use sys.sysobjects, it's an old compatibility feature. Use sys.objects instead.
You don't need a cursor, you can use STRING_AGG to make on big batch.
You aren't filtering by schema.
Objects names go in sysname variables.
You want to filter out any system functions or tables.

DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(
  CONVERT(nvarchar(max),
    CONCAT(
      N'DROP ',
      CASE
        WHEN o.type = 'U' THEN N'TABLE '
        WHEN o.type = 'V' THEN N'VIEW '
        WHEN o.type IN ('IF', 'FN', 'FS', 'AF', 'TF') THEN N'FUNCTION '
        WHEN o.type IN ('P', 'PC') THEN N'PROCEDURE '
      END,
      QUOTENAME(s.name),
      N'.',
      QUOTENAME(o.name),
      ';'
    )
  ), N'
')
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
  AND s.name = N'dbo';

PRINT @sql;

EXEC sp_executesql @sql;

Note that this script does not deal with triggers, foreign key, unique, check or default constraints, indexes or primary keys on these tables, which you would need to drop first, in that order.

answered Apr 9 at 9:47

Charlieface

78.9k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Manngo Apr 15 at 1:09

I’ve found that if I drop the tables in the reverse creation order I don’t need to worry about most of the constraints, especially the foreign key constraints.

Collectives™ on Stack Overflow

Using sp_executesql drop multiple tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related