Drop all indexes

I have been assigned to do some heavy duty index re-engineering on a
database.
EXECing a few sp_HelpIndex calls demonstrates that there are gobbles of
useless, unused, haphazard and redundant indexes all sort of kludged
together and don't even have consistent names.
I am allowed to have full control over this reindexing chore, so rather than
manually going through all the tedium, I'd like to be able to drop all
indexes on a table, and create fresh ones, rather than all this "if
exists...drop...create"
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:ud2EORuYFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I have been assigned to do some heavy duty index re-engineering on a
> database.
> EXECing a few sp_HelpIndex calls demonstrates that there are gobbles of
> useless, unused, haphazard and redundant indexes all sort of kludged
> together and don't even have consistent names.
> I am allowed to have full control over this reindexing chore, so rather
than
> manually going through all the tedium, I'd like to be able to drop all
> indexes on a table, and create fresh ones, rather than all this "if
> exists...drop...create"
These are not the fastest or best laid out scripts, but they do the job.
Rick Sawtell
MCT, MCSD, MCDBA
PRINT '**************************************'
PRINT '* Dropping Foreign Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.FKName nvarchar(256)
DECLARE @.FKNames TABLE(FKName nvarchar(256))
DECLARE @.FKCount int
INSERT @.TableNames
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.FKName = NULL
INSERT @.FKNames (FKName)
SELECT name
FROM sysobjects
WHERE TYPE = 'F'
AND parent_obj = OBJECT_ID(@.TableName)
AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
SELECT @.FKCount = COUNT(*)
FROM @.FKNames
WHILE @.FKCount > 0
BEGIN
SELECT @.FKName = MIN(FKName)
FROM @.FKNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
DELETE @.FKNames
WHERE FKName = @.FKName
SET @.FKCount = @.FKCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END
SET NOCOUNT OFF
GO
/******************************
DROP PKeys
******************************/
PRINT ''
PRINT ''
PRINT ''
PRINT '**************************************'
PRINT '* Dropping Primary Key Constraints *'
PRINT '**************************************'
SET NOCOUNT ON
DECLARE @.TableNames TABLE(TableName nvarchar(256))
DECLARE @.TableCount int
DECLARE @.TableName nvarchar(256),
@.PKName nvarchar(256)
DECLARE @.KeyNames TABLE(KeyName nvarchar(256))
DECLARE @.KeyCount int
INSERT @.TableNames (TableName)
SELECT name
FROM sysobjects
WHERE TYPE = 'U'
AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
ORDER BY name
SELECT @.TableCount = Count(*) FROM @.TableNames
WHILE @.TableCount > 0
BEGIN
SELECT @.TableName = MIN(TableName)
FROM @.TableNames
SET @.PKName = NULL
INSERT @.KeyNames(KeyName)
SELECT name
FROM sysobjects
WHERE parent_obj = OBJECT_ID(@.TableName)
AND (OBJECTPROPERTY(OBJECT_ID(name), 'IsPrimaryKey') = 1
OR OBJECTPROPERTY(OBJECT_ID(name), 'IsUniqueCnst') = 1)
SELECT @.KeyCount = COUNT(*)
FROM @.KeyNames
WHILE @.KeyCount > 0
BEGIN
SELECT @.PKName = MIN(KeyName)
FROM @.KeyNames
PRINT ' Dropping Constraint ' + @.TableName + '.' + @.PKName
EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.PKName)
DELETE @.KeyNames
WHERE KeyName = @.PKName
SET @.KeyCount = @.KeyCount - 1
END
DELETE FROM @.TableNames WHERE TableName = @.TableName
SET @.TableCount = @.TableCount - 1
END
SET NOCOUNT OFF
GO
/******************************
DROP Indexes
******************************/
PRINT ''
PRINT ''
PRINT ''
PRINT '**********************'
PRINT '* Dropping Indexes *'
PRINT '**********************'
SET NOCOUNT ON
DECLARE @.IndexNames TABLE(IndexName nvarchar(256))
DECLARE @.IndexCount int
DECLARE @.IndexName nvarchar(256)
INSERT @.IndexNames (IndexName)
SELECT o.name + '.' + i.name AS IndexName
FROM sysindexes i, sysobjects o
WHERE i.indid > 0
AND i.indid < 255
AND (i.status & 64) = 0
AND i.id = o.id
AND o.type = 'U'
AND OBJECTPROPERTY(o.id, 'IsSystemTable') =0
AND OBJECTPROPERTY(o.id, 'IsConstraint') = 0
SELECT @.IndexCount = Count(*) FROM @.IndexNames
WHILE @.IndexCount > 0
BEGIN
SET @.IndexName = NULL
SELECT @.IndexName = MIN(IndexName)
FROM @.IndexNames
IF @.IndexName <> 'NxT_Version.PK_NxT_Version'
BEGIN
EXECUTE('DROP INDEX ' + @.IndexName)
PRINT ' Dropping Index ' + @.IndexName
END
DELETE FROM @.IndexNames WHERE IndexName = @.IndexName
SET @.IndexCount = @.IndexCount - 1
END
SET NOCOUNT OFF
GO|||AWESOME! Thanks man, this looks like a keeper!
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||> IF @.IndexName <> 'NxT_Version.PK_NxT_Version'
In that third script that drops indexes, what is this?
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23ogJWXuYFHA.2944@.TK2MSFTNGP14.phx.gbl...
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:ud2EORuYFHA.3864@.TK2MSFTNGP10.phx.gbl...
> than
> These are not the fastest or best laid out scripts, but they do the job.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
> PRINT '**************************************'
> PRINT '* Dropping Foreign Key Constraints *'
> PRINT '**************************************'
> SET NOCOUNT ON
> DECLARE @.TableNames TABLE(TableName nvarchar(256))
> DECLARE @.TableCount int
> DECLARE @.TableName nvarchar(256),
> @.FKName nvarchar(256)
> DECLARE @.FKNames TABLE(FKName nvarchar(256))
> DECLARE @.FKCount int
> INSERT @.TableNames
> SELECT name
> FROM sysobjects
> WHERE TYPE = 'U'
> AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
> AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
> ORDER BY name
>
> SELECT @.TableCount = Count(*) FROM @.TableNames
> WHILE @.TableCount > 0
> BEGIN
> SELECT @.TableName = MIN(TableName)
> FROM @.TableNames
> SET @.FKName = NULL
> INSERT @.FKNames (FKName)
> SELECT name
> FROM sysobjects
> WHERE TYPE = 'F'
> AND parent_obj = OBJECT_ID(@.TableName)
> AND OBJECTPROPERTY(OBJECT_ID(name), 'IsForeignKey') = 1
>
> SELECT @.FKCount = COUNT(*)
> FROM @.FKNames
> WHILE @.FKCount > 0
> BEGIN
> SELECT @.FKName = MIN(FKName)
> FROM @.FKNames
> PRINT ' Dropping Constraint ' + @.TableName + '.' + @.FKName
> EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' + @.FKName)
> DELETE @.FKNames
> WHERE FKName = @.FKName
> SET @.FKCount = @.FKCount - 1
> END
> DELETE FROM @.TableNames WHERE TableName = @.TableName
> SET @.TableCount = @.TableCount - 1
> END
> SET NOCOUNT OFF
> GO
>
> /******************************
> DROP PKeys
> ******************************/
> PRINT ''
> PRINT ''
> PRINT ''
> PRINT '**************************************'
> PRINT '* Dropping Primary Key Constraints *'
> PRINT '**************************************'
> SET NOCOUNT ON
> DECLARE @.TableNames TABLE(TableName nvarchar(256))
> DECLARE @.TableCount int
> DECLARE @.TableName nvarchar(256),
> @.PKName nvarchar(256)
> DECLARE @.KeyNames TABLE(KeyName nvarchar(256))
> DECLARE @.KeyCount int
> INSERT @.TableNames (TableName)
> SELECT name
> FROM sysobjects
> WHERE TYPE = 'U'
> AND OBJECTPROPERTY(object_id(name), 'IsTable') = 1
> AND OBJECTPROPERTY(object_id(name), 'IsSystemTable') = 0
> ORDER BY name
>
>
> SELECT @.TableCount = Count(*) FROM @.TableNames
> WHILE @.TableCount > 0
> BEGIN
> SELECT @.TableName = MIN(TableName)
> FROM @.TableNames
> SET @.PKName = NULL
> INSERT @.KeyNames(KeyName)
> SELECT name
> FROM sysobjects
> WHERE parent_obj = OBJECT_ID(@.TableName)
> AND (OBJECTPROPERTY(OBJECT_ID(name), 'IsPrimaryKey') = 1
> OR OBJECTPROPERTY(OBJECT_ID(name), 'IsUniqueCnst') = 1)
> SELECT @.KeyCount = COUNT(*)
> FROM @.KeyNames
> WHILE @.KeyCount > 0
> BEGIN
> SELECT @.PKName = MIN(KeyName)
> FROM @.KeyNames
> PRINT ' Dropping Constraint ' + @.TableName + '.' + @.PKName
> EXECUTE('ALTER TABLE ' + @.TableName + ' DROP CONSTRAINT ' +
> @.PKName)
> DELETE @.KeyNames
> WHERE KeyName = @.PKName
> SET @.KeyCount = @.KeyCount - 1
> END
> DELETE FROM @.TableNames WHERE TableName = @.TableName
> SET @.TableCount = @.TableCount - 1
> END
> SET NOCOUNT OFF
> GO
>
> /******************************
> DROP Indexes
> ******************************/
> PRINT ''
> PRINT ''
> PRINT ''
> PRINT '**********************'
> PRINT '* Dropping Indexes *'
> PRINT '**********************'
> SET NOCOUNT ON
> DECLARE @.IndexNames TABLE(IndexName nvarchar(256))
> DECLARE @.IndexCount int
> DECLARE @.IndexName nvarchar(256)
>
> INSERT @.IndexNames (IndexName)
> SELECT o.name + '.' + i.name AS IndexName
> FROM sysindexes i, sysobjects o
> WHERE i.indid > 0
> AND i.indid < 255
> AND (i.status & 64) = 0
> AND i.id = o.id
> AND o.type = 'U'
> AND OBJECTPROPERTY(o.id, 'IsSystemTable') =0
> AND OBJECTPROPERTY(o.id, 'IsConstraint') = 0
>
> SELECT @.IndexCount = Count(*) FROM @.IndexNames
> WHILE @.IndexCount > 0
> BEGIN
> SET @.IndexName = NULL
> SELECT @.IndexName = MIN(IndexName)
> FROM @.IndexNames
> IF @.IndexName <> 'NxT_Version.PK_NxT_Version'
> BEGIN
> EXECUTE('DROP INDEX ' + @.IndexName)
> PRINT ' Dropping Index ' + @.IndexName
> END
>
> DELETE FROM @.IndexNames WHERE IndexName = @.IndexName
> SET @.IndexCount = @.IndexCount - 1
> END
> SET NOCOUNT OFF
> GO
>
>|||Correction,

> About deleting indexes, you can not delete a index being used by a pk
> constraint. You have to drop the constraint first. So if there are fk
You have to drop the constraint instead.
AMB
"Alejandro Mesa" wrote:
> Mike,
> Before continuing, take a look to this article. Using sp_helpindex is not
> enough to determine if a index is util or not.
> How to Identify Non-Active SQL Server Indexes
> http://support.microsoft.com/newsgroups/default.aspx
> About deleting indexes, you can not delete a index being used by a pk
> constraint. You have to drop the constraint first. So if there are fk
> constraints referencing this pk, you will have to drop them first and them
> drop the pk one.
> Example:
> use northwind
> go
> create table t1 (
> c1 int not null identity primary key nonclustered
> )
> go
> create table t2 (
> c1 int not null references t1(c1)
> )
> go
> declare @.in sysname
> select @.in = [name] from sysindexes
> where object_name([id]) = 't1' and indid > 1
> exec('drop index t1.' + @.in)
> go
> drop table t2, t1
> go
> Result:
> Server: Msg 3723, Level 16, State 4, Line 1
> An explicit DROP INDEX is not allowed on index 't1.PK__t1__36545CEC'. It i
s
> being used for PRIMARY KEY constraint enforcement.
> One more thing, create a script of current indexes. You never know if you
> will need this script.
>
> AMB
> "Mike Labosh" wrote:
>|||Mike,
Before continuing, take a look to this article. Using sp_helpindex is not
enough to determine if a index is util or not.
How to Identify Non-Active SQL Server Indexes
http://support.microsoft.com/newsgroups/default.aspx
About deleting indexes, you can not delete a index being used by a pk
constraint. You have to drop the constraint first. So if there are fk
constraints referencing this pk, you will have to drop them first and them
drop the pk one.
Example:
use northwind
go
create table t1 (
c1 int not null identity primary key nonclustered
)
go
create table t2 (
c1 int not null references t1(c1)
)
go
declare @.in sysname
select @.in = [name] from sysindexes
where object_name([id]) = 't1' and indid > 1
exec('drop index t1.' + @.in)
go
drop table t2, t1
go
Result:
Server: Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 't1.PK__t1__36545CEC'. It is
being used for PRIMARY KEY constraint enforcement.
One more thing, create a script of current indexes. You never know if you
will need this script.
AMB
"Mike Labosh" wrote:

> I have been assigned to do some heavy duty index re-engineering on a
> database.
> EXECing a few sp_HelpIndex calls demonstrates that there are gobbles of
> useless, unused, haphazard and redundant indexes all sort of kludged
> together and don't even have consistent names.
> I am allowed to have full control over this reindexing chore, so rather th
an
> manually going through all the tedium, I'd like to be able to drop all
> indexes on a table, and create fresh ones, rather than all this "if
> exists...drop...create"
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>