August 17, 2010 at 5:29 pm
Hi Folks,
In my current project i need to clean the tables before i run Packages on it.
However because of Foreign key relation its not allowing to use TRUNCATE Command but if i use Delete its taking lot of time.
So i want disable all the Foreign keys on my database.so that i can go with TRUNCATE instead of Delete.
Could you please some one suggest ...
Thnaks for your help
August 17, 2010 at 5:57 pm
You could try using the [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] view for each DB, loop through the results issuing a drop constraint command.
August 17, 2010 at 8:14 pm
krish.sett (8/17/2010)
Hi Folks,In my current project i need to clean the tables before i run Packages on it.
However because of Foreign key relation its not allowing to use TRUNCATE Command but if i use Delete its taking lot of time.
So i want disable all the Foreign keys on my database.so that i can go with TRUNCATE instead of Delete.
Could you please some one suggest ...
Thnaks for your help
Here you go
-- before dropping replace the DROP with create, so that you can put the FK's back
-- change the o/p to text mode
use [dbname]
go
SELECT 'alter table '+user_name(uid)+'.'+object_name(parent_obj)+' DROP CONSTRAINT '+name,char(13)+char(10)+'go'
from sysobjects where xtype = 'F'
go
HTH,
Cheers !
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
August 17, 2010 at 10:16 pm
Hi,
Follow these steps,
1) Generate Add FK Statements.
SET NOCOUNT ON
---Create table to insert the proc values
IF OBJECT_ID('dbo.fkeys', 'U') IS NOT NULL
DROP TABLE dbo.fkeys
go
CREATE TABLE dbo.fkeys (c1 varchar(8000))
GO
-- Generate Adds for All Foreign Keys in Database
DECLARE @fkName varchar(800), @tabName varchar(800), @refName varchar(800)
DECLARE @isDel int, @isUpd int, @fkCol varchar(8000), @refCol varchar(8000)
DECLARE @pline varchar(8000), @fline varchar(8000)
set @fline = ''
DECLARE fkCursor CURSOR FOR
select distinct object_name(constid), object_name(fkeyid),
object_name(rkeyid),
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ),
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' )
from sysforeignkeys k
order by object_name(fkeyid)
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @isDel, @isUpd
WHILE @@FETCH_STATUS = 0
BEGIN
select @fkCol = NULL
SELECT @fkCol = ISNULL(@fkCol + ', ','') + '[' + col_name(fkeyid, fkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @refCol = NULL
SELECT @refCol = ISNULL(@refCol + ', ','') + '[' + col_name(rkeyid, rkey) + ']'
from sysforeignkeys
where object_name(constid) = @fkName
order by keyno
select @pline = 'ALTER TABLE [dbo].[' + @tabName +
'] ADD CONSTRAINT [' + @fkName + ']' +
CHAR(13) + CHAR(10) +
' FOREIGN KEY (' + @fkCol + ') REFERENCES [dbo].[' + @refName +
'] (' + @refCol + ')'
if @isDel = 1
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON DELETE CASCADE'
if @isUpd = 1
select @pline = @pline + CHAR(13) + CHAR(10) +
' ON UPDATE CASCADE'
select @pline = @pline + CHAR(13) + CHAR(10)
set @fline=@fline + @pline
INSERT INTO fkeys VALUES(@pline)
FETCH NEXT FROM fkCursor
INTO @fkName, @tabName, @refName, @isDel, @isUpd
END
CLOSE fkCursor
DEALLOCATE fkCursor
2) DROP FK's
DECLARE @LINE VARCHAR(MAX)
DECLARE fkCursor CURSOR FOR
select distinct 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + ' '
from sysforeignkeys
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @LINE
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @LINE
EXEC (@LINE)
FETCH NEXT FROM fkCursor
INTO @LINE
END
CLOSE fkCursor
DEALLOCATE fkCursor
3) EXEX generate add fk's
-- exec the generated add fk statements
DECLARE @sql varchar(8000), @stmts varchar(8000)
--set @sql = ''
DECLARE fkCursor CURSOR FOR
select * from fkeys
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @stmts
WHILE @@FETCH_STATUS = 0
BEGIN
--print @stmts
exec (@stmts)
FETCH NEXT FROM fkCursor
INTO @stmts
END
CLOSE fkCursor
DEALLOCATE fkCursor
August 18, 2010 at 1:59 pm
Thank you very much...
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] is really help full
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply