dropping and recreating indexes dynamically

muzzy2003 - I can't quite get this statement to work:

FOR     SELECT     indid, name, OrigFillFactor
          FROM     sysindexes
          WHERE     id = @intObjectID
          AND     status & 0x800 = 0
          AND     status & 0x2 = 0
          AND     status <> 0
          AND     first <> 0

I did get it further along but am not clear on the how the status criteria and first criteria work as far as identifying the rows I need.  I get several rows for the system indexes starting with _WA_Sys when I remove the 'status' and 'first' criteria.  If I use the criteria as you have it entered, it eliminates all my rows. I hope this isn't a stupid question but I don't understand what 'status & 0x800' does.

This is the status column and the first column for the actual rows I want:
18450      0x28B609000100
0      0x6A0100000100
0      0x067501000100

The rows I don't want look like this for that particular table
10485856      0x000000000000
8388704      0x000000000000

Take out the status <> 0 and you should get all but your first. My recollection of the 0x800 and 0x2 flags are that they are the primary key and unique flags for an index. The first one you list has both set, and is therefore (if my recollection is right) the primary key of the table. Are you sure you want to drop this as well? If not, then hopefully removing the status <> 0 will sort you out - I can't remember why I added it in the first place.

Do you need any further help with this problem? Thanks.

I might...I was able to get started but haven't gotten to complete it yet because some other things have taken priority but I hope to be hitting on it next week.  I do have one question offhand....security....we don't have dbo rights in production so I would assume we would have issues with dropping and recreating indexes within our proc unless we set something up special.  Do you have any thoughts on approaches to this?

Thanks for being so patient.  

No problem.

You need to be in the db_owner or db_ddladmin roles to drop and recreate the indexes. Since this is a batch process not an ad hoc thing, I would negotiate with the DBAs for this to run under a special login in the db_ddladmin role.

Of course, the above proc that goes against the sysindexes table might not work in SQL2005....

arbert - why do you say that? What's changing?

No, true.

The system tables are being replaced in Yukon with different ones. They are implementing many of the old names as views for backwards compatibility, but there's no guarantee.

"The system tables are being replaced in Yukon with different ones."

I wouldn't say replaced--there are a lot of changes.  There are never any guarantees when you use the system tables directly--that's why you use the Information_schema views or built in (documented) stored procedures.  Not only can an upgrade of SQL Server break things, but even a service pack/hotfix can cause problems.

I ended up using Muzzy2003's logic tweaked to used sp_helpindex results and metadata functions where possible. This hopefully will limit the impact if they make changes to sysindexes.  I greatly appreciate both of your help.  You saved me a great deal of time and the process works beautifully!!