CODEWITHSUNDEEP
c#sqlsql-server
Minsk
Minsk

Reputation: 315

Database drop table

If I have a select t.name as tableName from sys.tables t... How can I drop all those tables? I mean I have them, but I want to do something like...

drop table where tableName in select t.name as tableName from sys.tables t

An option would be with C# to create and run some SP? Any ideea?

Upvotes: 1

Views: 353

Answers (3)

Dan Guzman
Dan Guzman

Reputation: 46301

Below is an example script. Use with caution.

DECLARE @SQL nvarchar(MAX) = '';
SELECT @SQL += N'DROP TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) + N';
'
FROM sys.tables
<include your where clause here>

EXEC(@SQL);

Upvotes: 3

Devart
Devart

Reputation: 122032

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    IF OBJECT_ID(''' + SCHEMA_NAME(schema_id) + '.' + QUOTENAME(name) + ''', ''U'') IS NOT NULL
        DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';
    '
    FROM sys.objects
    WHERE [type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

PRINT @SQL
EXEC sys.sp_executesql @SQL

Upvotes: 0

Steve
Steve

Reputation: 216343

In C# you could build a code like this. First execute the SELECT to fill a datatable with the table names, then use the StringBuilder class to build a batch command with all the DROP required.

Strongly suggest to have a backup before runnning this code.....

string cmdText = "select t.name as tableName from sys.tables t";
SqlCommand cmd = new SqlCommand(cmdText, connection);
connection.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
string baseCmd = "DROP TABLE {0};\r\n";
StringBuilder sb = new StringBuilder();
foreach(DataRow r in dt.Rows)
   sb.AppendFormat(baseCmd, r["tableName"].ToString());

cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();

Upvotes: 2

Related Questions