Blog di Zanirato Luca

DROP ForeignKey con popolazione di Codice SQL

Questo script, a me molto utile, permette d'estrapolare

tutte le ForeignKey del mio Database e di popolare in un colpo solo

tutte le istruzioni DROP che riguardano queste ForeignKey :


USE [NomeDatabase]

SELECT cast(f.name AS Varchar(255))  AS Nome_ForeignKey

, r.keycnt                    

, cast(c.name AS Varchar(255)) AS Foreign_Table

, cast(fc.name AS Varchar(255)) AS Foreign_Column_1

, cast(fc2.name AS Varchar(255)) AS Foreign_Column_2

, cast(p.name AS Varchar(255)) AS Primary_Table

, cast(rc.name AS Varchar(255)) AS Primary_Column_1

, cast(rc2.name AS Varchar(255)) AS Primary_Column_2

FROM sysobjects f

INNER JOIN sysobjects c On f.parent_obj = c.id

INNER JOIN sysreferences r On f.id = r.constid

INNER JOIN sysobjects p On r.rkeyid = p.id

INNER JOIN syscolumns rc On r.rkeyid = rc.id And r.rkey1 = rc.colid

INNER JOIN syscolumns fc On r.fkeyid = fc.id And r.fkey1 = fc.colid

LEFT JOIN syscolumns rc2 On r.rkeyid = rc2.id And r.rkey2 = rc.colid                    

LEFT JOIN syscolumns fc2 On r.fkeyid = fc2.id And r.fkey2 = fc.colid

WHERE f.type = 'F'

SELECT

'if exists (select * from dbo.sysobjects where id =

object_id(N''[dbo].['+ Nome_ForeignKey + ']'')

ALTER TABLE [dbo].[' + Foreign_Table + '] DROP CONSTRAINT ' + Nome_ForeignKey

FROM ForeignKey ORDER BY Foreign_Table

Buona Giornata!