CHECK CONSTRAINTS - UNIQUE, PRIMARY KEY, FOREIGN KEY OR DEFAULT

--Returns 1 row for each CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY, and/or DEFAULT
SELECT 
    OBJECT_NAME(OBJECT_ID) AS NameofConstraint
        ,SCHEMA_NAME(schema_id) AS SchemaName
        ,OBJECT_NAME(parent_object_id) AS TableName
        ,type_desc AS ConstraintType
    FROM sys.objects
    WHERE type_desc LIKE '%CONSTRAINT'
        AND OBJECT_NAME(OBJECT_ID)='XYZ'

Drop and Create All Constraints of Database

SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'DROP CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Drop Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE (TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE'))
OR (TC.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY FC.TABLE_NAME DESC,
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') ASC


SELECT 'ALTER TABLE [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + TC.CONSTRAINT_NAME + ']' + CHAR(13)+CHAR(10)+
'  ' + TC.CONSTRAINT_TYPE +
CASE INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU.ORDINAL_POSITION WHEN 1 THEN '[' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'WITH FILLFACTOR = ' +
CONVERT(varchar(3), --Replace 0 FILLFACTOR with 100
ISNULL(NULLIF(
INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IndexFillFactor'),
0), 100)
) + CHAR(13)+CHAR(10)+
'ON [' + sfg.groupname + ']' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create PK/UNIQUE Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
LEFT JOIN sysindexes s ON s.name = TC.CONSTRAINT_NAME
AND s.id = object_id(TC.TABLE_NAME)
LEFT JOIN sysfilegroups sfg ON sfg.groupid = s.groupid
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
TC.TABLE_SCHEMA,
TC.TABLE_NAME,
TC.CONSTRAINT_NAME,
TC.CONSTRAINT_TYPE,
sfg.groupname
ORDER BY INDEXPROPERTY(OBJECT_ID(TC.TABLE_NAME), TC.CONSTRAINT_NAME, 'IsClustered') DESC

SELECT 'ALTER TABLE [' + FC.TABLE_SCHEMA + '].[' + FC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'ADD CONSTRAINT [' + FC.CONSTRAINT_NAME + '] ' + FC.CONSTRAINT_TYPE + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_FK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_FK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+
'REFERENCES [' + TC.TABLE_SCHEMA + '].[' + TC.TABLE_NAME + ']' + CHAR(13)+CHAR(10)+
'  (' +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 1 THEN '[' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 2 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 3 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 4 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 5 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 6 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 7 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 8 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 9 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 10 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 11 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 12 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 13 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 14 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 15 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
MAX(CASE KCU_PK.ORDINAL_POSITION WHEN 16 THEN ', [' + KCU_PK.COLUMN_NAME + ']' ELSE '' END) +
')' + CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
AS [-- Create FK Constraints]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_PK ON TC.TABLE_NAME = KCU_PK.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU_PK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.UNIQUE_CONSTRAINT_NAME = TC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FC ON FC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU_FK ON FC.TABLE_NAME = KCU_FK.TABLE_NAME
AND FC.CONSTRAINT_NAME = KCU_FK.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')

GROUP BY
FC.TABLE_SCHEMA,
FC.TABLE_NAME,
FC.CONSTRAINT_NAME,
FC.CONSTRAINT_TYPE,
TC.TABLE_SCHEMA,
TC.TABLE_NAME

Identify the cause of SQL Server blocking

SQL Server has a rich set of dynamic management views (DMVs) that helps you to quickly identify locking and blocking in SQL Server. That is why, writing such a query is quite simple in SQL Server. I used the following dynamic management views (DMVs) for my query.

• sys.dm_os_waiting_tasks - Returns information about blocked and blocking processes.
• sys.dm_exec_sessions - Returns information about authenticated sessions on SQL Server.
• sys.dm_exec_requests - Returns the detailed information about the requests currently executing on SQL Server.
• sys.dm_tran_locks - Returns the information about the current locks and the processes blocking them.
• sys.dm_exec_query_plan - Returns the showplan for the query in XML format.
• sys.dm_exec_sql_text - Returns the text of T-SQL batch.

The following is the query, which I have written using these dynamic management views (DMVs) that will help you to quickly identify the SPIDs and other information about the processes that are causing the blocking on SQL Server instance. This query returns the comprehensive information about the blocking and waiting processes, which is useful for troubleshooting SQL Server locking and blocking issues. This query is also a good way to analyze detailed information about locks, and help you to identify the cause of a large number of blocks.

WITH [Blocking]
AS (SELECT w.[session_id]
   ,s.[original_login_name]
   ,s.[login_name]
   ,w.[wait_duration_ms]
   ,w.[wait_type]
   ,r.[status]
   ,r.[wait_resource]
   ,w.[resource_description]
   ,s.[program_name]
   ,w.[blocking_session_id]
   ,s.[host_name]
   ,r.[command]
   ,r.[percent_complete]
   ,r.[cpu_time]
   ,r.[total_elapsed_time]
   ,r.[reads]
   ,r.[writes]
   ,r.[logical_reads]
   ,r.[row_count]
   ,q.[text]
   ,q.[dbid]
   ,p.[query_plan]
   ,r.[plan_handle]
 FROM [sys].[dm_os_waiting_tasks] w
 INNER JOIN [sys].[dm_exec_sessions] s ON w.[session_id] = s.[session_id]
 INNER JOIN [sys].[dm_exec_requests] r ON s.[session_id] = r.[session_id]
 CROSS APPLY [sys].[dm_exec_sql_text](r.[plan_handle]) q
 CROSS APPLY [sys].[dm_exec_query_plan](r.[plan_handle]) p
 WHERE w.[session_id] > 50
  AND w.[wait_type] NOT IN ('DBMIRROR_DBM_EVENT'
      ,'ASYNC_NETWORK_IO'))
SELECT b.[session_id] AS [WaitingSessionID]
      ,b.[blocking_session_id] AS [BlockingSessionID]
      ,b.[login_name] AS [WaitingUserSessionLogin]
      ,s1.[login_name] AS [BlockingUserSessionLogin]
      ,b.[original_login_name] AS [WaitingUserConnectionLogin] 
      ,s1.[original_login_name] AS [BlockingSessionConnectionLogin]
      ,b.[wait_duration_ms] AS [WaitDuration]
      ,b.[wait_type] AS [WaitType]
      ,t.[request_mode] AS [WaitRequestMode]
      ,UPPER(b.[status]) AS [WaitingProcessStatus]
      ,UPPER(s1.[status]) AS [BlockingSessionStatus]
      ,b.[wait_resource] AS [WaitResource]
      ,t.[resource_type] AS [WaitResourceType]
      ,t.[resource_database_id] AS [WaitResourceDatabaseID]
      ,DB_NAME(t.[resource_database_id]) AS [WaitResourceDatabaseName]
      ,b.[resource_description] AS [WaitResourceDescription]
      ,b.[program_name] AS [WaitingSessionProgramName]
      ,s1.[program_name] AS [BlockingSessionProgramName]
      ,b.[host_name] AS [WaitingHost]
      ,s1.[host_name] AS [BlockingHost]
      ,b.[command] AS [WaitingCommandType]
      ,b.[text] AS [WaitingCommandText]
      ,b.[row_count] AS [WaitingCommandRowCount]
      ,b.[percent_complete] AS [WaitingCommandPercentComplete]
      ,b.[cpu_time] AS [WaitingCommandCPUTime]
      ,b.[total_elapsed_time] AS [WaitingCommandTotalElapsedTime]
      ,b.[reads] AS [WaitingCommandReads]
      ,b.[writes] AS [WaitingCommandWrites]
      ,b.[logical_reads] AS [WaitingCommandLogicalReads]
      ,b.[query_plan] AS [WaitingCommandQueryPlan]
      ,b.[plan_handle] AS [WaitingCommandPlanHandle]
FROM [Blocking] b
INNER JOIN [sys].[dm_exec_sessions] s1
ON b.[blocking_session_id] = s1.[session_id]
INNER JOIN [sys].[dm_tran_locks] t
ON t.[request_session_id] = b.[session_id]
WHERE t.[request_status] = 'WAIT'
GO

Sample Output

To examine the results of this query, run it on SQL Server where you are experiencing blocks. For example, when I executed this query on my test SQL Server where I'm deliberately running some code to cause blocking, it brings the following results (Note: To fit the resultset, I've split the resultset into seven images):

The following are the columns returned by this query:

• WaitingSessionID - The SPID of the waiting session.
• BlockingSessionID - The SPID of the blocking session.
• WaitingSessionUserLogin - The user session login name under which waiting session is currently executing.
• BlockingSessionUserLogin - The user session login name under which blocking session is currently executing.
• WaitingUserConnectionLogin - The login name that the user used to create waiting session.
• BlockingSessionConnectionLogin - The login name that the user used to create waiting session.
• WaitDuration - Waiting process wait time in milliseconds.
• WaitType - Type of wait.
• WaitRequestMode - Mode of the wait request.
• WaitingProcessStatus - The status of waiting process.
• BlockingSessionStatus - The status of blocking process.
• WaitResource - The name of the resource request is waiting for.
• WaitResourceType - The type of the resource request is waiting for.
• WaitResourceDatabaseID - The database id of the database in which the requested resource exists.
• WaitResourceDatabaseName - The name of the database in which the requested resource exists.
• WaitResourceDescription - The detailed description of the waiting resource.
• WaitingSessionProgramName - The name of the program that initiated the waiting session.
• BlockingSessionProgramName - The name of the program that initiated the blocking session.
• WaitingHost - The name of the workstation that is specific to waiting session.
• BlockingHost - The name of the workstation that is specific to blocking session.
• WaitingCommandType - The type of waiting session command.
• WaitingCommandText - The text of waiting session command.
• WaitingCommandRowCount - Expected number of rows return by the waiting session.
• WaitingCommandPercentComplete - Percentage of the waiting request client.
• WaitingCommandCPUTime - CPU time used by waiting session.
• WaitingCommandTotalElapsedTime - The total time elapsed in milliseconds since the waiting request arrived.
• WaitingCommandReads - The number of reads performed by the waiting session request.
• WaitingCommandWrites - The number of writes performed by the waiting session request.
• WaitingCommandLogicalReads - The number of logical reads performed by the waiting session request.
• WaitingCommandQueryPlan - Waiting command execution plan.
• WaitingCommandPlanHandle - Plan handle of the waiting session command.

As you can see from above resultset, that process 53 listed BlockingSessionID column of row 4 is not blocked by another process, hence identified as the SPID that is the cause of the blocking on my test SQL Server instance.

Next Steps

• Transaction locks are the most common cause of blocked processes. The stronger (least concurrent) the isolation level, the more likely it is to cause a blocked process.
• Try to use less granular lock for your queries, as the less granular the lock, the more likely a blocked process or deadlock will not occur. For example, if the entire table is locked, there is a higher likelihood of blocks than if only a single row is locked.
• Revisit your database design because bad database design could be potential reason for excessive locking and blocking.
• Check out these tips to learn more about locking and blocking:
  • Understanding SQL Server Locking
  • Understanding SQL Server Blocking
  • Locking and Blocking Tips category

Check SQL Server a specified database index fragmentation percentage (avg_fragmentation_in_percent)

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,  ind.name AS IndexName, indexstats.index_type_desc AS IndexType,  indexstats.avg_fragmentation_in_percent  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats  INNER JOIN sys.indexes ind   ON ind.object_id = indexstats.object_id  AND ind.index_id = indexstats.index_id  WHERE indexstats.avg_fragmentation_in_percent > 30  ORDER BY indexstats.avg_fragmentation_in_percent DESC

How To Enable Data Access For Server

exec sp_serveroption 'server name', 'data access', 'true'

Query To Drop All Constraints From Database

DECLARE @STR VARCHAR(MAX)
DECLARE CUR CURSOR FOR

SELECT 'ALTER TABLE ' + '[' + S.[NAME] + '].[' + T.NAME + '] DROP CONSTRAINT ['+ C.NAME + ']'
FROM SYS.OBJECTS C, SYS.OBJECTS T, SYS.SCHEMAS S
WHERE C.TYPE IN ('C', 'F', 'PK', 'UQ', 'D')
 AND C.PARENT_OBJECT_ID=T.OBJECT_ID AND T.TYPE='U' AND T.SCHEMA_ID = S.SCHEMA_ID
ORDER BY C.TYPE

OPEN CUR
FETCH NEXT FROM CUR INTO @STR
WHILE (@@FETCH_STATUS = 0) BEGIN
 PRINT @STR
 EXEC (@STR)
 FETCH NEXT FROM CUR INTO @STR
END

CLOSE CUR
DEALLOCATE CUR

Find All Other Tables Which Depends On Foreign Keys Involved That Reference Your Own Table

;WITH ReferencingFK AS
(
    SELECT
        fk.Name AS 'FKName',
        OBJECT_NAME(fk.parent_object_id) 'ParentTable',
        cpa.name 'ParentColumnName',
        OBJECT_NAME(fk.referenced_object_id) 'ReferencedTable',
        cref.name 'ReferencedColumnName'
    FROM
        sys.foreign_keys fk
    INNER JOIN
        sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
    INNER JOIN
        sys.columns cpa ON fkc.parent_object_id = cpa.object_id AND fkc.parent_column_id = cpa.column_id
    INNER JOIN
        sys.columns cref ON fkc.referenced_object_id = cref.object_id AND fkc.referenced_column_id = cref.column_id
)
SELECT
    FKName,
    ParentTable,
    ParentColumnName,
    ReferencedTable,
    ReferencedColumnName
FROM
    ReferencingFK
WHERE
    ReferencedTable = 'table_name'   --  <=== put your table name here!
ORDER BY
    ParentTable, ReferencedTable, FKName