Database Records clears in all tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Database Records clears in all tables

Author

Topic

parvai
Starting Member

1 Post

Posted - 2005-09-17 : 04:57:24

How to clear all data in database

scullee
Posting Yak Master

103 Posts

Posted - 2005-09-17 : 05:20:10

Lots of delete statements

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-17 : 10:26:24

Does your database use foreign keys?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-17 : 11:07:56

Script the database out, drop it, then create it from scratch using the script.

Kristen
Test

22859 Posts

Posted - 2005-09-18 : 02:28:00

[code]
PRINT '----- TRUNCATE TABLE SECTION -----'
SELECT 'TRUNCATE TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13) + CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
--
PRINT ''
PRINT '----- DELETE ROW SECTION -----'
SELECT 'DELETE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13) + CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
--
PRINT ''
PRINT '----- COUNT(*) SECTION -----'
SELECT 'SELECT COUNT(*), ''' + TABLE_SCHEMA + '.' + TABLE_NAME
+ '''FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
[/code]
Run this to generate a script. Then run the first part to TRUNCATE any tables that will allow it (e.g. they have no FKs). If there are no errors you are done.

If there are errors then run the second DELETE ROW script. Keep running this until you get no errors and all data has been deleted.

Then run the COUNT(*) stuff to double check that all tables have zero rows

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-19 : 12:01:06

Found this script sitting in one of my folders. Looks like it strips off your constraints, then you can do what you want, then puts them back on.
don't remember where i found it. I did not write it.

<code>
create proc dbo.Script_Out_Constraints

as
set nocount on

declare @tbl table(MyID int identity(1,1), table_name varchar(100))
declare @tdrop table (drop_c nvarchar(1000))
declare @taddu table (add_u nvarchar(1000))
declare @taddf table (add_f nvarchar(1000))

declare @counter int

declare @table_name varchar(100)
declare @constraint_name varchar(100)
declare @sql varchar(500)

insert into @tbl
select
distinct table_name
from
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
where table_name <> 'dtproperties'

select @counter = max(MyID) from @tbl

While @counter >0

Begin
select @table_name = table_name from @tbl where MyID = @counter
-- insert into drop table
-- this will drop in correct order
insert into @tdrop
SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [drop_c]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE') AND TC.TABLE_NAME = @table_name)
OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND FC.TABLE_NAME = @table_name)
ORDER BY FC.TABLE_NAME DESC,
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC

-- insert into create
-- this will create in correct order
insert into @taddu
SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
' ' + TC.CONSTRAINT_TYPE +
CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'WITH FILLFACTOR = ' +
CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
ISNULL(NULLIF(
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'),
0), 100)
) + CHAR(13)+CHAR(10)+
'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [add_u]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
AND s.id = object_id(TC.TABLE_NAME)
LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
AND TC.TABLE_NAME = @table_name
GROUP BY
TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_NAME,
TC.CONSTRAINT_TYPE,
sfg.groupname
ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC

-- foreign keys etc

insert into @taddf
SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
' (' +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [add_f]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
AND TC.TABLE_NAME = @table_name
GROUP BY
FC.TABLE_SCHEMA,
FC.TABLE_NAME,
FC.CONSTRAINT_NAME,
FC.CONSTRAINT_TYPE,
TC.TABLE_SCHEMA,
TC.TABLE_NAME

select @counter = @counter - 1

END
select '-- DROP STATEMENTS'
select * from @tdrop
select '-- ADD UNIQUE KEYS'
select * from @taddu
select '-- ADD OTHER KEYS and CONSTRAINTS'
select * from @taddf

</code>

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-20 : 00:45:41

>>don't remember where i found it. I did not write it.

Probably from this

http://www.sqljunkies.com/WebLog/amachanic/articles/ScriptTableConstraints.aspx

Madhivanan

Failing to plan is Planning to fail

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-20 : 09:57:40

Bingo!

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Hunglech
Starting Member

16 Posts

Posted - 2005-09-21 : 01:05:59

I thing in this case sort tables first, if table a dependend on table b then delete record in table a first, after that delete record in table b.
Script to sort all objects in database

CREATE PROCEDURE fs_SortObjects AS
BEGIN
DECLARE @xid INT, @xnumber NUMERIC(16)
CREATE TABLE #tResult(xid INT, xnumber NUMERIC(16), xcal BIT)
INSERT INTO #tResult SELECT id, 1, 0 FROM sysobjects WHERE xtype <> 'S' AND xtype <> 'F' AND xtype <> 'PK' AND xtype <> 'D' AND xtype <> 'TR' AND type <> 'S' AND category <> 2 AND (NOT (name LIKE '%proper%'))
SELECT id AS id, depid AS depid INTO #sysdepends FROM sysdepends WHERE id IN (SELECT xid FROM #tResult) AND depid IN (SELECT xid FROM #tResult) GROUP BY id, depid
INSERT INTO #sysdepends(id, depid) SELECT fkeyid, rkeyid FROM sysforeignkeys GROUP BY fkeyid, rkeyid
SELECT id, depid INTO #tsysdepends FROM #sysdepends
UPDATE #tResult SET xcal = 1 WHERE xid NOT IN (SELECT id FROM #tsysdepends)
DELETE #tsysdepends WHERE id IN (SELECT xid FROM #tResult WHERE xcal = 1)
WHILE EXISTS (SELECT * FROM #tResult WHERE xcal = 0)
BEGIN
DECLARE cr CURSOR FOR SELECT xid FROM #tResult WHERE xcal = 0
OPEN cr
FETCH NEXT FROM cr INTO @xid
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT * FROM #tResult a JOIN (SELECT depid FROM #tsysdepends WHERE id = @xid) b ON a.xid = b.depid WHERE a.xcal = 0)
BEGIN
IF EXISTS(SELECT* FROM #tResult WHERE xid IN (SELECT depid FROM #sysdepends WHERE id = @xid))
BEGIN
SELECT @xnumber = SUM(xnumber) FROM #tResult WHERE xid IN (SELECT depid FROM #sysdepends WHERE id = @xid)
UPDATE #tResult SET xnumber = @xnumber + 1, xcal = 1 WHERE xid = @xid
DELETE #tsysdepends WHERE id IN (SELECT xid FROM #tResult WHERE xcal = 1)
END
END
FETCH NEXT FROM cr INTO @xid
END
CLOSE cr
DEALLOCATE cr
END
SELECT b.name, a.xnumber FROM #tResult a JOIN sysobjects b ON a.xid = b.id ORDER BY a.xnumber, b.crdate
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources