Delete and Drop Select Tables via Macro

Database

Delete and Drop Select Tables via Macro

I am attempting to write a macro to delete and drop specific tables from a database.  Any suggestions on fixing this or a better method?

SQL Statement:

CREATE MACRO tbl_nme AS

(SELECT TABLENAME

FROM    DBC.TablesV

WHERE DataBaseName = 'MyDatabase_A_001' AND TABLENAME NOT IN('table_1','table_2','table_3')  ;) ;

EXECUTE DELETE ("MyDatabase_A_001".":tbl_nme") ;

Which returns this error:

EXECUTE Failed.  [3707] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between the 'EXECUTE' keyword and the 'DELETE' keyword.

Tags (3)
1 REPLY
Senior Apprentice

Re: Delete and Drop Select Tables via Macro

You need Dynamic SQL to do pass object name.

So this is only possible using a cursor in a Stored Procedure.