Looping through tables in a db

Hello - I am somewhat new to stored procedures, so please be patient. I need to know how to go about wring a stored proc to loop through all of the tables in a db and delete the records in them.

I'm assuming a stored procedure is the best way to do this. If there is a better way, please let me know

I think I'm on the right track with this below

/* Create in each database that it is used in */

CREATE PROC usp_DBCCCheckTable

AS

/* Declare Variables */

DECLARE @.v_table sysname,

@.v_SQL NVARCHAR(2000)

/* Declare the Table Cursor (Identity) */

DECLARE c_Tables CURSOR

FAST_FORWARD FOR

SELECT name

FROM sysobjects obj (NOLOCK)

WHERE type = 'U'

OPEN c_Tables

Try

CREATE PROCEDURE DeleteYourTableRecords
AS
BEGIN
declare @.SQL_Str nvarchar(max)
set @.SQL_Str=''
SELECT @.SQL_Str=@.SQL_Str +'delete from ' + name +';' from sys.tables
EXECUTE sp_executesql @.SQL_Str
END
GO|||

I cant dare running this on my machine so here you go with PRINT statement. just uncomment EXEC if you dare.

Sorry i dont like CURSORS so here you go ... hope this will help

DECLARE @.TABLE_NAME VARCHAR(500)

DECLARE @.CMD VARCHAR(MAX)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

TABLE_TYPE = 'BASE TABLE'

ORDER BY

OBJECT_ID(TABLE_NAME)

WHILE @.@.ROWCOUNT > 0

BEGIN

SET @.CMD = 'TRUNCATE TABLE ' + @.TABLE_NAME

PRINT @.CMD

--EXEC(@.CMD)

SELECT TOP 1

@.TABLE_NAME = TABLE_NAME

FROM

INFORMATION_SCHEMA.TABLES

WHERE

OBJECT_ID(TABLE_NAME) > OBJECT_ID(@.TABLE_NAME)

END

|||

Be sure you want to "DELETE" rather than "TRUNCATE"

Here's the code to do it. Don't run this unless you REALLY REALLY want to delete all the data from all your tables.

Code Snippet

select 'delete from [' + U.Name + '].[' + O.Name + '];' as [CMD]

into #CMDS

from sysobjects O

inner join sysusers U on U.UID = o.UID

where O.xType = 'U'

declare @.cmd varchar(255)

while exists(select * from #cmds)

begin

select top 1 @.cmd = cmd from #cmds

exec(@.cmd)

delete from #cmds where cmd = @.cmd

end

drop table #cmds

|||

In most cases, it is not as simple as deleting from each table in the database. If there are foreign key references then you need to delete data in a particular order unless you have cascading actions (it depends on the schema and it doesn't work for all types of relationships). Otherwise, you will get errors when you try to delete rows that are referenced by other tables. And truncate table as suggested in other replies is more restrictive. Lastly, what happens to any business logic in triggers? Do you need to unnecessarily execute them?

So what are you trying to do exactly? Are you trying to cleanup the database? It is better to just recreate the db from scratch using the scripts from your source code control system.

|||

I think that it will be fairly easy to just delete the rows in the tables. The way that my company's software is set up, there aren't many foreign keys. It's a long story and a dba would have a fit looking at it. I'm fairly used to now though. We do most of our linking through code.

Anyway, when we install a new system, we go through and delete all of our test data out of the tables and sometimes that can take awhile. I thought, since I'm trying to get better at stored procedures anyway, that it would be useful to write one to delete the rows out of the tables.