This script will drop and recreated all indexes in a database within a transaction. The script will also add standard nameing conventions to the index names. Any duplicated indexes are removed.
/**********************************************************************************************
Author: Andrew J. Esser *
Create Date: April-25-2006 *
***********************************************************************************************
Update Date: April-28-2006 - A.J.E. *
Fixed error of duplicated indexes being created. Problem occured when two tables with the *
same name but different owners were present. Filtered base table name selection to only *
reindex tables that are owned by 'dbo'. *
*
Fixed issue of duplicated index definitions not being dropped corectly. *
***********************************************************************************************
Purpose: *
1). Drop and recreate all indexes in the current database which are user-defined. *
2). Standardize nameing conventions for index creation. *
3.) Remove duplicated index entries. *
***********************************************************************************************
For @tbl table variable, the TypeOf char 2 field values mean the following: *
***********************************************************************************************
1). 'FK' = Foriegn Key *
2). 'DF' = Default Constraint *
3). 'IX' = Index - Non-unique *
4). 'IU' = Index - Unique *
5). 'CL' = Clustered Index - Non-unique *
6). 'CU' = Clustered Index - Unique *
7). 'CX' = Constraint Index - Non-clustered *
8). 'CC' = Constraint Index - Clustered *
9). 'PK' = Primary Key - Non-clustered *
10). 'PC' = Primary Key - Clustered *
***********************************************************************************************
To drop all database indexes properly, the following order must be used for success. *
***********************************************************************************************
1). Drop Foriegn Keys *
2). Drop Default Constraints *
3). Drop Indexes - Non-unique *
4). Drop Indexes - Unique *
5). Drop Clustered Indexes - Non-unique *
6). Drop Clustered Indexes - Unique *
7). Drop Index Constraints - Non-clustered *
8). Drop Index Constraints - Clustered *
9). Drop Primary Keys - Non-clustered *
10). Drop Primary Keys - Clustered *
***********************************************************************************************
To re-create database indexes properly, the following order must be used for success. *
***********************************************************************************************
1). Create Primary Keys - Clustered *
2). Create Primary Keys - Non-clustered *
3). Create Constraint Indexes - Clustered *
4). Create Constraint Indexes - Non-clustered *
5). Create Clustered Indexes - Unique *
6). Create Clustered Indexes - Non-unique *
7). Create Indexes - Unique *
8). Create Indexes - Non-unique *
9). Create Default Constraints *
10). Create Foriegn Keys *
**********************************************************************************************/
-- The first action is to get the foreign key references
-- on each table to build the drop and create scripts.
DECLARE @pkTABLE_name sysname
, @fkTABLE_name sysname
, @fkey sysname
, @fkey_orig sysname
, @valI varchar(3000)
, @valD varchar(3000)
, @fld_pk varchar(1000)
, @fld_fk varchar(1000)
, @pk_tbl varchar(100)
, @pkfull_TABLE_name nvarchar(257) /* 2*128 + 1 */ , @fkfull_TABLE_name nvarchar(257) /* 2*128 + 1 */ , @cnstdes nvarchar(4000) -- string to build default index value.
, @pkTABLE_id int
, @cnt int
, @cnt_of int
, @min_id int
, @tbl_cnt int
, @tbl_idx int
, @fkTABLE_id int
, @order_by_pk int
, @i int
SET NOCOUNT ON
-- Virtual table pointer to contain all the tables in the current database.
DECLARE @tbl_all TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, tbl_name sysname NOT NULL
)
-- Virtual table pointer to contain raw foreign key column references.
DECLARE @fkeysall TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, rkeyid int NOT NULL
, rkey1 int NOT NULL
, rkey2 int NOT NULL
, rkey3 int NOT NULL
, rkey4 int NOT NULL
, rkey5 int NOT NULL
, rkey6 int NOT NULL
, rkey7 int NOT NULL
, rkey8 int NOT NULL
, rkey9 int NOT NULL
, rkey10 int NOT NULL
, rkey11 int NOT NULL
, rkey12 int NOT NULL
, rkey13 int NOT NULL
, rkey14 int NOT NULL
, rkey15 int NOT NULL
, rkey16 int NOT NULL
, fkeyid int NOT NULL
, fkey1 int NOT NULL
, fkey2 int NOT NULL
, fkey3 int NOT NULL
, fkey4 int NOT NULL
, fkey5 int NOT NULL
, fkey6 int NOT NULL
, fkey7 int NOT NULL
, fkey8 int NOT NULL
, fkey9 int NOT NULL
, fkey10 int NOT NULL
, fkey11 int NOT NULL
, fkey12 int NOT NULL
, fkey13 int NOT NULL
, fkey14 int NOT NULL
, fkey15 int NOT NULL
, fkey16 int NOT NULL
, constid int NOT NULL
, name sysname COLLATE database_default NOT NULL
)
-- Virtual table pointer for isolation to one foreign key field per
-- row to build the create and delete foreign key SQL statements.
DECLARE @fkeys TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, pkTABLE_id int NOT NULL
, pkcolid int NOT NULL
, fkTABLE_id int NOT NULL
, fkcolid int NOT NULL
, KEY_SEQ smallint NOT NULL
, fk_id int NOT NULL
, PK_NAME sysname collate database_default NOT NULL
)
DECLARE @fkeysout TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, PKTABLE_QUALIFIER sysname collate database_default NULL
, PKTABLE_OWNER sysname collate database_default NULL
, PKTABLE_NAME sysname collate database_default NOT NULL
, PKCOLUMN_NAME sysname collate database_default NOT NULL
, FKTABLE_QUALIFIER sysname collate database_default NULL
, FKTABLE_OWNER sysname collate database_default NULL
, FKTABLE_NAME sysname collate database_default NOT NULL
, FKCOLUMN_NAME sysname collate database_default NOT NULL
, KEY_SEQ smallint NOT NULL
, UPDATE_RULE smallint NULL
, DELETE_RULE smallint NULL
, FK_NAME sysname collate database_default NULL
, PK_NAME sysname collate database_default NULL
)
DECLARE @parse TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, PKTABLE_QUALIFIER sysname collate database_default NULL
, PKTABLE_OWNER sysname collate database_default NULL
, PKTABLE_NAME sysname collate database_default NOT NULL
, PKCOLUMN_NAME sysname collate database_default NOT NULL
, FKTABLE_QUALIFIER sysname collate database_default NULL
, FKTABLE_OWNER sysname collate database_default NULL
, FKTABLE_NAME sysname collate database_default NOT NULL
, FKCOLUMN_NAME sysname collate database_default NOT NULL
, KEY_SEQ smallint NOT NULL
, UPDATE_RULE smallint NULL
, DELETE_RULE smallint NULL
, FK_NAME sysname collate database_default NULL
, PK_NAME sysname collate database_default NULL
)
DECLARE @tbl TABLE
( CountID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, CreateStatement varchar(3000) NOT NULL
, DropStatement varchar(3000) NOT NULL
, ConstraintName varchar(128) NOT NULL
, OrigConstraint varchar(128) NOT NULL
, TypeOf varchar(2) NOT NULL
)
IF (@table_name = N'all')
-- Insert the list of user table names contained in the current database.
INSERT INTO @tbl_all
( tbl_name
)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'base table')
AND (TABLE_NAME <> 'dtproperties')
AND (LEFT(TABLE_NAME, 4) <> 'Fog_')
AND (LEFT(TABLE_NAME, 5) <> 'Spot_')
AND (TABLE_SCHEMA = 'dbo')
ELSE
INSERT INTO @tbl_all
( tbl_name
)
VALUES ( @table_name
)
SELECT @tbl_cnt = COUNT(*)
FROM @tbl_all
SET @tbl_idx = 1
SET @fkTABLE_name = NULL
WHILE (@tbl_idx <= @tbl_cnt)
BEGIN
SELECT @pkTABLE_name = tbl_name
FROM @tbl_all
WHERE (CountID = @tbl_idx)
-- Starting parameter analysis.
SET @order_by_pk = 0
IF (@pkTABLE_name IS NULL) AND (@fkTABLE_name IS NULL)
BEGIN/* IF neither primary key nor foreign key TABLE names given */ RAISERROR (15252,-1,-1)
RETURN
END
-- Get prime key table Object ID.
IF (@pkTABLE_name IS NOT NULL)
BEGIN
SET @pkfull_TABLE_name = quotename(@pkTABLE_name)
SET @pkTABLE_id = object_id(@pkfull_TABLE_name)
END
-- Get foreign key Object ID
IF (@fkTABLE_name IS NOT NULL)
BEGIN
SET @fkfull_TABLE_name = quotename(@fkTABLE_name)
SET @fkTABLE_id = object_id(@fkfull_TABLE_name)
END
IF (@fkTABLE_name IS NOT NULL)
BEGIN
IF (@fkTABLE_id IS NULL)
SET @fkTABLE_id = 0-- fk TABLE NOT found, empty result.
END
IF (@pkTABLE_name IS NULL)
SET @order_by_pk = 1
ELSE
BEGIN
IF @pkTABLE_id IS NULL
SELECT @pkTABLE_id = 0/* pk TABLE NOT found, empty result */ END
-- SQL Server supports upto 16 PK/FK relationships BETWEEN 2 TABLEs
-- Process syskeys for each relationship
/* First, attempt to get all 16 keys for each rel'ship, then sort
them out with a 16-way "insert SELECT ... union SELECT ..." */-- Starting data analysis.
INSERT INTO @fkeysall
( rkeyid
, rkey1
, rkey2
, rkey3
, rkey4
, rkey5
, rkey6
, rkey7
, rkey8
, rkey9
, rkey10
, rkey11
, rkey12
, rkey13
, rkey14
, rkey15
, rkey16
, fkeyid
, fkey1
, fkey2
, fkey3
, fkey4
, fkey5
, fkey6
, fkey7
, fkey8
, fkey9
, fkey10
, fkey11
, fkey12
, fkey13
, fkey14
, fkey15
, fkey16
, constid
, name
)
SELECT r.rkeyid
, r.rkey1
, r.rkey2
, r.rkey3
, r.rkey4
, r.rkey5
, r.rkey6
, r.rkey7
, r.rkey8
, r.rkey9
, r.rkey10
, r.rkey11
, r.rkey12
, r.rkey13
, r.rkey14
, r.rkey15
, r.rkey16
, r.fkeyid
, r.fkey1
, r.fkey2
, r.fkey3
, r.fkey4
, r.fkey5
, r.fkey6
, r.fkey7
, r.fkey8
, r.fkey9
, r.fkey10
, r.fkey11
, r.fkey12
, r.fkey13
, r.fkey14
, r.fkey15
, r.fkey16
, r.constid
, i.name
FROM sysreferences r
, sysobjects o
, sysindexes i
WHERE r.constid = o.id
AND o.xtype = 'F '
AND r.rkeyindid = i.indid
AND r.rkeyid = i.id
AND r.rkeyid BETWEEN isNULL(@pkTABLE_id, 0)
AND isNULL(@pkTABLE_id, 0x7fffffff)
AND r.fkeyid BETWEEN isNULL(@fkTABLE_id, 0)
AND isNULL(@fkTABLE_id, 0x7fffffff)
SELECT @cnt_of = COUNT(*)
FROM @fkeysall
IF (ISNULL(@cnt_of, 0) = 0)
GOTO No_Foreign_Keys
INSERT INTO @fkeys
( pkTABLE_id
, pkcolid
, fkTABLE_id
, fkcolid
, KEY_SEQ
, fk_id
, PK_NAME
)
SELECT rkeyid, rkey1, fkeyid, fkey1, 1, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey2, fkeyid, fkey2, 2, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey3, fkeyid, fkey3, 3, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey4, fkeyid, fkey4, 4, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey5, fkeyid, fkey5, 5, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey6, fkeyid, fkey6, 6, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey7, fkeyid, fkey7, 7, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey8, fkeyid, fkey8, 8, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey9, fkeyid, fkey9, 9, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey10, fkeyid, fkey10, 10, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey11, fkeyid, fkey11, 11, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey12, fkeyid, fkey12, 12, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey13, fkeyid, fkey13, 13, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey14, fkeyid, fkey14, 14, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey15, fkeyid, fkey15, 15, constid, name
FROM @fkeysall
UNION ALL
SELECT rkeyid, rkey16, fkeyid, fkey16, 16, constid, name
FROM @fkeysall
DELETE
FROM @fkeysall
INSERT INTO @fkeysout
( PKTABLE_QUALIFIER
, PKTABLE_OWNER
, PKTABLE_NAME
, PKCOLUMN_NAME
, FKTABLE_QUALIFIER
, FKTABLE_OWNER
, FKTABLE_NAME
, FKCOLUMN_NAME
, KEY_SEQ
, UPDATE_RULE
, DELETE_RULE
, FK_NAME
, PK_NAME
)
SELECT PKTABLE_QUALIFIER = convert(sysname,db_name())
, PKTABLE_OWNER = convert(sysname,USER_NAME(o1.uid))
, PKTABLE_NAME = convert(sysname,o1.name)
, PKCOLUMN_NAME = convert(sysname,c1.name)
, FKTABLE_QUALIFIER = convert(sysname,db_name())
, FKTABLE_OWNER = convert(sysname,USER_NAME(o2.uid))
, FKTABLE_NAME = convert(sysname,o2.name)
, FKCOLUMN_NAME = convert(sysname,c2.name)
, KEY_SEQ
, UPDATE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsUpdateCascade')=1) THEN
convert(smallint,0)
ELSE
convert(smallint,1)
END
, DELETE_RULE = CASE WHEN (ObjectProperty(fk_id, 'CnstIsDeleteCascade')=1) THEN
convert(smallint,0)
ELSE
convert(smallint,1)
END
, FK_NAME = convert(sysname,OBJECT_NAME(fk_id))
, PK_NAME
FROM @fkeys f, sysobjects o1
, sysobjects o2
, syscolumns c1
, syscolumns c2
WHERE o1.id = f.pkTABLE_id
AND o2.id = f.fkTABLE_id
AND c1.id = f.pkTABLE_id
AND c2.id = f.fkTABLE_id
AND c1.colid = f.pkcolid
AND c2.colid = f.fkcolid
DELETE
FROM @fkeys
SELECT @cnt = COUNT(*)
FROM @fkeysout
WHILE (@cnt > 0)
BEGIN
SELECT @fkey = FK_NAME
FROM @fkeysout
WHERE CountID = (SELECT MIN(CountID) FROM @fkeysout)
INSERT INTO @parse
( PKTABLE_QUALIFIER
, PKTABLE_OWNER
, PKTABLE_NAME
, PKCOLUMN_NAME
, FKTABLE_QUALIFIER
, FKTABLE_OWNER
, FKTABLE_NAME
, FKCOLUMN_NAME
, KEY_SEQ
, UPDATE_RULE
, DELETE_RULE
, FK_NAME
, PK_NAME
)
SELECT PKTABLE_QUALIFIER
, PKTABLE_OWNER
, PKTABLE_NAME
, PKCOLUMN_NAME
, FKTABLE_QUALIFIER
, FKTABLE_OWNER
, FKTABLE_NAME
, FKCOLUMN_NAME
, KEY_SEQ
, UPDATE_RULE
, DELETE_RULE
, FK_NAME
, PK_NAME
FROM @fkeysout
WHERE FK_NAME = @fkey
SELECT @cnt_of = COUNT(*)
, @min_id = MIN(CountID)
FROM @parse
IF (@cnt_of > 0)
SELECT @valI = 'ALTER TABLE ['
+ FKTABLE_OWNER + '].[' + FKTABLE_NAME + ']'
+ ' ADD CONSTRAINT [' + FK_NAME
+ '] FOREIGN KEY ('
, @valD = 'ALTER TABLE ['
+ FKTABLE_OWNER + '].[' + FKTABLE_NAME + ']'
+ ' DROP CONSTRAINT [' + FK_NAME + ']'
, @pk_tbl = '[' + PKTABLE_OWNER + '].[' + PKTABLE_NAME + ']'
, @fkey = FK_NAME
, @fkey_orig = FK_NAME
FROM @parse
WHERE CountID = @min_id
SET @fld_fk = ''
SET @fld_pk = ''
WHILE (ISNULL(@cnt_of, 0) > 0)
BEGIN
IF (@fld_fk = '')
SELECT @fld_fk = '[' + FKCOLUMN_NAME + ']'
, @fld_pk = '[' + PKCOLUMN_NAME + ']'
FROM @parse
WHERE CountID = @min_id
ELSE
SELECT @fld_fk = (@fld_fk + ', [' + FKCOLUMN_NAME + ']')
, @fld_pk = (@fld_pk + ', [' + PKCOLUMN_NAME + ']')
FROM @parse
WHERE CountID = @min_id
DELETE
FROM @parse
WHERE CountID = @min_id
SELECT @cnt_of = COUNT(*)
, @min_id = MIN(CountID)
FROM @parse
END -- of WHILE (@cnt_of > 0)
SELECT @valI = @valI + @fld_fk + ') REFERENCES '
+ @pk_tbl + '('
+ @fld_pk + ')'
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
VALUES ( @valI
, @valD
, @fkey
, @fkey_orig
, 'FK'
)
DELETE
FROM @parse
DELETE
FROM @fkeysout
WHERE FK_NAME = @fkey
SELECT @cnt = COUNT(*)
FROM @fkeysout
END
No_Foreign_Keys:
SET @tbl_idx = @tbl_idx + 1
END -- of WHILE (@tbl_idx <= @tbl_cnt)
-- The next action is to build the drop and create SQL
-- for the default constraints.
DECLARE @tbl_def TABLE
( CountOf int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED
, Parent sysname NOT NULL
, ConstraintName sysname NOT NULL
, NewConstraintName sysname NOT NULL
, ConstraintField sysname NOT NULL
, ConstraintValue varchar(4000) NOT NULL
)
SET @tbl_idx = 1
WHILE (@tbl_idx <= @tbl_cnt)
BEGIN
SELECT @pkTABLE_name = tbl_name
, @pkTABLE_id = object_id(tbl_name)
FROM @tbl_all
WHERE (CountID = @tbl_idx)
INSERT INTO @tbl_def
( Parent
, ConstraintName
, NewConstraintName
, ConstraintField
, ConstraintValue
)
SELECT '[' + convert(sysname,USER_NAME(so.uid)) + '].[' + so.name + ']' -- Parent table name.
, sysobjects.name -- Constraint name.
, 'DF_'
+ REPLACE(so.name, ' ', '_')
+ '_'
+ REPLACE(col_name(@pkTABLE_id, sysobjects.info), ' ', '_') -- New Constraint name.
, col_name(@pkTABLE_id, sysobjects.info) -- Constraint field name.
, syscomments.text -- Constraint value.
FROM sysobjects INNER JOIN syscomments
ON sysobjects.id = syscomments.id
INNER JOIN sysobjects so
ON sysobjects.parent_obj = so.id
WHERE sysobjects.parent_obj = @pkTABLE_id
AND sysobjects.xtype = 'D '
AND sysobjects.uid = user_id(N'dbo')
AND syscomments.colid = 1
SET @tbl_idx = @tbl_idx + 1
END -- of WHILE (@tbl_idx <= @tbl_cnt)
SELECT @cnt = COUNT(*)
FROM @tbl_def
SET @i = 1
IF (@cnt = 0)
GOTO No_Default_Keys
WHILE (@i <= @cnt)
BEGIN
SELECT @valI = ( 'ALTER TABLE '
+ Parent
+ ' ADD CONSTRAINT ['
+ REPLACE(NewConstraintName, '__', '_')
+ '] DEFAULT '
+ ConstraintValue
+ ' FOR [' + ConstraintField + ']'
)
, @valD = ( 'ALTER TABLE '
+ Parent
+ ' DROP CONSTRAINT ['
+ ConstraintName
+ ']'
)
, @fkey = REPLACE(NewConstraintName, '__', '_')
, @fkey_orig = ConstraintName
FROM @tbl_def
WHERE (CountOf = @i)
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
VALUES ( @valI
, @valD
, @fkey
, @fkey_orig
, 'DF'
)
No_Default_Keys:
SET @i = @i + 1
END -- of WHILE (@i <= @cnt)
-- Now create the Drop and Create statements for the regular indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'CREATE NONCLUSTERED INDEX [IX_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] ON ['
+ u.name
+ '].['
+ o.name
+ '](['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'DROP INDEX ['
+ u.name
+ '].['
+ o.name
+ '].['
+ i.name
+ ']'
, 'IX_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'IX'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid < 255)
AND (i.indid > 1)
AND (i.status & 2048) = 0
AND (i.status & 4096) = 0
AND (o.name <> 'dtproperties')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND o.uid = user_id(N'dbo')
AND (i.status & (2 | 4096)) = 0
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the unique regular indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'CREATE UNIQUE NONCLUSTERED INDEX [IU_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] ON ['
+ u.name
+ '].['
+ o.name
+ '](['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'DROP INDEX ['
+ u.name
+ '].['
+ o.name
+ '].['
+ i.name
+ ']'
, 'IU_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'IU'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid < 255)
AND (i.indid > 1)
AND (i.status & 2048) = 0
AND (i.status & 4096) = 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (i.status & (2 | 4096)) > 0
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the non-unique clustered indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'CREATE CLUSTERED INDEX [CL_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] ON ['
+ u.name
+ '].['
+ o.name
+ '](['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'DROP INDEX ['
+ u.name
+ '].['
+ o.name
+ '].['
+ i.name
+ ']'
, 'CL_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'CL'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid < 255)
AND (i.indid = 1)
AND (i.status & 2048) = 0
AND (i.status & 4096) = 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (i.status & (2 | 4096)) = 0
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the unique clustered indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'CREATE UNIQUE CLUSTERED INDEX [CU_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] ON ['
+ u.name
+ '].['
+ o.name
+ '](['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'DROP INDEX ['
+ u.name
+ '].['
+ o.name
+ '].['
+ i.name
+ ']'
, 'CU_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'CU'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid = 1)
AND (i.status & (2048)) = 0
AND (i.status & (4096)) = 0
AND (i.status & (2 | 4096)) > 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the unique non-clustered constraint indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] ADD CONSTRAINT [CX_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] UNIQUE NONCLUSTERED (['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] DROP CONSTRAINT ['
+ i.name
+ ']'
, 'CX_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'CX'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid BETWEEN 2 AND 255)
AND (i.status & 2048) = 0
AND (i.status & 4096) > 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the unique clustered constraint indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] ADD CONSTRAINT [CC_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] UNIQUE CLUSTERED(['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] DROP CONSTRAINT ['
+ i.name
+ ']'
, 'CC_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'CC'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.status & (2 | 4096) > 0)
AND ((i.status & 4096) > 0)
AND ((i.status & 2048) = 0)
AND (i.indid = 1)
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the prime key non-clustered indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] ADD CONSTRAINT [PK_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] PRIMARY KEY NONCLUSTERED (['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] DROP CONSTRAINT ['
+ i.name
+ ']'
, 'PK_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'PK'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid BETWEEN 2 AND 255)
AND (i.status & 2048) > 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Now create the Drop and Create statements for the prime key clustered indexes.
INSERT INTO @tbl
( CreateStatement
, DropStatement
, ConstraintName
, OrigConstraint
, TypeOf
)
SELECT 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] ADD CONSTRAINT [PC_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
+ '] PRIMARY KEY CLUSTERED (['
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1) + ']'
+
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 3) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 4) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 4) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 5) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 6) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 7) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 8) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 9) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 10) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 11) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 12) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 13) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 14) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 15) + ']'
END +
CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 16) IS NULL THEN
''
ELSE
', [' + INDEX_COL(u.name + '.' + QUOTENAME(o.name),i.indid, 16) + ']'
END +
') WITH FILLFACTOR = 90'
, 'ALTER TABLE ['
+ u.name
+ '].['
+ o.name
+ '] DROP CONSTRAINT ['
+ i.name
+ ']'
, 'PC_'
+ o.name
+ '_'
+ INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 1)
+ CASE WHEN INDEX_COL(u.name + '.' + QUOTENAME(o.name), i.indid, 2) IS NULL THEN
''
ELSE
'_Ect'
END
, i.name
, 'PC'
FROM sysobjects o WITH (NOLOCK) LEFT OUTER JOIN sysindexes i WITH (NOLOCK)
ON o.id = i.id
INNER JOIN sysusers u WITH (NOLOCK)
ON o.uid = u.uid
WHERE (o.type = 'U')
AND (i.indid = 1)
AND (i.status & 2048) > 0
AND (o.name <> 'dtproperties')
AND o.uid = user_id(N'dbo')
AND (LEFT(o.name, 4) <> 'Fog_')
AND (LEFT(o.name, 5) <> 'Spot_')
AND (LEFT(i.name, 8) <> '_WA_Sys_')
ORDER BY o.name, i.indid
-- Remove the list of user table objects from memory.
DELETE
FROM @tbl_all
-- Start a transaction.
BEGIN TRANSACTION
-- First drop the Foreign Key codes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'FK'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the default constraints.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'DF'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the non unique indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'IX'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the unique indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'IU'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the non-unique clustered indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'CL'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the unique clustered indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'CU'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the non-clustered constraint indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'CX'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the clustered constraint indexes.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'CC'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the non-clustered Primary Key constraints.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'PK'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt)
-- Drop the clustered Primary Key constraints.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
WHERE TypeOf = 'PC'
WHILE (@min_id <= @cnt)
BEGIN
SELECT @valD = DropStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @min_id)
IF (@valD IS NOT NULL)
EXEC(@valD)
IF (@@ERROR <> 0)
GOTO Error_Exit
SET @valD = NULL
SET @i = NULL
SET @min_id = @min_id + 1
END -- of WHILE (@min_id <= @cnt) */
IF (@drop_only = 0)
BEGIN
-- Re-add all the indexex back to the database by transversing the
-- @tbl records in reverse order.
SELECT @min_id = MIN(CountID)
, @cnt = MAX(CountID)
FROM @tbl
SET @i = NULL
SET @valI = NULL
WHILE @cnt >= @min_id
BEGIN
SELECT @valI = CreateStatement
, @fkey = ConstraintName
FROM @tbl
WHERE (CountID = @cnt)
SELECT @i = COUNT(CountID)
FROM @tbl
WHERE (ConstraintName = @fkey)
AND (CountID > @cnt)
IF (@i = 0)
EXEC(@valI) --PRINT @valI
ELSE
PRINT 'Found Duplicate for: ' + @fkey
IF (@@ERROR <> 0)
BEGIN
PRINT 'An Error Occured...'
GOTO Error_Exit
END
SET @i = 0
SET @valI = NULL
SET @cnt = @cnt - 1
END -- of WHILE @cnt >= @min_id */ END -- of IF (@drop_only = 0)
IF (@@ERROR = 0)
COMMIT TRANSACTION
ELSE
BEGIN
Error_Exit:
ROLLBACK TRANSACTION
END --
RETURN 
This article has a description of an index utilization tool that Ron Johnson uses to analyze the effectiveness and use of indexes in a system.
2012-07-04 (first published: 2011-07-05)
19,268 reads
For each user database rebuild indexes, update statistics and shrink. The script will use the system catalog to produce a list of databases. If you want to excluce a database add the excluded db name to the 'not in' list. You can also adjust the target fillfactor.
2003-05-07
1,788 reads
I see scripts all the time on how to reindex all the tables in a db but they are usually too generic and may cause issues. For instance they typically cause the log file to grow very large and may even fill the hard drive. This is intended to be run off hours when db […]
2003-04-24
635 reads
SQL 2000 OnlyThis script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)2. Defragment all user indexes in the database3. Gather/Log DBCC SHOWCONTIG statistics (post defrag)4. DBCC DBREINDEX […]
2010-04-19 (first published: 2003-03-19)
2,212 reads
This script uses sysobjects and sysindexes to ID all tables in a database that are indexed, and/or clustered. It then runs through per table all of the indexes and runs DBREINDEX with their fillfactor. This eliminates having to maintain a list of all indexes and having code fail when it tries to modify indexes that […]
2002-12-19
1,748 reads