Implémentation d'un standard de nommage pour les keys, index, contraintes

Pour renommer les keys primaires en PK_TableName simplement:

 CREATE PROCEDURE dbo.Rename_PrimaryKeys @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''PK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') + ''', ''OBJECT'';' FROM sys.key_constraints WHERE type = 'PK' AND name <> 'PK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') AND OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Pour renommer les keys FK avec le schéma FK_TableName_col_col_ReferencedName_col_col :

 CREATE PROCEDURE dbo.Rename_ForeignKeys_WithColumns @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''FK_' + REPLACE(OBJECT_NAME(fk.parent_object_id), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + '_' + REPLACE(OBJECT_NAME(fk.referenced_object_id), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.[object_id] WHERE fkc.constraint_object_id = fk.[object_id] ORDER BY fkc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') + ''', ''OBJECT'';' FROM sys.foreign_keys AS fk WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Pour les foreign keys si vous voulez juste FK_TableName_ReferencedName c'est beaucoup plus simple:

 CREATE PROCEDURE dbo.Rename_ForeignKeys @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(name, '''', '''''') + ''', ''FK_' + REPLACE(OBJECT_NAME(parent_object_id), '''', '') + '_' + REPLACE(OBJECT_NAME(referenced_object_id), '''', '') + ''', ''OBJECT'';' FROM sys.foreign_keys WHERE OBJECTPROPERTY(parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Pour les index, cela renomme tous les index IX_TableName_Col1_Col2... Il ignorera les keys primaires (puisqu'elles sont traitées séparément ci-dessus), appenda UQ_ aux index / contraintes uniques (donc IX_UQ_TableName_Col1_Col2... , traitera les contraintes uniques et les index uniques de la même façon, et ignorera les colonnes incluses. les colonnes incluses peuvent générer un conflit de noms si vous avez des index redondants qui ne diffèrent que par des colonnes incluses.)

 CREATE PROCEDURE dbo.Rename_Indexes @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(i.name, '''', '''''') + ''', ''IX_' + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE '' END + REPLACE(OBJECT_NAME(i.[object_id]), '''', '') + '_' + STUFF((SELECT '_' + REPLACE(c.name, '''', '') FROM sys.columns AS c INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id AND ic.[object_id] = c.[object_id] WHERE ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND is_included_column = 0 ORDER BY ic.index_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''', ''OBJECT'';' FROM sys.indexes AS i WHERE index_id > 0 AND is_primary_key = 0 -- dealt with separately AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Pour les contraintes par défaut:

 CREATE PROCEDURE dbo.Rename_DefaultConstraints @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(dc.name, '''', '''''') + ''', ''DF_' + REPLACE(OBJECT_NAME(dc.parent_object_id), '''','') + '_' + REPLACE(c.name, '''', '') + ''', ''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 AND OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Et enfin vérifier les contraintes:

 CREATE PROCEDURE dbo.Rename_CheckConstraints @PrintOnly BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + CHAR(13) + CHAR(10) + 'EXEC sp_rename ''' + REPLACE(cc.name, '''', '''''') + ''', ''CK_' + REPLACE(OBJECT_NAME(cc.parent_object_id), '''','') + '_' + REPLACE(c.name, '''', '') + ''', ''OBJECT'';' FROM sys.check_constraints AS cc INNER JOIN sys.columns AS c ON cc.parent_object_id = c.[object_id] AND cc.parent_column_id = c.column_id AND OBJECTPROPERTY(dc.parent_object_id, 'IsMsShipped') = 0; PRINT @sql; IF @PrintOnly = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END END GO 

Notez que PRINT ne révèle pas nécessairement l'intégralité de l'instruction, en fonction de vos parameters de résultats dans le text et de la taille de l'instruction. Mais il devrait être assez bon pour voir que les scripts font le bon travail. Je les ai tous PrintOnly sur PrintOnly par défaut.

Et pour renommer les foreign keys, vous pouvez utiliser quelque chose comme ça (ceci ne fait pas encore exactement ce que vous vouliez – mais assez proche pour commencer):

 DECLARE RenameFKCursor CURSOR FAST_FORWARD FOR SELECT 'dbo.sp_rename @objName = ''' + fk.Name + ''', @NewName = ''FK_' + t.Name + '_' + ref.Name + ''', @objtype = ''OBJECT''' FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id INNER JOIN sys.tables ref ON fk.referenced_object_id = ref.object_id WHERE fk.is_system_named = 1 DECLARE @RenameFKStmt NVARCHAR(500) OPEN RenameFKCursor FETCH NEXT FROM RenameFKCursor INTO @RenameFKStmt WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT @RenameFKStmt EXEC(@RenameFKStmt) END FETCH NEXT FROM RenameFKCursor INTO @RenameFKStmt END CLOSE RenameFKCursor DEALLOCATE RenameFKCursor GO 

Fondamentalement, vous itérez sur toutes les foreign keys définies dans votre database, et vous les renommez à un nom que vous décidez comment build dans le SELECT qui est la base de ce slider.

Ensuite, vous exécutez le slider sur tous les résultats et exécutez la procédure stockée dbo.sp_rename pour renommer votre contrainte FK en fonction de ce que vous voulez qu'elle soit.

En utilisant l'approche d'Aaron consistant simplement à build une seule instruction SQL énorme, vous pouvez même vous en passer sans avoir à utiliser un slider.

Ce serait le même code de renommer les contraintes par défaut "nommées par le système" à votre propre convention de nommage – il utilise la même approche que ci-dessus, un SELECT sur les vues du catalogue système, puis un slider pour parcourir toutes les inputs et build et exécutez une instruction de changement de nom SQL:

 DECLARE DFCursor CURSOR FAST_FORWARD FOR SELECT dc.Name, t.Name, c.Name FROM sys.default_constraints dc INNER JOIN sys.tables t ON dc.parent_object_id = t.object_id INNER JOIN sys.columns c ON dc.parent_column_id = c.column_id AND dc.parent_object_id = c.object_id WHERE is_system_named = 1 DECLARE @OldConstraintName sysname, @TableName sysname, @ColumnName sysname OPEN DFCursor FETCH NEXT FROM DFCursor INTO @OldConstraintName, @TableName, @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Stmt NVARCHAR(999) SET @Stmt = 'dbo.sp_rename @objName = ''' + @OldConstraintName + ''', @NewName = ''DF_' + @TableName + '_' + @ColumnName + ''', @objtype = ''OBJECT''' PRINT @Stmt EXEC (@Stmt) FETCH NEXT FROM DFCursor INTO @OldConstraintName, @TableName, @ColumnName END CLOSE DFCursor DEALLOCATE DFCursor 

Les solutions fournies seront interrompues si DB a des tables similaires dans des schémas différents. Voici ma modification de cette solution , que j'utilise.

 CREATE PROCEDURE dbo._ImplementNamingStandard @SELECT_Only BIT = 1, @PrimaryKeys BIT = 1, @ForeignKeys BIT = 1, @Indexes BIT = 1, @UniqueConstraints BIT = 1, @DefaultConstraints BIT = 1, @CheckConstraints BIT = 1 AS BEGIN SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX), @cr CHAR(2); SELECT @sql = N'', @cr = CHAR(13) + CHAR(10); DECLARE @TableLimit TINYINT, @ColumnLimit TINYINT; SELECT @TableLimit = 24, @ColumnLimit = 24; 

Clés primaires:

 IF @PrimaryKeys = 1 BEGIN SELECT @sql = @sql + @cr + @cr + N'/* ---- Primary Keys ---- */' + @cr; SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' + SCHEMA_NAME(schema_id) + '.' + REPLACE(name, '''', '''''') + ''', @newname = N''PK_' + LEFT(REPLACE(OBJECT_NAME(parent_object_id), '''', ''), @TableLimit) + ''';' FROM sys.key_constraints WHERE type = 'PK' AND is_ms_shipped = 0; END 

Clés étrangères:

 IF @ForeignKeys = 1 BEGIN SELECT @sql = @sql + @cr + @cr + N'/* ---- Foreign Keys ---- */' + @cr; SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' + SCHEMA_NAME(f.schema_id) + '.' + REPLACE(f.name, '''', '''''') + ''', @newname = N''FK_' + LEFT(REPLACE(t.name, '''', ''), @TableLimit) + '_' + LEFT(REPLACE(t2.name, '''', ''), @TableLimit) + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';' FROM sys.foreign_keys as f inner join sys.foreign_key_columns as fk on f.object_id = fk.constraint_object_id inner join sys.tables as t on fk.parent_object_id = t.object_id inner join sys.tables as t2 on fk.referenced_object_id = t2.object_id inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id WHERE f.is_ms_shipped = 0; END 

Contraintes uniques:

  IF (@UniqueConstraints = 1 OR @Indexes = 1) BEGIN SELECT @sql = @sql + @cr + @cr + N'/* ---- Indexes / Unique Constraints ---- */' + @cr; SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' + CASE is_unique_constraint WHEN 0 THEN QUOTENAME(REPLACE(OBJECT_NAME(i.[object_id]), '''', '''''')) + '.' ELSE '' END + QUOTENAME(REPLACE(i.name, '''', '''''')) + ''', @newname = N''' + CASE is_unique_constraint WHEN 1 THEN 'UQ_' ELSE 'IX_' + CASE is_unique WHEN 1 THEN 'U_' ELSE '' END END + CASE has_filter WHEN 1 THEN 'F_' ELSE '' END + LEFT(REPLACE(OBJECT_NAME(i.[object_id]), '''', ''), @TableLimit) + '_' + STUFF((SELECT '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) FROM sys.columns AS c INNER JOIN sys.index_columns AS ic ON ic.column_id = c.column_id AND ic.[object_id] = c.[object_id] WHERE ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND is_included_column = 0 ORDER BY ic.index_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') +''';' FROM sys.indexes AS i WHERE index_id > 0 AND is_primary_key = 0 AND type IN (1,2) AND OBJECTPROPERTY(i.[object_id], 'IsMsShipped') = 0; END 

Contraintes par défaut:

 IF @DefaultConstraints = 1 BEGIN SELECT @sql = @sql + @cr + @cr + N'/* ---- DefaultConstraints ---- */' + @cr; SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' + SCHEMA_NAME(schema_id) + '.' + REPLACE(dc.name, '''', '''''') + ''', @newname = N''DF_' + LEFT(REPLACE(OBJECT_NAME(dc.parent_object_id), '''',''), @TableLimit) + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';' 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 AND dc.is_ms_shipped = 0; END 

Vérifier les contraintes:

 IF @CheckConstraints = 1 BEGIN SELECT @sql = @sql + @cr + @cr + N'/* ---- CheckConstraints ---- */' + @cr; SELECT @sql = @sql + @cr + N'EXEC sp_rename @objname = N''' + SCHEMA_NAME(schema_id) + '.' + REPLACE(cc.name, '''', '''''') + ''', @newname = N''CK_' + LEFT(REPLACE(OBJECT_NAME(cc.parent_object_id), '''',''), @TableLimit) + '_' + LEFT(REPLACE(c.name, '''', ''), @ColumnLimit) + ''';' FROM sys.check_constraints AS cc INNER JOIN sys.columns AS c ON cc.parent_object_id = c.[object_id] AND cc.parent_column_id = c.column_id AND cc.is_ms_shipped = 0; END SELECT @sql; IF @SELECT_Only = 0 AND @sql > N'' BEGIN EXEC sp_executesql @sql; END