Delete all data

Question

Post reply

Delete all data

Adam Sandler

SSCrazy

Points: 2697
More actions
March 29, 2007 at 4:00 am

#116361

I have searched for this but not found it - how can I order my tables so the data can be deleted form all of them. So I have to order table so FK constraints are recognised and the tables tackled in order.

(I have seen a solution where all constraints are turned off and then back on again after the deletion of records - but that is a little too dangerous for me - and incorrectly turns on all constraints)

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

SET NOCOUNT ON

DECLARE @Constraints TABLE

(

ConstraintID SMALLINT IDENTITY(0, 1),

UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),

UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),

UNIQUE_CONSTRAINT_NAME NVARCHAR(128),

CONSTRAINT_CATALOG NVARCHAR(128),

CONSTRAINT_SCHEMA NVARCHAR(128),

CONSTRAINT_NAME NVARCHAR(128),

TABLE_CATALOG NVARCHAR(128),

TABLE_SCHEMA NVARCHAR(128),

TABLE_NAME NVARCHAR(128)

&nbsp

INSERT @Constraints

(

UNIQUE_CONSTRAINT_CATALOG,

UNIQUE_CONSTRAINT_SCHEMA,

UNIQUE_CONSTRAINT_NAME,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

&nbsp

SELECT rc.UNIQUE_CONSTRAINT_CATALOG,

rc.UNIQUE_CONSTRAINT_SCHEMA,

rc.UNIQUE_CONSTRAINT_NAME,

tc.CONSTRAINT_CATALOG,

tc.CONSTRAINT_SCHEMA,

tc.CONSTRAINT_NAME,

kcu.TABLE_CATALOG,

kcu.TABLE_SCHEMA,

kcu.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG

AND kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA

AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME

LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG

AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA

AND rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME

WHERE OBJECTPROPERTY(OBJECT_ID(tc.TABLE_NAME), 'IsMSShipped') = 0

AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')

DECLARE @Tables TABLE

(

UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),

UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),

UNIQUE_CONSTRAINT_NAME NVARCHAR(128),

CONSTRAINT_CATALOG NVARCHAR(128),

CONSTRAINT_SCHEMA NVARCHAR(128),

CONSTRAINT_NAME NVARCHAR(128),

TABLE_CATALOG NVARCHAR(128),

TABLE_SCHEMA NVARCHAR(128),

TABLE_NAME NVARCHAR(128)

&nbsp

INSERT @Tables

(

UNIQUE_CONSTRAINT_CATALOG,

UNIQUE_CONSTRAINT_SCHEMA,

UNIQUE_CONSTRAINT_NAME,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

&nbsp

SELECT NULL,

NULL,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

AND TABLE_TYPE = 'BASE TABLE'

DELETE t

FROM @Tables t

INNER JOIN @Constraints c ON t.TABLE_CATALOG = c.TABLE_CATALOG

AND t.TABLE_SCHEMA = c.TABLE_SCHEMA

AND t.TABLE_NAME = c.TABLE_NAME

DECLARE @Tree TABLE

(

RowID SMALLINT IDENTITY(0, 1),

RowKey VARBINARY(6478),

Generation SMALLINT,

ConstraintID SMALLINT,

CONSTRAINT_CATALOG NVARCHAR(128),

CONSTRAINT_SCHEMA NVARCHAR(128),

CONSTRAINT_NAME NVARCHAR(128),

TABLE_CATALOG NVARCHAR(128),

TABLE_SCHEMA NVARCHAR(128),

TABLE_NAME NVARCHAR(128)

&nbsp

INSERT @Tree

(

Generation,

ConstraintID,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

&nbsp

SELECT 0,

ConstraintID,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

FROM @Constraints

WHERE UNIQUE_CONSTRAINT_CATALOG IS NULL

AND UNIQUE_CONSTRAINT_SCHEMA IS NULL

AND UNIQUE_CONSTRAINT_NAME IS NULL

UNION

SELECT 0,

NULL,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

FROM @Tables

ORDER BY TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

DELETE t

FROM @Tree t

INNER JOIN @Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG

AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

AND c.TABLE_NAME = t.TABLE_NAME

and c.UNIQUE_CONSTRAINT_CATALOG IS NOT NULL

AND c.UNIQUE_CONSTRAINT_SCHEMA IS NOT NULL

AND c.UNIQUE_CONSTRAINT_NAME IS NOT NULL

INNER JOIN @Tree x ON x.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG

AND x.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA

AND x.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME

AND x.TABLE_CATALOG = t.TABLE_CATALOG

AND x.TABLE_SCHEMA = t.TABLE_SCHEMA

AND x.TABLE_NAME <> t.TABLE_NAME

DELETE c

FROM @Constraints c

INNER JOIN @Tree t ON t.ConstraintID = c.ConstraintID

UPDATE @Tree

SET RowKey = CAST(RowID AS VARBINARY)

DECLARE @Generation SMALLINT

SELECT @Generation = 0

WHILE @@ROWCOUNT > 0

BEGIN

SELECT @Generation = @Generation + 1

INSERT @Tree

(

RowKey,

Generation,

ConstraintID,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME,

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

&nbsp

SELECT t.RowKey,

@Generation,

c.ConstraintID,

c.CONSTRAINT_CATALOG,

c.CONSTRAINT_SCHEMA,

c.CONSTRAINT_NAME,

c.TABLE_CATALOG,

c.TABLE_SCHEMA,

c.TABLE_NAME

FROM @Constraints c

INNER JOIN (

SELECT RowKey,

CONSTRAINT_CATALOG,

CONSTRAINT_SCHEMA,

CONSTRAINT_NAME

FROM @Tree

WHERE Generation = @Generation - 1

&nbsp t ON t.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG

AND t.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA

AND t.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME

ORDER BY c.TABLE_CATALOG,

c.TABLE_SCHEMA,

c.TABLE_NAME

UPDATE @Tree

SET RowKey = RowKey + CAST(RowID AS VARBINARY)

WHERE Generation = @Generation

UPDATE t

SET t.ConstraintID = c.ConstraintID,

t.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG,

t.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA,

t.CONSTRAINT_NAME = c.CONSTRAINT_NAME

FROM @Tree t

INNER JOIN @Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG

AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

AND c.TABLE_NAME = t.TABLE_NAME

WHERE t.Generation = @Generation

AND c.UNIQUE_CONSTRAINT_CATALOG IS NULL

AND c.UNIQUE_CONSTRAINT_SCHEMA IS NULL

AND c.UNIQUE_CONSTRAINT_NAME IS NULL

DELETE c

FROM @Constraints c

INNER JOIN @Tree t ON t.ConstraintID = c.ConstraintID

END

SELECT Generation [Level],

TABLE_CATALOG,

TABLE_SCHEMA,

TABLE_NAME

FROM @Tree

ORDER BY RowKey

Level TABLE_CATALOG TABLE_SCHEMA TABLE_NAME

----- ------------- ------------ ----------

0 Alsis dbo Dimensions

1 Alsis dbo Models

2 Alsis dbo Rules

3 Alsis dbo Translations

2 Alsis dbo Translations

2 Alsis dbo Trees

1 Alsis dbo Prioritygroups

2 Alsis dbo Rules

3 Alsis dbo Translations

0 Alsis dbo Members

1 Alsis dbo Models

2 Alsis dbo Rules

3 Alsis dbo Translations

2 Alsis dbo Translations

2 Alsis dbo Trees

0 Alsis dbo Owners

1 Alsis dbo Models

2 Alsis dbo Rules

3 Alsis dbo Translations

2 Alsis dbo Translations

2 Alsis dbo Trees

0 Alsis dbo TEST

0 Alsis dbo Versions

1 Alsis dbo Models

2 Alsis dbo Rules

3 Alsis dbo Translations

2 Alsis dbo Translations

2 Alsis dbo Trees

1 Alsis dbo Prioritygroups

2 Alsis dbo Rules

3 Alsis dbo Translations

1 Alsis dbo Versions

Tree

---------------------------

Dimensions

Models

Rules

Translations

Trees

Prioritygroups

Rules

Translations

Members

Models

Rules

Translations

Trees

Owners

Models

Rules

Translations

Trees

TEST

Versions

Models

Rules

Translations

Trees

Prioritygroups

Rules

Translations

Versions

N 56°04'39.16"
E 12°55'05.25"

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Take a look at the info on this link:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

Adam Sandler SSCrazy Points: 2697 More actions · Answer 3

Thanks both of you - I found this which seems to do the job -

CREATE PROCEDURE procDeleteAll

AS

DECLARE @sql nvarchar(4000)

SET @sql = ''

IF OBJECT_ID('Hello')IS NOT NULL

DROP TABLE Hello

CREATE TABLE Hello (type int, oName varchar(517), owner varchar(517), seq int )

INSERT Hello EXEC sp_MSdependencies NULL, 3, NULL, NULL, 1

DECLARE tcursor CURSOR READ_ONLY FOR

SELECT oName

FROM Hello

WHERE oName NOT IN

(

'Hello', 'SELECT oName FROM HelloDone'

)

ORDER BY seq DESC

DECLARE @sName varchar(517)

OPEN tcursor

FETCH

FROM tcursor

INTO @sName

WHILE @@FETCH_STATUS = 0

BEGIN

--Display the name to check progress...

PRINT CHAR(13)+''+@sName

EXEC ('DELETE FROM '+@sName)

SELECT @sql = 'INSERT HelloDone(oName) Values(''' + @sName + ''')'

SELECT @sql = replace(@sql,'@sname',@sName)

EXEC(@SQL)

FETCH NEXT FROM tcursor INTO @sName

END

CLOSE tcursor

DEALLOCATE tcursor

GO