Normalize all constraints’ & indices’ names

Tweet
Share
Share
0 Shares

Constraints that are created by SSMA or by user via SSMS tend to have ugly names. Worse, auto-generated names are not stable across backups. Meaning if you restore a new database based on a copy of another database, the constraint names will change. That create huge problems during migrations because scripts that references constraint will fail. This script helps us clean up all non-standard naming and use our own naming convention uniformly.

Default Constraints

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(dc.object_id)), N'.', QUOTENAME(dc.name), N''', N''DF_', OBJECT_NAME(dc.parent_object_id), N'_', REPLACE(c.name, N' ', N'_'), ''', N''OBJECT'';')
FROM sys.default_constraints AS dc
INNER JOIN sys.columns AS c
	ON dc.parent_object_id = c.object_id
	AND dc.parent_column_id = c.column_id
WHERE dc.name <> CONCAT('DF_', OBJECT_NAME(dc.parent_object_id), N'_', REPLACE(c.name, N' ', N'_'))

Foreign Key Constraints

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(fk.object_id)), N'.', QUOTENAME(fk.name), N''', N''FK_', REPLACE(OBJECT_NAME(fk.parent_object_id), N' ', N'_'), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'), ''', N''OBJECT'';')
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
	ON fk.object_id = fc.constraint_object_id
INNER JOIN sys.columns AS c
	ON fc.parent_object_id = c.object_id
	AND fc.parent_column_id = c.column_id
GROUP BY fk.name, fk.parent_object_id, fk.object_id
HAVING fk.name <> CONCAT('FK_', OBJECT_NAME(fk.parent_object_id), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'));

Check Constraints

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(ck.object_id)), N'.', QUOTENAME(ck.name), N''', N''CK_', REPLACE(OBJECT_NAME(ck.parent_object_id), N' ', N'_'), N'_', REPLACE(c.name, N' ', N'_'), N''', N''OBJECT'';')
FROM sys.check_constraints AS ck
INNER JOIN sys.columns AS c
	ON ck.parent_object_id = c.object_id
	AND ck.parent_column_id = c.column_id
WHERE ck.name <> CONCAT(N'CK_', OBJECT_NAME(ck.parent_object_id), N'_', REPLACE(c.name, N' ', N'_'));

Primary Key Constraints

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)), N'.', QUOTENAME(i.name), N''', N''PK_', REPLACE(OBJECT_NAME(i.object_id), N' ', N'_'), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'), ''', N''OBJECT'';')
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
	ON i.object_id = ic.object_id 
	AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
	ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
WHERE i.is_primary_key <> 0
GROUP BY i.name, i.object_id
HAVING i.name <> CONCAT('PK_', OBJECT_NAME(i.object_id), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'));

Unique Constraints

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)), N'.', QUOTENAME(i.name), N''', N''UQ_', REPLACE(OBJECT_NAME(i.object_id), N' ', N'_'), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'), ''', N''OBJECT'';')
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
	ON i.object_id = ic.object_id 
	AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
	ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
WHERE i.is_unique <> 0
  AND i.is_unique_constraint <> 0
  AND i.is_primary_key = 0
GROUP BY i.name, i.object_id
HAVING i.name <> CONCAT('UQ_', OBJECT_NAME(i.object_id), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'));

Unique Index

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)), N'.', QUOTENAME(OBJECT_NAME(i.object_id)), N'.', QUOTENAME(i.name), N''', N''UQ_', REPLACE(OBJECT_NAME(i.object_id), N' ', N'_'), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'), ''', N''INDEX'';')
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
	ON i.object_id = ic.object_id 
	AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
	ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t 
	ON i.object_id = t.object_id
WHERE i.is_unique <> 0
  AND i.is_unique_constraint = 0
  AND i.is_primary_key = 0
GROUP BY i.name, i.object_id
HAVING i.name <> CONCAT('UQ_', OBJECT_NAME(i.object_id), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'));

Index

SELECT CONCAT(N'EXEC sys.sp_rename N''', QUOTENAME(OBJECT_SCHEMA_NAME(i.object_id)), N'.', QUOTENAME(OBJECT_NAME(i.object_id)), N'.', QUOTENAME(i.name), N''', N''IX_', REPLACE(OBJECT_NAME(i.object_id), N' ', N'_'), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'), ''', N''INDEX'';')
FROM sys.indexes AS i 
INNER JOIN sys.index_columns AS ic 
	ON i.object_id = ic.object_id 
	AND i.index_id = ic.index_id
INNER JOIN sys.columns AS c
	ON ic.object_id = c.object_id
	AND ic.column_id = c.column_id
INNER JOIN sys.tables AS t 
	ON i.object_id = t.object_id
WHERE i.is_unique = 0
  AND i.is_unique_constraint = 0
  AND i.is_primary_key = 0
GROUP BY i.name, i.object_id
HAVING i.name <> CONCAT('IX_', OBJECT_NAME(i.object_id), N'_', STRING_AGG(REPLACE(c.name, N' ', N'_'), N'_'));

Related Posts