Using sp_executesql drop multiple tables

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;

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.