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
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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'_')); |