CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlsql-server-2005sql-server-2008-r2
Indranil.Bharambe
Indranil.Bharambe

Reputation: 1498

Truncate Table With Foreign Key

I have one database having above 1000+ tables. I am creating fresh Blank setup. For that i have to truncate all the tables but some of the tables i do not wish to truncate so i created on table and stored the names in the table.

    ----------- Create hardcode table ----------------------
    Create table TblHardCodeTableNotToTruncate(TableName varchar(100))
    go
    insert into TblHardCodeTableNotToTruncate
    select 'TblHardCodeTableNotToTruncate'
    go

    ---- insert the table names which dont wish to truncate ------------
Insert into TblHardCodeTableNotToTruncate
select 'TblAccount'
go
Insert into TblHardCodeTableNotToTruncate
select 'TblCity'
go

etc, Following is the query i used to truncate all the tables except these inserted tables

------------------------- Truncate all the tables except the tables specified in the Hardcode table -------------------------

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

GO

DECLARE @TBLTEMP TABLE(TABLENAME VARCHAR(100))
insert into @TBLTEMP
select name from sysobjects where xtype = 'U'

delete from @TBLTEMP where tablename in (
select tablename from TblHardCodeTableNotToTruncate)

DECLARE @SQLQUERY VARCHAR(MAX) =''
DECLARE @INTCNT INT = 1

DECLARE @TABLENAME VARCHAR(100) =''


WHILE (SELECT COUNT(*) FROM @TBLTEMP) > 0
BEGIN


            select  top 1 @TABLENAME = TABLENAME from @TBLTEMP
            SET @SQLQUERY = 'Truncate table ' + @TABLENAME
            EXEC(@SQLQUERY)
            PRINT @SQLQUERY
    DELETE FROM @TBLTEMP WHERE TABLENAME = @TABLENAME   
END
go

EXEC sp_MSForEachTable  'ALTER TABLE ? CHECK CONSTRAINT ALL'

GO

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
go

But i get the error foreign key reference error. I know i need to drop constraint before doing it. But is there any way to do it as i cannot drop then truncate and again add each time with so many tables. Is there any unique script can we write in below which will drop constraint if exists,truncate table and add the dropped constraint again to the table again.

Upvotes: 1

Views: 29595

Answers (2)

jean
jean

Reputation: 4350

Even if all tabled are empty and you disable all constraints the truncate ill fail due to the mere FK existence. You really must drop it.

You can create a script to automate it (truncate all tables dropping and recreating FKs) I found one here (use with discretion) http://www.sqlservercentral.com/scripts/Development/62892/

Also if you just want to create a "blanket" DB you can export the script to create all objects and just populate that few (domain?) tables do you need.

Upvotes: 0

ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

I'm afraid that there is no way to do that without dropping constraints, you can write a code that extracts the constraints for the tables and drop them it's simple.

Create a temporary table and use this code to get the tables constraint and notice that the sysconstraints.status must be 1 or 3 to get foreign and primary keys:

Select  SysObjects.[Name] As "Constraint Name",
        Tabls.[Name] as "Table Name",
        Cols.[Name] As "Column Name"
From SysObjects Inner Join 
(Select [Name],[ID] From SysObjects) As Tabls
On Tabls.[ID] = Sysobjects.[Parent_Obj] 
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
Inner Join SysColumns Cols On Cols.[ColID] = sysconstraints.[ColID] 
And Cols.[ID] = Tabls.[ID]
where sysconstraints.status in (1, 3)
order by [Tabls].[Name]

you gonna have to recreate these constraints again after truncate, that's simple too, but I'm not gonna do all your job :)

Upvotes: 3

Related Questions