All Forums Database
fdaugherty 1 post Joined 08/12
05 Jun 2015
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.

dnoeth 4628 posts Joined 11/04
05 Jun 2015

You need Dynamic SQL to do pass object name.
So this is only possible using a cursor in a Stored Procedure.

Dieter

You must sign in to leave a comment.