Can anybody help me with this?
I prefer if I don't have to explicitly list the table names.
A:
How about dropping the database? DROP DATABASE <database name>
Of course, that's rough on stored procedures, triggers, etc. But if the purpose is to eliminate all the tables in order to recreate them, it makes sense that you'd recreate all the other associated components as well, such as indexes.
wallyk
2010-05-04 03:03:17
Why drop the database? One would loose all the other objects as well.
ydobonmai
2010-05-04 03:07:08
It seems you anticipated my addendum. Without the tables, there's very little value in the other objects.
wallyk
2010-05-04 03:09:16
That may work, but i'm not sure what will happen to various settings like users roles etc, i don't think i can recreate them via ant.
Anthony
2010-05-04 04:28:17
It's been 8+ years since I've touched SQLserver, but Oracle and MySQL store the user roles separately from the database. For permissions specific to a table, it would be unreasonable to expect those to survive a dropped table.
wallyk
2010-05-04 05:12:51
+1
A:
Saw this before...
exec sp_MSforeachtable "DROP TABLE ? PRINT '? to be dropped' "
Source - http://sqlserver-qa.net/blogs/t-sql/archive/2008/05/20/4266.aspx
xt_20
2010-05-04 03:06:48