SQL Table Index and Constraint Name Analysis and Repair Tool

StandardConstraintAndIndexNameAnalyzer.sql

/* SQL Table Index and Constraint Name Analysis and Repair Tool
 * Finds all indexes and constraints with names that do not conform to expected 
 * patterns and helpfully generates the SQL to rename them to the right values.
 *
 * NOTE: I am not responsible for any issues that result from the use of this script, 
 *       always review any generated SQL output before running!
 */
;WITH AllConstraints AS
(
    SELECT  Type            = 'PrimaryKey'
    ,       SchemaName      = OBJECT_SCHEMA_NAME(c.parent_object_id)
    ,       ParentTableName = OBJECT_NAME(c.parent_object_id)
    ,       ObjectName      = c.name
    ,       ObjectSource    = 'sys.key_constraints'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'PK_' + OBJECT_NAME(c.parent_object_id) + '%'
    ,       SuggestedName   = 'PK_' + OBJECT_NAME(c.parent_object_id)
    ,       IsSystemNamed   = c.is_system_named
    FROM    sys.key_constraints c
    WHERE   c.type = 'PK'
    AND     c.is_ms_shipped = 0
    UNION ALL

    SELECT  Type            = 'ForeignKey'
    ,       SchemaName      = (OBJECT_SCHEMA_NAME(c.parent_object_id))
    ,       ParentTableName = (OBJECT_NAME(c.parent_object_id))
    ,       ObjectName      = (c.name)
    ,       ObjectSource    = 'sys.foreign_keys'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'FK_' + (OBJECT_NAME(c.parent_object_id)) + '_' + ft.name + '%'
    ,       SuggestedName   = 'FK_' + OBJECT_NAME(c.parent_object_id) + '_' + ft.name
    ,       IsSystemNamed   = c.is_system_named
    FROM    sys.foreign_keys c
    INNER JOIN sys.tables ft
               ON c.referenced_object_id = ft.object_id
    WHERE   c.is_ms_shipped = 0

    UNION ALL

    SELECT  Type            = 'UniqueConstraint'
    ,       SchemaName      = (OBJECT_SCHEMA_NAME(t.object_id))
    ,       ParentTableName = OBJECT_NAME(t.object_id)
    ,       ObjectName      = (c.name)
    ,       ObjectSource    = 'sys.key_constraints'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'UQ_' + t.name + '_%'
    ,       SuggestedName   = 'UQ_' + t.name + '_<suffix>'
    ,       IsSystemNamed   = 0
    FROM    sys.key_constraints c
    JOIN    sys.tables t
            ON t.object_id = c.parent_object_id
    WHERE   c.type = 'UQ'
    AND     c.type_desc != 'HEAP' --Exclude HEAP
    AND     c.is_ms_shipped = 0

    UNION ALL

    SELECT  Type            = 'CheckConstraint'
    ,       SchemaName      = (OBJECT_SCHEMA_NAME(c.parent_object_id))
    ,       ParentTableName = (OBJECT_NAME(c.parent_object_id))
    ,       ObjectName      = (c.name)
    ,       ObjectSource    = 'sys.check_constraints'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'CK_' + (OBJECT_NAME(c.parent_object_id)) + '_' + col.name + '%'
    ,       SuggestedName   = 'CK_' + (OBJECT_NAME(c.parent_object_id)) + '_' + col.name
    ,       IsSystemNamed   = c.is_system_named
    FROM    sys.check_constraints c
    INNER JOIN sys.columns col
               ON c.parent_object_id = col.object_id
                  AND   col.column_id = c.parent_column_id
    INNER JOIN sys.tables t
               ON c.parent_object_id = t.object_id
    WHERE   c.is_ms_shipped = 0

    UNION ALL
    
    SELECT  Type            = 'DefaultConstraint'
    ,       SchemaName      = (OBJECT_SCHEMA_NAME(c.parent_object_id))
    ,       ParentTableName = (OBJECT_NAME(c.parent_object_id))
    ,       ObjectName      = (c.name)
    ,       ObjectSource    = 'sys.default_constraints'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'DF_' + (OBJECT_NAME(c.parent_object_id)) + '_' + col.name + '%'
    ,       SuggestedName   = 'DF_' + (OBJECT_NAME(c.parent_object_id)) + '_' + col.name
    ,       IsSystemNamed   = c.is_system_named
    FROM    sys.default_constraints c
    INNER JOIN sys.columns col
               ON c.parent_object_id = col.object_id
                  AND   col.column_id = c.parent_column_id
    INNER JOIN sys.tables t
               ON c.parent_object_id = t.object_id
    WHERE   c.is_ms_shipped = 0

    UNION ALL

    SELECT  Type            = 'Index'
    ,       SchemaName      = (OBJECT_SCHEMA_NAME(t.object_id))
    ,       ParentTableName = OBJECT_NAME(t.object_id)
    ,       ObjectName      = (c.name)
    ,       ObjectSource    = 'sys.indexes'
    ,       CanonicalName   = QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(t.name) + '.' + QUOTENAME(c.name)
    ,       ExpectedPattern = 'IDX_' + t.name + '_%'
    ,       SuggestedName   = 'IDX_' + t.name + '_<suffix>'
    ,       IsSystemNamed   = 0
    FROM    sys.indexes c
    JOIN    sys.tables t
            ON t.object_id = c.object_id
    WHERE   c.is_primary_key = 0 -- Exclude PKs
            AND c.is_unique_constraint = 0 -- Exclude UQs
            AND c.type_desc != 'HEAP' --Exclude HEAP
)
SELECT      *
,           RepairSql = CASE
                            WHEN a.IsSystemNamed = 1 THEN
                                '

-- Get the name of the first system-generated ' + a.Type + ' matching the name pattern for this table
DECLARE @Old_' + a.ObjectName + ' nvarchar(128) = 
(
    SELECT TOP 1 QUOTENAME(OBJECT_SCHEMA_NAME(c.object_ID)) + ''.'' + QUOTENAME(c.name)
    FROM ' + a.ObjectSource + ' c 
    WHERE c.name LIKE ''' + CASE a.Type
                                WHEN 'PrimaryKey' THEN
                                    LEFT(a.ObjectName, LEN(a.ObjectName) - 16)
                                WHEN 'ForeignKey' THEN
                                    LEFT(a.ObjectName, LEN(a.ObjectName) - 16)
                                ELSE
                                    LEFT(a.ObjectName, LEN(a.ObjectName) - 8)
                            END + '%''
    AND     ' + CASE a.Type
                    WHEN 'PrimaryKey' THEN
                        'OBJECT_NAME(c.parent_object_id) = ''' + a.ParentTableName + ''' AND c.type = ''PK'''
                    WHEN 'ForeignKey' THEN
                        'OBJECT_NAME(c.parent_object_id) = ''' + a.ParentTableName + ''' AND c.type = ''FK'''
                    WHEN 'UniqueConstraint' THEN
                        'OBJECT_NAME(c.parent_object_id) = ''' + a.ParentTableName + ''' AND c.type = ''UQ'''
                    WHEN 'Index' THEN
                        'OBJECT_NAME(c.parent_object_id) = ''' + a.ParentTableName + ''' AND c.is_primary_key = 0 AND c.is_unique_constraint = 0 -- Include PK/UQs'
                    ELSE
                        'OBJECT_NAME(c.parent_object_id) = ''' + a.ParentTableName + ''''
                END + '
)
-- Rename nonstandard ' + a.Type + '
PRINT ''Renaming system-generated ' + a.Type + ': '' + @Old_' + a.ObjectName + ' + '' to ' + a.SuggestedName + '''
EXEC sys.sp_rename 
    @objname = @Old_' + a.ObjectName + '
,   @newname = N''' + REPLACE(a.SuggestedName,'<suffix>','PUTSUFFIXHERE'' NeedsDescriptiveSuffix --Intentional syntax error to force you to add desired suffix ') + ''' 
,   @objtype = ''' + CASE a.Type
                         WHEN 'Index' THEN
                             'index'
                         WHEN 'PrimaryKey' THEN
                             'object'
                         WHEN 'UniqueConstraint' THEN
                             'object'
                         ELSE
                             'object'
                     END + ''''
                            WHEN a.IsSystemNamed = 0 THEN
                                '

-- Rename nonstandard ' + a.Type + '
PRINT ''Renaming ' + a.Type + ': ' + a.CanonicalName + ' to ' + a.SuggestedName + '''
EXEC sys.sp_rename 
    @objname = N''' + a.CanonicalName + '''
,   @newname = N''' + REPLACE(a.SuggestedName,'<suffix>','PUTSUFFIXHERE'' NeedsDescriptiveSuffix --Intentional syntax error to force you to add desired suffix ') + '''
,   @objtype = ''' + CASE a.Type
                         WHEN 'Index' THEN
                             'index'
                         WHEN 'PrimaryKey' THEN
                             'object'
                         WHEN 'UniqueConstraint' THEN
                             'object'
                         ELSE
                             'object'
                     END + ''''
                        END
FROM        AllConstraints a
WHERE       ObjectName NOT LIKE ExpectedPattern ESCAPE '\'
ORDER BY    a.SchemaName
,           a.ParentTableName
,           a.ObjectName;