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. |
|
|
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? |
|
|
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 thathttp://www.code-magazine.com/Article.aspx?quickid=0301101 |
|
|
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? |
|
|
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 |
|
|
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? |
|
|
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 |
|
|
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 |
|
|
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 2Invalid object name 'SYSINDEXES'. |
|
|
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. |
|
|
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 1Invalid object name 'SYS.SYSINDEXES'. |
|
|
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 |
|
|
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 replacementssysindexes -> sys.indexessysindexkeys -> sys.index_columnssyscolumns -> sys.columnsID column -> object_idCOLID column -> column_idINDID column -> index_id(I.STATUS & 64)=0 -> I.is_hypothetical=0 |
|
|
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. |
|
|
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 2Invalid 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_productionSELECT 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') =0DECLARE @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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
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..... |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 |
|
|
X002548
Not Just a Number
15586 Posts |
|
Next Page
|