Dropping relationships

Is there an easy way to drop all of the relationships in a database in one
swoop?
Thanks.
Archeryou can use the following query to create the needed 'alter table' script:
select 'alter table '+ctu.table_name+' drop constraint
'+rc.constraint_name+char(13)
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE ctu on
rc.constraint_name=ctu.constraint_name
dean
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Try,
declare @.sql nvarchar(4000)
declare @.ts sysname
declare @.tn sysname
declare @.cn sysname
declare fk_constraints_cursor cursor local fast_forward
for
select
table_schema,
table_name,
constraint_name
from
information_schema.CONSTRAINT_TABLE_USAGE
where
objectproperty(object_id(quotename(const
raint_schema) + '.' +
quotename(constraint_name)), 'IsForeignKey') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
open fk_constraints_cursor
while 1 = 1
begin
fetch next from fk_constraints_cursor into @.ts, @.tn, @.cn
if @.@.error != 0 or @.@.fetch_status != 0 break
set @.sql = N'alter table ' + quotename(@.ts) + '.' + quotename(@.tn) + N'
drop constraint '+ quotename(@.cn)
print @.sql
exec sp_executesql @.sql
end
close fk_constraints_cursor
deallocate fk_constraints_cursor
go
AMB
"bagman3rd" wrote:

> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer|||Here is my proc that I use. It gives you a lot of flexibility (use at your
own risk, obviously)
if object_id('utility$removeRelationships')
is not null
begin
drop procedure utility$removeRelationships
end
go
create procedure utility$removeRelationships
(
@.table_schema sysname = 'dbo', --does not do a like comparison
@.parent_table_name sysname = '%', --it is the parrent when it is being
referred to
@.child_table_name sysname = '%', --it is the child table when it is the
table referring
--to another
@.constraint_name sysname = '%', --can be used to drop only a single
constraint
@.display_only_fl bit = 1 --displays the statements to execute, but does not
execute them
) as
-- ---
-- Drop all of the foreign key contraints on and or to a table
-- ---
-- Runs on table name in the current database
-- ---
-- 01/11/2005 lbdavi created
-- ---
begin
set nocount on
declare @.statements cursor
set @.statements = cursor for
select 'alter table ' + ctu.table_schema + '.' + ctu.table_name +
' drop constraint ' + cc.constraint_name
from information_schema.referential_constraints as cc
join information_schema.constraint_table_usage as ctu
on cc.constraint_catalog = ctu.constraint_catalog
and cc.constraint_schema = ctu.constraint_schema
and cc.constraint_name = ctu.constraint_name
where ctu.table_schema = @.table_schema
and ctu.table_name like @.child_table_name
and cc.constraint_name like @.constraint_name
and exists (select *
from information_schema.constraint_table_usage ctu2
where cc.unique_constraint_catalog = ctu2.constraint_catalog
and cc.unique_constraint_schema = ctu2.constraint_schema
and cc.unique_constraint_name = ctu2.constraint_name
and ctu2.table_schema = @.table_schema
and ctu2.table_name like @.parent_table_name)
open @.statements
declare @.statement varchar(1000)
fetch from @.statements into @.statement
While @.@.fetch_status = 0
begin
if @.display_only_fl = 1
begin
select @.statement as statementPreview
end
else
begin
exec (@.statement)
end
fetch next from @.statements into @.statement
end
end
go
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"bagman3rd" <bagman3rd@.discussions.microsoft.com> wrote in message
news:8D376833-1AE8-4D37-8D58-1A4EC045CE38@.microsoft.com...
> Is there an easy way to drop all of the relationships in a database in one
> swoop?
> Thanks.
> Archer