How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually)
But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm.
thanks in advance,
mulata
Does Automatically means just sending a query from you program? DELETE FROM TABLENAME
|||yes, but how can I implement that in my .NET program?
The Database should simply be cleared totally, so all tables should be deleted.
|||You would need to explain a little bit more rather than one liners. Do you need to delete all the tables or some of them or just one of them?
You could write a stored proc to drop all the tables and just call the proc from your .NET code.
|||okay, I just want to delete all tables in a database.
Im sorry but I have no idea how I can write such a proc, which I can call from my .NET code.
It would be great if someone can give me an example of such a proc.
Actually I think what the proc has to do, is just connect to the database, and the make a simple call, but I dont know what there is to do in detail.
thanks in advance
|||SqlConnection connection =
new
SqlConnection (ConfigurationSettings.AppSettings[
"ConnectionString"]);
try
{
connection.Open ();
SqlCommand cmd =
new SqlCommand ("delete from (select * from sys.tablenames)" )
cmd.ExecuteNonQuery ();
}
finally
{
connection.Close ();
}
|||Depending on your business requirements if you dont need the data ever, you might look into truncatng the tables. DELETE'ing can bloat the logs. TRUNCATE is a non-logged operation.
Read up the documentation regarding DELETE, TRUNCATE and it wil lhelp you decide which one worls best for you.
CREATE PROC dbo.prcDeleteAllTables
AS
BEGIN
TRUNCATE TABLE tableA
END
