Posts Tagged ‘sys.indexes’

sys.foreign_keys does not have matching row in sys.indexes

November 19, 2015 Leave a comment

Running DBCC CHECKDB you are getting following error message:

Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=194099732,key_index_id=7) of row (object_id=2040565179) in sys.foreign_keys does not have a matching row (object_id=194099732,index_id=7) in sys.indexes

This error means, that Unique key constraint (index_id 7) in the primary table (object_id 194099732) is missing, which was referenced by child table’s FK constraint (FK object_id 2040565179).  This should not happen, SQL Server will not allow you to drop a constraint that is referenced by FK.  If attempted should get following error message:

Msg 3723, Level 16, State 6, Line 1
An explicit DROP INDEX is not allowed on index ‘dbo.a.NonClusteredIndex-20151119-085219’. It is being used for FOREIGN KEY constraint enforcement.

So if we are suppose to get errors? Why do we have corruption; simple answer, someone be making updates to system tables directly, which is not allowed or supported!

Actually we are not able to update system tables in SQL Server 2005+ (ref), however in SQL 2000 days, we had setting called allow updates in sp_configure options.  Also supported by the KB2787112.

So question is how do you fix it?

First, identify the child table name from sys.foreign_keys:

SELECT object_name(parent_object_id) AS TableName
FROM sys.foreign_keys
WHERE name = 'FK_b_a'

Second, script our constraint definition:

  1. Find the table, we got in SQL Statement above.
  2. Go to Keys.
  3. Right click on FK constraint name.
  4. Script Key As.
  5. Create To.
  6. New Query Window.

Third, drop the FK constraint:

ALTER TABLE [schema].[tablename] DROP CONSTRAINT [fk_constraint_name]

Fourth, Re-create the constraint, with script generated in Step 2.

If it was issue of someone playing around in system tables, this should resolve it.  However, if you get error similar to below:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table ‘dbo.a’ that match the referencing column list in the foreign key ‘FK_b_a’.

This means, that the key is missing in parent table and appropriate index needs be created before FK constraint can be created.  Since SQL doesn’t allow the index to be dropped there most likely are other corruption issues that have gone unnoticed.  If that is an issue, you will have to rely on your backups for recovery.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Index Creation Date

October 18, 2015 Leave a comment

Often when troubleshooting performance related issues, it is beneficial to know when new indexes were introduced.  However currently there is no easy way to keep track of this information or attain this information.  That’s why one of my colleagues and a good friend, Mohamed Sharaf (Blog | Twitter), has submitted a request on Microsoft Connect, Expose index creation date in sys.indexes, Link.  Please take a few minutes to vote if you agree Smile.

This post is cross posted on my SQLCAN Blog, MSDN Blog, and SQL server Consultation blog.

Getting List of Fragmented Indexes in a Database

November 9, 2011 2 comments

Index fragmentation is naturally happens as an action of insert, deletes and updates.  It is important to manage this fragmentation, if not you can have performance issue with range type queries.

              'HEAP TABLE, NO INDEXES'
           END                              AS IndexName
         , index_type_desc                  AS IndexType
         , index_depth
         , avg_fragmentation_in_percent
         , page_count
    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') IPS
    JOIN sys.indexes I ON IPS.OBJECT_ID = I.OBJECT_ID AND IPS.index_id = I.index_id
ORDER BY avg_fragmentation_in_percent

Getting Index count for all tables

August 9, 2010 Leave a comment
    SELECT AS Table_Name
	       , t.object_id
		   , i.index_id
		   , i.type
		   , i.type_desc
      FROM sys.indexes i
INNER JOIN sys.tables t
        ON i.object_id = t.object_id
     WHERE t.is_ms_shipped = 0
	   AND i.is_disabled = 0
	   AND i.is_hypothetical = 0
       AND i.type <= 2
), cte2 AS
  FROM cte c
 PIVOT (count(type) for type_desc in ([NONCLUSTERED])) pv
    SELECT   c2.table_name       AS TableName
           , MAX(p.rows)         AS Rows
		   , CASE WHEN (OBJECTPROPERTY(c2.object_id,'TableHasClustIndex') = 1) THEN
			 END                 AS TableType
           , sum([NONCLUSTERED]) AS NumOfNonClusteredIndexes
      FROM cte2 c2
INNER JOIN sys.partitions p
        ON c2.[object_id] = p.[object_id]
	   AND c2.index_id = p.index_id
  GROUP BY table_name, c2.object_id

Davide Mauri wrote a nice script, I have done similar work without using CTE.  So I really liked the script; I notice in his comments Rob suggested using OBJECTPROPERTY.  I haven’t used the function much, but just to get use to it I have re-write Davide’s script using Object Property and reformatted the code to my style ;-).   I like Davide’s script, because when it comes to T-SQL coding, I am fairly week.  So it was nice seeing an example double CTE + Pivot table :D.


  1. SQLBlog.Com. Davide Marui. Find all the tables with no indexes at all. Link.
%d bloggers like this: