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