Delete all records from a Database

Is there a way to delete all records in a SQL Server 2005 database? A function or a script?

use delete or truncate command to delete the records

Delete from <tablename>

don;t specify the condition in delete query, it will delete all records

or

truncate table <table name>

|||

Hi

can u try this one

sqlconnection con= new sqlconnection();

con.Open();

cmd =newSqlCommand("Delete <table name>", con);

int rows = cmd.ExecuteNonQuery();

if (rows != 0)

{

lblMsg.Text =" Row(s) Deleted Successfully... ";

}

|||

There is not a single command to do what you are asking. You can run something like this in Query Analyzer then cut and paste the results into Query Analyzer and run them all at once.

SELECT 'truncate table ' + table_name
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'

|||

I dont want to delete all rows from one table! I want to delete all rows from all tables at once!

|||

I know there isnt a single command. I was wontering if anyone had implemented a script for this. The one you propose is a good idea but wont function if the tables are related with keys. You must first truncate tables with foreing keys. If we can think a way of sorting the results it will work...

|||

to generate a script you have required to find the all tables in ur databases,

you can find all the tables of ur database by using

select*from sysobjectswheretype='u'

then you required to write a cursor, which is taking the name of table and append the table name to the delete query,

its like

fetch from --

declare @.query varchar(1000)

{

@.query=@.query + " Delete from " @.yourTableName"

}

then write

Exec(@.query)

this will delete all the rows from all the tables

|||

this is the script

DECLARE DeleteRecordsCURSORFOR

selectnamefrom sysobjectswheretype='u'

DECLARE @.queryvarchar(1000)

DECLARE @.tableNamevarchar(100)

DECLARE @.TEMPVARCHAR(100)

set @.query='';

set @.TEMP='';

OPEN DeleteRecords;

FETCH NEXTFROM DeleteRecordsinto @.tableNameWHILE@.@.FETCH_STATUS= 0

BEGIN

set @.TEMP=' Delete from '+@.tableNameset @.query= @.query+ @.TEMP

FETCH NEXTFROM DeleteRecordsinto @.tableName

END

Print(@.query)

EXEC(@.query)

CLOSE DeleteRecords;

DEALLOCATE DeleteRecords;

GO

|||Thanks for the script, although the constraint issue remains!