July 27, 2007 at 6:50 am
How can i check if my database has any circular references/relationships and how to get rid of them.
July 27, 2007 at 11:14 am
All scripts were copied from SQL Central Scripts section.
This script will show you all the foreign keys in your database.
select so1.Name as FKConstraint, so.Name as FromTable,
sc.Name as FromColumn, so2.Name as ToTable, sc1.Name as ToColumn
From sysforeignkeys fk (nolock)
JOIN sysobjects so (nolock) on so.[id] = fk.fkeyid
JOIN sysobjects so1 (nolock) on fk.constid = so1.id
join syscolumns sc (nolock) on fk.fkeyid = sc.id and fk.fkey = sc.colid
JOIN sysobjects so2 (nolock) on fk.rkeyid = so2.id
join syscolumns sc1 (nolock) on fk.rkeyid = sc1.id and fk.rkey = sc1.colid
You can use this script to get rid of all foreign keys in your database.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure Drop_Foreign_Keys
AS
/* Create a variable to hold dynamic SQL statement */
declare @Select_SQL as nvarchar(500)
/* Drop Foreign Key table if exists - this is temporary */
if exists (select * from sysobjects where id = object_id('Foreign_Key_Table') and sysstat & 0xf = 3)
drop table Foreign_Key_Table
/* Get foreign key info for one table */
select top 1
object_name(fkeyid) as foreign_key_table,
object_name(rkeyid) as primary_key_table,
object_name(constid) as [name]
Into Foreign_Key_Table
from sysforeignkeys
where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1
/* Drop foreign key found and get details on next until all have been dropped */
While (select count(*) from foreign_key_table) > 0
begin
set @Select_SQL = 'alter table '
+ (select foreign_key_table + ' drop ' + name from Foreign_Key_Table)
exec sp_executesql @Select_SQL
truncate table Foreign_Key_Table
Insert into Foreign_Key_Table
select top 1
object_name(fkeyid) as foreign_key_table,
object_name(rkeyid) as primary_key_table,
object_name(constid) as [name]
from sysforeignkeys
where OBJECTPROPERTY(rkeyid, N'IsUserTable') = 1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply