I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dkJesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
[vbcol=seagreen]
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>sql
Showing posts with label content. Show all posts
Showing posts with label content. Show all posts
Sunday, March 25, 2012
Delete all content from all tables
I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Jesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
[vbcol=seagreen]
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Jesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk p nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA
|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
[vbcol=seagreen]
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Delete all content from all tables
I have a relational data structure where I need to delete all data from all
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dkJesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>>I have a relational data structure where I need to delete all data
>>from all
>> tables. However, constraints are defined on the tables, so I need to
>> delete from bottom to top to avoid referential constraints.
>> Do you guys have an idea of how to accomplish this?
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>
tables. However, constraints are defined on the tables, so I need to delete
from bottom to top to avoid referential constraints.
Do you guys have an idea of how to accomplish this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dkJesper
Who if not you shloud know the database structure
Take a look at OJ's script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||Jesper,
Why don't you simply create the DDL scripts, drop the database, and create a
fresh one using the scripts? BTW, don't forget to bavkup the db first!
--
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in news:#G4PWDt3FHA.1396@.TK2MSFTNGP12.phx.gbl:
> Jesper,
> Why don't you simply create the DDL scripts, drop the database, and
> create a fresh one using the scripts? BTW, don't forget to bavkup the
> db first!
The reason is that I don't really need to delete all data in the tables -
just the data related to a specific id - that is used in all tables.
The id is a job id and this id is a part of all tables relating to this
job.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk|||"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>I have a relational data structure where I need to delete all data from all
> tables. However, constraints are defined on the tables, so I need to
> delete
> from bottom to top to avoid referential constraints.
> Do you guys have an idea of how to accomplish this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> Findes din kiosk på nettet? Se http://ekiosk.dk
How about setting up your FK's as FK's ON DELETE CASCADE?
Would this not solve your problem?
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in
news:ukG0Bju3FHA.3952@.TK2MSFTNGP10.phx.gbl:
> "Jesper Stocholm" <j@.stocholm.invalid> wrote in message
> news:Xns970158D95CA57stocholmdk@.207.46.248.16...
>>I have a relational data structure where I need to delete all data
>>from all
>> tables. However, constraints are defined on the tables, so I need to
>> delete from bottom to top to avoid referential constraints.
>> Do you guys have an idea of how to accomplish this?
> How about setting up your FK's as FK's ON DELETE CASCADE?
> Would this not solve your problem?
It would - if I needed to delete all data for a specific job. But this is
not what I need - I "just" need to delete the content in a number for
tables for a specific job. I do not want to delete the job entirely.
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>
Subscribe to:
Posts (Atom)