April 13, 2008 at 11:50 pm
Hi ,
Is there any Way to delete all the datas in all the tables of a database without reloading the database.
Expecting your valuable suggestions.Thanks in Advance for your kind co-operation
Best Regards,
Rajesh
April 14, 2008 at 12:46 am
Hi there,
Maybe this is what you are looking for:
/*****************************************************************************************************************************************
* Purpose This procedure deletes all records from all tables in the specified database
* (except schema 'sys' and databases master, model, tempdb and msdb)
* At first all constraints are disabled, afterwards all records are deleted
* (truncate would not work due to foreign keys) and
* finally all constraints are enabled
*
******************************************************************************************************************************************/
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id('dbo.proc_clear_database') and TYPE = 'P')
DROP PROCEDURE dbo.proc_clear_database;
go
CREATE PROCEDURE dbo.proc_clear_database
@db_name sysname = null
AS
BEGIN
SET NOCOUNT ON
IF @db_name IS NULL
SET @db_name = db_name()
IF NOT EXISTS(SELECT * FROM master.sys.databases WHERE name = @db_name and database_id > 4)
BEGIN
RAISERROR('Database does not exist or it can not be truncated', 16, 1)
RETURN
END
-------------------------------------------------------------------------------------------
-- prepare table with tables list
CREATE TABLE #temp_tables
(
schema_name sysname NOT NULL,
table_name sysname NOT NULL
)
DECLARE @n_cmd NVARCHAR(MAX)
SET @n_cmd = 'INSERT INTO #temp_tables
SELECT ss.name AS schema_name, st.name AS table_name
FROM [' + @db_name + '].sys.tables AS st
INNER JOIN [' + @db_name + '].sys.schemas AS ss ON ss.schema_id = st.schema_id
WHERE ss.name <> ''sys'' '
EXEC sp_executesql @n_cmd
-------------------------------------------------------------------------------------------
-- disable constraints
DECLARE @table_name SYSNAME
DECLARE @schema_name SYSNAME
DECLARE database_tables CURSOR local read_only FOR
SELECT
table_name,
schema_name
FROM
#temp_tables
OPEN database_tables
FETCH next FROM database_tables INTO @table_name, @schema_name
WHILE @@fetch_status = 0
BEGIN
SET @n_cmd = 'ALTER TABLE [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] NOCHECK CONSTRAINT ALL'
PRINT @n_cmd
EXEC sp_executesql @n_cmd
FETCH next FROM database_tables INTO @table_name, @schema_name
END
CLOSE database_tables
----------------------------------------------------------------------------------------------
-- delete records from tables
OPEN database_tables
FETCH next FROM database_tables INTO @table_name, @schema_name
WHILE @@fetch_status = 0
BEGIN
SET @n_cmd = 'DELETE [' + @db_name + '].[' + @schema_name + '].[' + @table_name + ']'
PRINT @n_cmd
EXEC sp_executesql @n_cmd
FETCH next FROM database_tables INTO @table_name, @schema_name
END
CLOSE database_tables
-----------------------------------------------------------------------------------------------
-- enable constraints
OPEN database_tables
FETCH next FROM database_tables INTO @table_name, @schema_name
WHILE @@fetch_status = 0
BEGIN
SET @n_cmd = 'ALTER TABLE [' + @db_name + '].[' + @schema_name + '].[' + @table_name + '] WITH CHECK CHECK CONSTRAINT ALL'
PRINT @n_cmd
EXEC sp_executesql @n_cmd
FETCH next FROM database_tables INTO @table_name, @schema_name
END
CLOSE database_tables
DEALLOCATE database_tables
DROP TABLE #temp_tables
END
go
-- example:
--EXEC dbo.proc_clear_database 'AdventureWorks'
Kind regards,
Oana Schuster.
April 14, 2008 at 1:15 am
Hi Oana Schuster,
Thanks a lot ,Your Query Worked Fine .
One Small Request ,the query will look much powerful if we reseed the identity columns in the tables to 0 once we delete records from each table.Because if the table contains the Identity columns then next Time when we insert it starts from the last identity value.
Thanks and Regards,
Rajesh
April 14, 2008 at 2:12 am
Hi,
Thanks for your tip 🙂
I will take into consideration what you suggested.
Thanks a lot,
Oana.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply