|
|
Posted by Erland Sommarskog on 10/10/07 21:48
hunkgym (hunkgym@yahoo.com) writes:
> Currently I am using MS SQL Server 2000.
>
> I wish to delete all data in the database table except data in login
> table and security table. Furthermore, TRUNCATE is done on stand-alone
> and child tables, else DELETE is done if table has any foreign key
> references (normally refer to parent tables).
>
> Is there any suggested script/stored procedure to automate the
> process?
Here is some stuff that I have lying around. It works from a list of
tables rather than all tables in the database (because that is what
we need to do). If you really want to empty all table you can just fill
that temp table from sysobjects.
Note that there are two stored procedures. The first administers the
cleansing and sets up the order, and the second does the deleting.
CREATE PROCEDURE zz_delete_all_transdata_sp @password char(5) = NULL AS
IF isnull(@password, "") <> "nisse"
BEGIN
RAISERROR("What's da password?", 16, -1)
RETURN 1
END
DECLARE @err int
SELECT @err = 0
CREATE TABLE #tables (tbl sysname NOT NULL PRIMARY KEY,
refcount int NULL)
-- Add all tables to be cleansed here. Preferably in alphabetic order.
-- (We will sort them according to references later on.)
INSERT #tables(tbl) VALUES ('abaautojobexecutions')
INSERT #tables(tbl) VALUES ('abaeventlog')
INSERT #tables(tbl) VALUES ('abaeventlogparameters')
INSERT #tables(tbl) VALUES ('accountrecledges')
INSERT #tables(tbl) VALUES ('accountstats')
-- Delete all tables not available in this database.
DELETE #tables
FROM #tables t
WHERE NOT EXISTS (SELECT *
FROM sysobjects o
WHERE o.name = t.tbl)
-- Add temporary table for references.
CREATE TABLE #refs (ref_from sysname NOT NULL,
ref_to sysname NOT NULL,
PRIMARY KEY (ref_from, ref_to))
-- Now we add all foreign key references.
INSERT #refs (ref_from, ref_to)
SELECT DISTINCT o1.name, o2.name
FROM sysobjects o1, sysobjects o2, sysreferences r, #tables t
WHERE o1.id = r.fkeyid
AND o2.id = r.rkeyid
AND o2.name = t.tbl
AND o1.name <> o2.name
AND NOT EXISTS (SELECT *
FROM #refs ref
WHERE ref.ref_from = o1.name
AND ref.ref_to = o2.name)
-- Check that there are no tables referring the list above, but is not in
-- the list at all.
IF EXISTS (SELECT *
FROM #refs r
WHERE NOT EXISTS (SELECT *
FROM #tables t
WHERE t.tbl = r.ref_from))
BEGIN
SELECT r.ref_from
FROM #refs r
WHERE NOT EXISTS (SELECT *
FROM #tables t
WHERE t.tbl = r.ref_from)
RAISERROR("These tables appear to be missing in #tables", 16, -1)
RETURN 1
END
DECLARE @refcount int,
@rowc int
-- Now, we first mark all tables that are not referenced by any other table.
UPDATE #tables
SET refcount = 0
FROM #tables t
WHERE NOT EXISTS (SELECT *
FROM #refs r
WHERE r.ref_to = t.tbl)
SELECT @rowc = @@rowcount
-- Then loop as long as there unmarked tables.
SELECT @refcount = 1
WHILE @rowc <> 0
BEGIN
UPDATE #tables
SET refcount = @refcount
FROM #tables t
WHERE t.refcount IS NULL
AND NOT EXISTS (SELECT *
FROM #refs r, #tables t2
WHERE r.ref_to = t.tbl
AND r.ref_from = t2.tbl
AND t2.refcount IS NULL)
SELECT @rowc = @@rowcount
SELECT @refcount = @refcount + 1
END
-- Did we mark them all?
IF EXISTS (SELECT * FROM #tables WHERE refcount IS NULL)
BEGIN
SELECT * FROM #tables WHERE refcount IS NULL
RAISERROR ("Could not determine ref.count for all tables. Circular references?", 16, -1)
RETURN 1
END
-- Eventually, we can do the cleansing job.
DECLARE @tbl sysname
SELECT @err = 0
WHILE @err = 0
BEGIN
SELECT @tbl = NULL
SELECT TOP 1 @tbl = tbl FROM #tables ORDER BY refcount, tbl
IF @tbl IS NULL
BREAK
EXEC @err = zz_cleanse_table_sp @tbl
SELECT @err = coalesce(nullif(@err, 0), @@error) IF @err <> 0 RETURN @err
DELETE #tables WHERE tbl = @tbl
END
go
CREATE PROCEDURE zz_cleanse_table_sp @table sysname AS
SET ROWCOUNT 20000
SET NOCOUNT ON
DECLARE @count int,
@db sysname,
@err int,
@err2 int,
@sql nvarchar(4000)
SELECT @db = db_name()
SELECT @sql = "SELECT @cnt = COUNT(*) FROM " + @table
EXEC @err = sp_executesql @sql, N'@cnt int OUTPUT', @count OUTPUT
SELECT @err = coalesce(nullif(@@error, 0), @err, -4711) IF @err <> 0 RETURN @err
RAISERROR("Cleansing %d records in table %s", 0, -1, @count, @table)
EXEC('ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ALL')
EXEC('ALTER TABLE ' + @table + ' DISABLE TRIGGER ALL')
SELECT @sql = "SELECT @err = 0 " +
"WHILE @err = 0 AND EXISTS(SELECT * FROM " + @table +
") BEGIN DELETE " + @table + " SELECT @err = @@error END"
EXEC @err = sp_executesql @sql, N'@err int OUTPUT', @err2 OUTPUT
SELECT @err = coalesce(nullif(@@error, 0), @err2, @err, -4711) IF @err <> 0 RETURN @err
EXEC('ALTER TABLE ' + @table + ' CHECK CONSTRAINT ALL')
EXEC('ALTER TABLE ' + @table + ' ENABLE TRIGGER ALL')
EXEC ('DBCC UPDATEUSAGE (''' + @db + ''',''' + @table + ''') WITH NO_INFOMSGS')
BACKUP TRANSACTION @db WITH NO_LOG
RETURN @err
go
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|