Technical Article

Standardize Object Names

,

If you let SQL decide on your contraint and key names, a lot of times you'll end up with non-intuitive names. This script isolates those non-conventional names and creates a script that will rename them using sp_rename.

If you want to change the naming convention to meet your requirements, change the part of the script that aliases the [ChangeTSQL] column, as well as the 'WHERE' clauses.

/*
//Standardize database object names
//Cody Allen
//5.13.2015
*/
/*
//Generates a list of all default constraints with their table and referenced column. 
//Creates a rename script to standardize constraint name to DF_TableName_ColumnName
//
//If there are incorrectly named constraints that already use the table name and different column name, run twice.
*/
SELECT s.name AS [SchemaName],
   t.name AS [TableName],
   c.name AS [ColumnName],
   dc.name AS [ContraintName],
   'sp_rename ''' + s.name + '.' + dc.name +'''' + ', ''' + 'DF_' + t.name + '_' + c.name + ''';' + CHAR(10) + 'GO' AS ChangeTSQL
FROM sys.default_constraints dc
INNER JOIN sys.columns c
ON dc.parent_object_id = c.object_id
   AND dc.parent_column_id = c.column_id
INNER JOIN sys.tables t
ON t.object_id = c.object_id
INNER JOIN sys.schemas s
ON s.schema_id = t.schema_id
WHERE dc.name <> 'DF_' + t.name + '_' + c.name
ORDER BY t.name,c.name

/*
//Generates a list of all the foreign keys with their source table and column, and referenced table and column
//Creates a rename script to standardize foreign key name to FK_SourceTable_SourceColumn_ReferencedTable_ReferencedColumn
//
//If there is a foreign key that references two or more columns, they will be duplicated in this list and take only the name of the first referenced column
*/
SELECT o.name AS [FKName],
   s.name AS [SchemaName],
   t1.name AS [TableName],
   c1.name AS [ColumnName],
   t2.name AS [RefTableName],
   c2.name AS [RefColName],
   'sp_rename ''' + s.name + '.' + o.name + '''' + ',''' + 'FK_' + t1.name + '_' + c1.name + '_' + t2.name + '_' + c2.name + ''';' + CHAR(10) + 'GO' AS ChangeTSQL

FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects o
ON o.object_id = fkc.constraint_object_id
INNER JOIN sys.tables t1
ON t1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas s
ON t1.schema_id = s.schema_id
INNER JOIN sys.columns c1
ON c1.column_id = fkc.parent_column_id
   AND c1.object_id = t1.object_id
INNER JOIN sys.tables t2
ON t2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns c2
ON c2.column_id = fkc.referenced_column_id
   AND c2.object_id = t2.object_id
WHERE o.name <> 'FK_' + t1.name + '_' + c1.name + '_' + t2.name + '_' + c2.name
/*
//Generates a list of all the primary keys with their source table.
//Creates a rename script to standardize primary key name to PK_SourceTable
*/
SELECT kc.name,
   s.name,
   t.name,
   'sp_rename ''' + s.name + '.' + kc.name + '''' + ',''' + 'PK_' + t.name + ''';' + CHAR(10) + 'GO' AS ChangeTSQL
    FROM sys.key_constraints kc
INNER JOIN sys.objects o
ON o.object_id = kc.object_id
INNER JOIN sys.tables t
ON t.object_id = kc.parent_object_id
INNER JOIN sys.schemas s
ON s.schema_id = kc.schema_id
WHERE kc.name <> 'PK_' + t.name

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating