Index information on a particular database.

I have gotten into a situation where one of the index page got corrupted in one of the databases. Regarding corruption we come to know when run DBCC CHECKDB command and it thrown an error message like below. Msg 8936, Level 16, State 1, Line 3 Table error: Object ID 1701581100, index ID 1, partition ID 72057594048479232, alloc unit ID 72057594058964992 (type In-row data). B-tree chain linkage mismatch. (1:1209224)->next = (1:1081), but (1:1081)->Prev = (1:1080). Search here with the below query by adding ObjectID(above) to the query
USE DatabaseNameHere
GO
SELECT * FROM sys.indexes
where object_id='123456'
This query will also bring information about Primary Key indexes.
USE DatabaseNameHere
GO
select s.name, t.name, i.name, c.name from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
inner join sys.indexes i on i.object_id = t.object_id
inner join sys.index_columns ic on ic.object_id = t.object_id
inner join sys.columns c on c.object_id = t.object_id and
        ic.column_id = c.column_id
where i.index_id > 0    
 and i.type in (1, 2) -- clustered & nonclustered only
 and i.is_primary_key = 1 -- do not include PK indexes
 and i.is_unique_constraint = 0 -- do not include UQ
 and i.is_disabled = 0
 and i.is_hypothetical = 0
 and ic.key_ordinal > 0
 and i.name='IndexNameHere'
order by ic.key_ordinal

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server