By at November 22, 2008 01:36
Filed Under:
------------
-- Generate Drop Disable.sql
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Drops for All Foreign Keys in Database
print '-- Drop Foreign Keys'
print ''
select distinct 'ALTER TABLE ['+ SCHEMA_NAME(CAST(OBJECTPROPERTYEX(tbl.id , 'SchemaId') AS INT)) +'].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
from sysforeignkeys
JOIN sysobjects ON sysobjects.id = constid
JOIN sysobjects tbl ON tbl.id = fkeyid
JOIN syscolumns col ON col.id = fkeyid AND col.colid = fkey
JOIN sysobjects ltbl ON ltbl.id = rkeyid
JOIN syscolumns lcol ON lcol.id = rkeyid AND lcol.colid = rkey
go
-- Generate Disable all triggers
print ''
print '-- Disable Triggers'
print ''
select distinct 'ALTER TABLE [dbo].[' + OBJECT_NAME(parent_obj) +
'] DISABLE TRIGGER ALL ' +
CHAR(13) + CHAR(10) + 'go'
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1 and parent_obj in
(select id from sysobjects where xtype = 'U')
go
-----------------------------------------------------------
-- Generate Add Enable.sql
SET NOCOUNT ON
print 'USE ' + DB_NAME()
print ''
-- Generate Adds for All Foreign Keys in Database
print '-- Add Foreign Keys'
print ''
DECLARE @fkName varchar(800),@schema VARCHAR(800), @tabName varchar(800), @refName varchar(800)
DECLARE @isDel int, @isUpd int, @fkCol varchar(8000), @refCol varchar(8000)
DECLARE @pline varchar(8000)
DECLARE fkCursor CURSOR FOR
SELECT
SCHEMA_NAME(CAST(OBJECTPROPERTYEX(tbl.id , 'SchemaId') AS INT)),
sysobjects.name,
tbl.name,
ltbl.name,
OBJECTPROPERTY ( constid , 'CnstIsDeleteCascade' ),
OBJECTPROPERTY ( constid , 'CnstIsUpdateCascade' )
FROM
sysforeignkeys
JOIN sysobjects ON sysobjects.id = constid
JOIN sysobjects tbl ON tbl.id = fkeyid
JOIN syscolumns col ON col.id = fkeyid AND col.colid = fkey
JOIN sysobjects ltbl ON ltbl.id = rkeyid
JOIN syscolumns lcol ON lcol.id = rkeyid AND lcol.colid = rkey
ORDER BY sysobjects.name
OPEN fkCursor
FETCH NEXT FROM fkCursor
INTO @schema, @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 [' + @schema + '].[' + @tabName + '] ADD CONSTRAINT [' + @fkName + ']' +
CHAR(13) + CHAR(10) + ' FOREIGN KEY (' + @fkCol + ') REFERENCES [' + @schema + '].[' + @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) + 'go'
print @pline
FETCH NEXT FROM fkCursor
INTO @schema, @fkName, @tabName, @refName, @isDel, @isUpd
END
CLOSE fkCursor
DEALLOCATE fkCursor
GO
-- Generate Enable all triggers
print ''
print '-- Enable Triggers'
print ''
select distinct 'ALTER TABLE [dbo].[' + OBJECT_NAME(parent_obj) +
'] ENABLE TRIGGER ALL ' +
CHAR(13) + CHAR(10) + 'go'
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsTrigger') = 1 and parent_obj in
(select id from sysobjects where xtype = 'U')
go
Tags: