How to get Index name using T-sql or system tables 2

[miq]

hi,
I am experiencing problem with site's search feature so, here is a question where i am stuck. Not actually stuck but i want to do it this way. I want to find index name which i can use as parameter for dbcc dbreindex statement. I have index id but not name. Enterprise manager can be used to get name and i will eventually but, i think index name can be find using T-sql statement / system table.
Remember index id is 255 so there is no name in 'name' column of sysindexes table istead there is a table name. Where else i can get index name?

bye
miq

 

[MeanGreen]

Not sure what you mean indexid is 255. I ran the following SQL and got table name with index names.

select object_name(id),name from sysindexes

Hope this helps.

 

[tlbroadbent]

IndID 255 is not an index. IndID = 255 indicates the table has at least one text, ntext or image column. The sysindexes will show the table name for the index name. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[miq]

hi,
you both are right.

command : dbcc showcontig ('suppliers') with ALL_INDEXES
output :

DBCC SHOWCONTIG scanning 'Suppliers' table...
Table: 'Suppliers' (2137058649); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 3350.0
- Avg. Page Density (full).....................: 58.61%
DBCC SHOWCONTIG scanning 'Suppliers' table...
Table: 'Suppliers' (2137058649); index ID: 2, database ID: 6
LEAF level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 6393.0
- Avg. Page Density (full).....................: 21.02%
DBCC SHOWCONTIG scanning 'Suppliers' table...
Table: 'Suppliers' (2137058649); index ID: 3, database ID: 6
LEAF level scan performed.
- Pages Scanned................................: 1
- Extents Scanned..............................: 1
- Extent Switches..............................: 0
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 100.00% [1:1]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 7268.0
- Avg. Page Density (full).....................: 10.21%

DBCC SHOWCONTIG scanning 'Suppliers' table...
Table: 'Suppliers' (2137058649); index ID: 255, database ID: 6
LEAF level scan performed.
- Pages Scanned................................: 7
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Extent Scan Fragmentation ...................: 14.29%
- Avg. Bytes Free per Page.....................: 7605.7
- Avg. Page Density (full).....................: 6.03%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


The red colored index info says its id is 255. Which is indid in sysindexes table. I want to recreate this index since, its badly fragmanted. How can i use dbcc dbreindex statement for this index.

I hope iam clear this time !

bye
miq

 

[miq]

sorry,
meangrean, i tried your code and it worked. I was late to test your code. Sorry for that.
sorry, terry. I was probably not in my senses (-:

Both posts are helpfull.

bye
miq

 

[tlbroadbent]

You didn't understand. You cannot reindex indid 255 because it is not a real index. It is simply an entry in the sysindexes table pointing to the first IAM* page of the text, ntext or image data**.

* "Index Allocation Map (IAM) pages map the extents in a database file used by a heap or index. IAM pages also map the extents allocated to the ntext, text, and image page chain for any table that has columns of these types."
Source: SQL BOL "Managing Space Used by Objects"

** "Each table that has at least one text, ntext, or image column also has a row in sysindexes with indid = 255.
The column FirstIAM points to the chain of IAM pages that manage the text, ntext, and image pages."
Source: SQL BOL "Table and Index Architecture"

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[miq]

yes,
sorry, for my December 15th first post. I actually didn't read your December 14th post coorectly


IndID 255 is not an index. IndID = 255 indicates the table has at least one text, ntext or image column. The sysindexes will show the table name for the index name.


for which i apologized in my December 15th second post. Your Dec. 14th post was clear enogh to convay your message whcih you have to rewrite for me in your recent post. Sorry, for making you answer again.


bye
miq