Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Drop and Recreate all indexes

Author  Topic 

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-19 : 16:28:16
I need a script to drop and recreate all indexes in a large database (over 5000 objects). Is there anyone out there that might have a script like this?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 16:47:59
Do you specifically want script to drop and create the indexes? DBCC DBREINDEX will rebuild all of the indexes for a table or even the entire database for you.
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-19 : 16:58:21
quote:
Originally posted by snSQL

Do you specifically want script to drop and create the indexes? DBCC DBREINDEX will rebuild all of the indexes for a table or even the entire database for you.



I know about DBREINDEX. However, we have the need to explicitly drop and then recreate the indexes from scratch. How can I do this?
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 17:43:28
In that case, here's an article that describes exactly that

http://www.code-magazine.com/Article.aspx?quickid=0301101
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-20 : 14:17:37
Great! I read the article, and it looks like this is what I need, however, the highest level script is an example of recreating an index for one table. I am a newbie at T-SQL so I am still looking for that high level script that will spin through and generate indexes for all of the tables in the database. I looked at sp_msforeach... but I am at a loss of how to use these types of stored procedures and pass the right arguments.... How might I do that?
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-20 : 14:48:02
Actually, while that article is great for understanding all the pieces you need, there is a complete script here (you'll need to register for a free account to get this)
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1652
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-20 : 16:30:17
Works great for SQL2000. I need one for SQL2005, is there one for that version?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-20 : 16:45:39
Did you trying running it on SQL Server 2005? If so, what error(s) are you getting?

Tara Kizer
Go to Top of Page

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-20 : 17:48:59
I allready give you a short solution in other Forum. But you were more specific there. I suggest to rewrite your question.


All in Love is Fair
Stevie Wonder
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-23 : 12:46:56
snSQL, the script you refer to works OK on the Master database, but it does not work on my application database. Here is the error message I recieved:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYSINDEXES'.
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-23 : 13:49:29
SQL Server 2005 uses the sys schema for the system tables, so you'll need to add sys. in front of the table names, so change sysindexes to sys.sysindexes and so on, and it should work.
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-23 : 14:02:51
Well, I tried that but it still blows up. Here is the error message:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYS.SYSINDEXES'.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-23 : 14:34:59
It is now called sys.indexes. Please do some checking on these types of things prior to posting again as it took me less than a minute to find the new name. These are stored in the master database.

Tara Kizer
Go to Top of Page

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-23 : 15:31:36
That is a little strange though - the system tables have changed but there are compatibility views for the old names so sys.sysindexes should work (I tried it on 2005 myself and it worked fine with sys.sysindexes - I went through that script and simply changed every name that started with sys, to sys.sys...)

It won't work if you use sys.indexes, because that view (it's not the system table either, they are now completely hidden) doesn't have the same columns as the old sysindexes table.

You'll need to make the following replacements
sysindexes -> sys.indexes
sysindexkeys -> sys.index_columns
syscolumns -> sys.columns
ID column -> object_id
COLID column -> column_id
INDID column -> index_id
(I.STATUS & 64)=0 -> I.is_hypothetical=0
Go to Top of Page

propanecan
Yak Posting Veteran

60 Posts

Posted - 2006-10-23 : 21:00:37
Perhaps his install is case-sensitive or binary, therefore the object SYS.SYSINDEXES would not be found.
Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-24 : 10:30:00
Dear snSQL -

Now this is making some sense, but I have run into something very odd. I have in the views.system views for my database, the sys.indexes view. However, after making the changes to the script (as descibed above), I still get the error:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'SYS.INDEXES'.

The script does work in the master database (still). Is there some kind of linkage not working here? Why can't it see the sys.indexes view?

Below is the script (as edited to include your suggested changes). I am executing it in Query Analyzer. What am I doing wrong?

use qw_production
SELECT TOP 100
REPLICATE(' ',4000) AS COLNAMES ,
OBJECT_NAME(I.object_id) AS TABLENAME,
I.object_id AS TABLEID,
I.index_id AS INDEXID,
I.NAME AS INDEXNAME,
-- I.STATUS,
INDEXPROPERTY (I.object_id,I.NAME,'ISUNIQUE') AS ISUNIQUE,
INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') AS ISCLUSTERED,
INDEXPROPERTY (I.object_id,I.NAME,'INDEXFILLFACTOR') AS INDEXFILLFACTOR
INTO #TMP
FROM SYS.INDEXES I
WHERE I.index_id > 0
AND I.index_id < 255
AND I.is_hypothetical=0
--uncomment below to eliminate PK or UNIQUE indexes;
--what i call 'normal' indexes
--AND INDEXPROPERTY (I.object_id,I.NAME,'ISUNIQUE') =0
--AND INDEXPROPERTY (I.object_id,I.NAME,'ISCLUSTERED') =0

DECLARE
@ISQL VARCHAR(4000),
@TABLEID INT,
@INDEXID INT,
@MAXTABLELENGTH INT,
@MAXINDEXLENGTH INT
--USED FOR FORMATTING ONLY
SELECT @MAXTABLELENGTH=MAX(LEN(TABLENAME)) FROM #TMP
SELECT @MAXINDEXLENGTH=MAX(LEN(INDEXNAME)) FROM #TMP

DECLARE C1 CURSOR FOR
SELECT TABLEID,INDEXID FROM #TMP
OPEN C1
FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = ''
SELECT @ISQL=@ISQL + ISNULL(SYS.COLUMNS.NAME,'') + ',' FROM SYS.INDEXES I
INNER JOIN SYS.INDEX_COLUMNS ON I.object_id=SYS.INDEX_COLUMNS.object_id AND I.index_id=SYS.INDEX_COLUMNS.index_id
INNER JOIN SYS.COLUMNS ON SYS.INDEX_COLUMNS.object_id=SYS.COLUMNS.object_id AND SYS.INDEX_COLUMNS.column_id=SYS.COLUMNS.column_id
WHERE I.index_id > 0
AND I.index_id < 255
AND I.is_hypothetical=0
AND I.object_id=@TABLEID AND I.index_id=@INDEXID
ORDER BY SYS.COLUMNS.column_id
UPDATE #TMP SET COLNAMES=@ISQL WHERE TABLEID=@TABLEID AND INDEXID=@INDEXID

FETCH NEXT FROM C1 INTO @TABLEID,@INDEXID
END
CLOSE C1
DEALLOCATE C1
--AT THIS POINT, THE 'COLNAMES' COLUMN HAS A TRAILING COMMA
UPDATE #TMP SET COLNAMES=LEFT(COLNAMES,LEN(COLNAMES) -1)

SELECT 'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN ' UNIQUE ' ELSE ' ' END
+ CASE WHEN ISCLUSTERED = 1 THEN ' CLUSTERED ' ELSE ' ' END
+ ' INDEX [' + UPPER(INDEXNAME) + ']'
+ SPACE(@MAXINDEXLENGTH - LEN(INDEXNAME))
+' ON [' + UPPER(TABLENAME) + '] '
+ SPACE(@MAXTABLELENGTH - LEN(TABLENAME))
+ '(' + UPPER(COLNAMES) + ')'
+ CASE WHEN INDEXFILLFACTOR = 0 THEN '' ELSE ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) END --AS SQL
FROM #TMP

--SELECT * FROM #TMP
DROP TABLE #TMP



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-24 : 10:46:24
Good lord, why not just script the database.

Use the cat to generate all of the drops

Do what ever work you need to do (I don't think this has been stated)

The execute the script for the indexes

What's the big deal?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-24 : 11:50:16
Brett -

I have over 2000 indexes to drop and re-create. That's the BIG deal.....
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-24 : 12:11:48
Yeah, so why is scripting the database indexes not a good idea? I don't understand.

Also, do you have ERWin From CA?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

JerrySommerville
Starting Member

12 Posts

Posted - 2006-10-24 : 12:47:42
Dear X002548 -

Oh do I wish I had a copy of Erwin, but alas, I do not. :-(
I even thought of an Eval copy, but it will not script a large DB like mine without a full license.

... Jerry
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-24 : 13:49:13
Do you know how to script your database?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -