Run A Delete Statement Certain Table Names Stored In A Table

I have a table which stores the names of certain tables - tableNames. I'd like to run a DELETE statement on some of those tables (deleting all the rows from the tables they represe

Solution 1:

You cannot do it that way because the inner SELECT is simply another set you're deleting from.

Basically you're creating a table of table names and telling the DB to delete it. Even iterating through them won't work without dynamic sql and EXEC

Do you need to automate this process?

What I've done in the past is something like this

SELECT'DELETE '+ tableName
FROM
    tablenames
WHERE
   [conditions]

your output will look like this:

DELETE myTableName1
DELETE myTableName2
DELETE myTableName3

And then simply copying the results of this query out of the window and running them.

IF you need to automate this in SQL you can concatenate all the output strings in the result and send them as a parameter to an EXEC call.

Solution 2:

try using cursor :

DECLARE@tableNamevarchar(255)

DECLARE cur cursorforselect tableName from tableNames where (...)

OPEN CUR
FETCH NEXT FROM cur into@tableName

WHILE @@FETCH_STATUS =0BEGINexec('DELETE '+@tableName)
FETCH NEXT FROM cur into@tableNameENDCLOSE cur
DEALLOCATE cur

Solution 3:

In this respect, you can think of SQL as a compiled language like C/C++. The SQL statement is evaluated by a "compiler", and certain checks are done. One of those checks is for the existence (and permissions) for tables and columns referenced directly in the query. Exact table names must be present in your code at the time you build your query, so that the compiler can validate it.

The good news is that SQL is also a dynamic language. This means you can write a procedure to build a query as a string, and tell the database to execute that string using the EXEC command. At this point, all the same "compiler" rules apply, but since you were able to insert table names directly into your SQL string, the query will pass.

The problem is that this also has security implications. It would be a good idea to also check your table against a resource like information_schema.Tables, to avoid potential injection attacks. Unfortunately, if you're deleting whole tables your whole model may already be suspect, such that you can't guarantee that someone won't inject a table name that you really want to keep. But depending on how these are populated, you may also be just fine.

Solution 4:

Assuming no potential constraint errors exist, one interesting possibility is an undocumented procedure sp_MSforeachtable, which will allow you to apply a given operation against all tables whose names are returned by your query:

EXEC sp_MSforeachtable @command1='delete from ?'
, @whereand='and o.name IN (SELECT tableName FROM tablesNames WHERE ...)'

Also http://weblogs.asp.net/nunogomes/archive/2008/08/19/sql-server-undocumented-stored-procedure-sp-msforeachtable.aspx for more reading.

Solution 5:

The delete statement works with only one table name at a time.

The full syntax is documented here, but it's TL;DR... In short, you'll have to use the loop.