Skip to content

Instantly share code, notes, and snippets.

@Zagrophyte
Last active September 30, 2021 09:25
Show Gist options
  • Save Zagrophyte/95c6a11b3b61d392cff8815b99ac4a14 to your computer and use it in GitHub Desktop.
Save Zagrophyte/95c6a11b3b61d392cff8815b99ac4a14 to your computer and use it in GitHub Desktop.
SQL Table Index and Constraint Name Analysis and Repair Tool
/* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment