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

Dropping all Data but keeping constraints and Structure Intact!

Hi All -
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1On Apr 11, 7:20 pm, "wiseteufel via SQLMonster.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .

Dropping all Data but keeping constraints and Structure Intact!

Hi All -
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200704/1On Apr 11, 7:20 pm, "wiseteufel via droptable.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, P
K-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via droptable.comhttp://www.droptable.com/Uwe/Forums.aspx/sql-ser
ver/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .

Dropping all Data but keeping constraints and Structure Intact!

Hi All -
I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
FK, and other relationships on them. I would like to drop all data in all
tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
relationships cascading, and indexes and such. I want to keep intact all
that is not data. So basically truncate or delete all data and leave all
table structures with all their constraints etc ..
I wonder if such a script exists? that would be a great help.
Thank You In Advance.
Wise -
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200704/1
On Apr 11, 7:20 pm, "wiseteufel via droptable.com" <u4067@.uwe> wrote:
> Hi All -
> I have a SQL 2000 database of 60 some tables, with constraints, indexes, PK-
> FK, and other relationships on them. I would like to drop all data in all
> tables, knowing that a lot of the tables have full PK-FK-PK-FK ...
> relationships cascading, and indexes and such. I want to keep intact all
> that is not data. So basically truncate or delete all data and leave all
> table structures with all their constraints etc ..
> I wonder if such a script exists? that would be a great help.
> Thank You In Advance.
> Wise -
> --
> Message posted via droptable.comhttp://www.droptable.com/Uwe/Forums.aspx/sql-server/200704/1
You can script out the entire database ( with PK,FK, INDEX ,
Constraint options) and create a new database from the script .

Drop Relationships

Hi there,

I would Like to delete a relationship by SQL Syntax!

When I go to M-Access I see the relationship with the name companiesVisitors
and this is how it is represented...

Companies(Company_ID) -- Visitors(VisitorCompany)

one-to-many

How can I drop the relationship using SQL?

Hope you can help me... I am searching a solution for daysyou might need to know the name of the constraint

see ACC2000: Create and Drop Tables and Relationships Using SQL DDL (http://support.microsoft.com/kb/q209037/)

but if you're in the relationship window already, just click on the line joining the two boxes and delete it|||That didn't help to much... I need the SQL to remove a relationship...

I tried to delete the constraints, but that does not help me to much... because my Constraints have strange names such as "Rel_158FB2CC_919D_462B"... It's a totally random name given by Access|||then when you create the relationship, you should assign it a constraint name|||but I can't do anything now, because is a software update... it's to import data to new database formats... I can't guess wich names the constraint have... it should be done in the beggining!