How to rename database objects to comply with naming conventions

We have guidelines for the naming conventions of our database objects.  It’s pretty much the common pattern you see all over the place. For example, we use the following convention for default constraints:

DF_TableName_ColumnName, such as DF_Student_School

Pinal Dave has great set of database coding standards on his site, more in-depth than ours actually.  We have found that using coding standards makes the database schema easier to read and makes it easier to perform schema updates.  If you don’t explicitly name a default, SQL Server will name it for you.  And it wont be pretty or consistent across databases.  We have our own tool for pushing out schema updates to our customers and it assumes the object names will be the same for each database.

When we submit our schema updates internally, we usually catch any deviation from our naming conventions.  It’s not a perfect process and every now and then, something slips through the cracks.  We then correct the schema update to use the appropriate naming convention.  if we have been using the schema changes internally, we may have some databases that don’t match the published schema for the object names.  When that happens, we run a simple T-SQL script that cleans house.  The following T-SQL will scan the database for default constraints that do not match our naming conventions and rename the ones that it finds.

DECLARE @OldName nvarchar(512)
DECLARE @NewName nvarchar(512)
DECLARE @OldToNewName nvarchar(512)

-- We use a table variable to contain the list of objects to be renamed.
DECLARE @FixSchema TABLE(
    sys_OldName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    sys_NewName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

-- Get the list of current column defaults
-- The OldName column represents the current name of the default
-- The NewName column represents the name of the default using 
-- our naming convention
INSERT INTO  @FixSchema(sys_OldName, sys_NewName)
select o.name AS OldName
,'DF_' + Object_Name(o.parent_obj) + '_' + c.Name AS NewName
FROM sysobjects o
JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
WHERE o.type = 'D' 

-- Declare cursor on this table variable to access on the non-compliant
-- constraints
DECLARE AddDrop CURSOR FOR
SELECT sys_OldName, sys_NewName
FROM @FixSchema
where sys_OldName <> sys_NewName

OPEN AddDrop

-- Loop through the list of defaults where the default name is not 
-- strongly typed
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Assenble a T-SQL command that can be executed dynamically 
-- to rename the constraint
SELECT @OldToNewName = 'sp_rename ' + QUOTENAME(@OldName,'''') + 
', ' + QUOTENAME(@NewName,'''') + ', ' + QUOTENAME('object','''')

-- Print the command to be executed.  Useful for seeing 
-- what is being done
PRINT @OldToNewName

-- Execute the T-SQL to rename the object.  If the EXEC line is
-- commented out, the code will display what would be changed 
-- with making any actual changes to the schema
EXEC(@OldToNewName)
FETCH NEXT
FROM AddDrop
INTO @OldName, @NewName
END

-- Close and deallocate the cursor
CLOSE AddDrop
DEALLOCATE AddDrop

I usually use the INFORMATION_SCHEMA views to peek at the table and column structures.  The INFORMATION_SCHEMA.COLUMNS view will tell which columns have default constraints, but not the name of the constraint. In this case I needed to access the sysobjects table to retrieve the name of the constraint.  The same type of code can be used to rename other objects like check constraints.