Delete All Rows

Is there a way to programatically delete all rows from a number of tables?
I am developing an application and am doing a lot of testing with dummy
data. As a result, I am regularly deleting the contents of tables. If I
could automate this down to a simple piece of code that would be great.
ThanksKeith,
This is from Uri's post sometime back.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
http://groups.msn.com/SQLBang
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||Create a cursor which reads off of INFORMATION_SCHEMA.TABLES or sysobjects a
nd for each table, fire TRUNCATE
TABLE or DELETE statement. Just be aware of foreign keys. If you have such,
I suggest you start with "bottom
most" tables and do TRUNCATE for all tables that no other refers to and for
those that are referenced, do
DELETE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Keith" <@..> wrote in message news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...">
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>|||If this is just for use in a development environment then it's maybe
acceptable to use the undocumented funcion sp_msforeachtable:
EXEC SP_MSFOREACHTABLE '-- TRUNCATE TABLE ?'
or
EXEC SP_MSFOREACHTABLE '-- DELETE FROM ?'
(remove the -- comment from these statements to run them, but only when
you're sure you want to do this!)
TRUNCATE TABLE will fail on tables with Foreign Key dependencies so you may
need to use the DELETE version.
David Portas
SQL Server MVP
--|||Just be aware that if you use delete, the log will grow significantly. Be
sure to backup the log (perhaps even between each delete ) if the tables are
large, to keep the log from growing growing growing..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Keith" <@..> wrote in message
news:%23lNF8J5TEHA.3512@.TK2MSFTNGP12.phx.gbl...
> Is there a way to programatically delete all rows from a number of tables?
> I am developing an application and am doing a lot of testing with dummy
> data. As a result, I am regularly deleting the contents of tables. If I
> could automate this down to a simple piece of code that would be great.
> Thanks
>