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
TryCREATE 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.
