Delete all Indexes

I have several databases that are supposed to have the same structure, over
time some things (mostly indexes) have been changed, added, etc. differently
on each database. I would like to write a script that would strip all user
tables of their indexes and then run a script that would add the indexes I
want on them. I have no problem writing the script to create the indexes.
What I need is a script that will delete read the system table and delete
all of the indexes in a particular database (including primary keys).
If anybody has such a thing and wouldn't mind sharing it, I would be most
grateful.Here is the actual error:
An explicit DROP INDEX is not allowed on index
'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
constraint enforcement.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> John,
> Execute the output of the below query .
> SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 250
> AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
>
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > I have several databases that are supposed to have the same structure,
> over
> > time some things (mostly indexes) have been changed, added, etc.
> differently
> > on each database. I would like to write a script that would strip all
> user
> > tables of their indexes and then run a script that would add the indexes
I
> > want on them. I have no problem writing the script to create the
indexes.
> > What I need is a script that will delete read the system table and
delete
> > all of the indexes in a particular database (including primary keys).
> >
> > If anybody has such a thing and wouldn't mind sharing it, I would be
most
> > grateful.
> >
> >
>|||You need to use DROP CONSTRAINT on constraints.
--
Andrew J. Kelly
SQL Server MVP
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> Here is the actual error:
> An explicit DROP INDEX is not allowed on index
> 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> constraint enforcement.
> "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > John,
> >
> > Execute the output of the below query .
> >
> > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 250
> > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> >
> >
> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > I have several databases that are supposed to have the same structure,
> > over
> > > time some things (mostly indexes) have been changed, added, etc.
> > differently
> > > on each database. I would like to write a script that would strip all
> > user
> > > tables of their indexes and then run a script that would add the
indexes
> I
> > > want on them. I have no problem writing the script to create the
> indexes.
> > > What I need is a script that will delete read the system table and
> delete
> > > all of the indexes in a particular database (including primary keys).
> > >
> > > If anybody has such a thing and wouldn't mind sharing it, I would be
> most
> > > grateful.
> > >
> > >
> >
> >
>|||What is the best way to seperate which are regular indexes and which are
primary keys?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> You need to use DROP CONSTRAINT on constraints.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > Here is the actual error:
> >
> > An explicit DROP INDEX is not allowed on index
> > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > constraint enforcement.
> >
> > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > John,
> > >
> > > Execute the output of the below query .
> > >
> > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' + [name]+CHAR(13)+'GO'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 250
> > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > >
> > >
> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com
> > >
> > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > I have several databases that are supposed to have the same
structure,
> > > over
> > > > time some things (mostly indexes) have been changed, added, etc.
> > > differently
> > > > on each database. I would like to write a script that would strip
all
> > > user
> > > > tables of their indexes and then run a script that would add the
> indexes
> > I
> > > > want on them. I have no problem writing the script to create the
> > indexes.
> > > > What I need is a script that will delete read the system table and
> > delete
> > > > all of the indexes in a particular database (including primary
keys).
> > > >
> > > > If anybody has such a thing and wouldn't mind sharing it, I would be
> > most
> > > > grateful.
> > > >
> > > >
> > >
> > >
> >
> >
>|||You can loop through the sysconstraints table first to remove all the
constraints and then do the indexes.
--
Andrew J. Kelly
SQL Server MVP
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:e6gR0AITDHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the best way to seperate which are regular indexes and which are
> primary keys?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > You need to use DROP CONSTRAINT on constraints.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > > Here is the actual error:
> > >
> > > An explicit DROP INDEX is not allowed on index
> > > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > > constraint enforcement.
> > >
> > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > > John,
> > > >
> > > > Execute the output of the below query .
> > > >
> > > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' +
[name]+CHAR(13)+'GO'
> > > > FROM sysindexes
> > > > WHERE indid BETWEEN 1 AND 250
> > > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > > >
> > > >
> > > > --
> > > > Dinesh.
> > > > SQL Server FAQ at
> > > > http://www.tkdinesh.com
> > > >
> > > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > > I have several databases that are supposed to have the same
> structure,
> > > > over
> > > > > time some things (mostly indexes) have been changed, added, etc.
> > > > differently
> > > > > on each database. I would like to write a script that would strip
> all
> > > > user
> > > > > tables of their indexes and then run a script that would add the
> > indexes
> > > I
> > > > > want on them. I have no problem writing the script to create the
> > > indexes.
> > > > > What I need is a script that will delete read the system table and
> > > delete
> > > > > all of the indexes in a particular database (including primary
> keys).
> > > > >
> > > > > If anybody has such a thing and wouldn't mind sharing it, I would
be
> > > most
> > > > > grateful.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks...that did it.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uL1KjTITDHA.3144@.tk2msftngp13.phx.gbl...
> You can loop through the sysconstraints table first to remove all the
> constraints and then do the indexes.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:e6gR0AITDHA.2180@.TK2MSFTNGP10.phx.gbl...
> > What is the best way to seperate which are regular indexes and which are
> > primary keys?
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e1E7LwHTDHA.2148@.TK2MSFTNGP12.phx.gbl...
> > > You need to use DROP CONSTRAINT on constraints.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > news:ukge0sHTDHA.2020@.TK2MSFTNGP11.phx.gbl...
> > > > Here is the actual error:
> > > >
> > > > An explicit DROP INDEX is not allowed on index
> > > > 'tblTableName.PrimaryKeyName_PK'. It is being used for PRIMARY KEY
> > > > constraint enforcement.
> > > >
> > > > "Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
> > > > news:OY7JRpGTDHA.1912@.tk2msftngp13.phx.gbl...
> > > > > John,
> > > > >
> > > > > Execute the output of the below query .
> > > > >
> > > > > SELECT 'DROP INDEX ' + OBJECT_NAME([id]) + '.' +
> [name]+CHAR(13)+'GO'
> > > > > FROM sysindexes
> > > > > WHERE indid BETWEEN 1 AND 250
> > > > > AND OBJECTPROPERTY([id], 'IsMsShipped') = 0
> > > > > AND INDEXPROPERTY([id], [name], 'IsStatistics') = 0
> > > > >
> > > > >
> > > > > --
> > > > > Dinesh.
> > > > > SQL Server FAQ at
> > > > > http://www.tkdinesh.com
> > > > >
> > > > > "John Hamilton" <jhamil@.nowhere.com> wrote in message
> > > > > news:uFM7uiGTDHA.2852@.tk2msftngp13.phx.gbl...
> > > > > > I have several databases that are supposed to have the same
> > structure,
> > > > > over
> > > > > > time some things (mostly indexes) have been changed, added, etc.
> > > > > differently
> > > > > > on each database. I would like to write a script that would
strip
> > all
> > > > > user
> > > > > > tables of their indexes and then run a script that would add the
> > > indexes
> > > > I
> > > > > > want on them. I have no problem writing the script to create
the
> > > > indexes.
> > > > > > What I need is a script that will delete read the system table
and
> > > > delete
> > > > > > all of the indexes in a particular database (including primary
> > keys).
> > > > > >
> > > > > > If anybody has such a thing and wouldn't mind sharing it, I
would
> be
> > > > most
> > > > > > grateful.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

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...
[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

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>

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>