dbo.sp_standardize_names
This procedure is useful for 'standardizing' the names of objects with auto-generated names. DBA's who write SQL update scripts to propagate DDL modifications from development database instances to QA and Production instances may prefer to 'standardize' all object names. 'Standardized' names are have the benefit of being predictable and consistent across databases.
Objects such as column defaults, primary keys, unique constraints and foreign keys can all be created without explicitly providing a name. When a name is not explicitly provided in SQL DDL, SQL Server auto-generates a name.
Auto-generated names always end with an 8 character hexadecimal suffix. This unique suffix is added to ensure that every name is UNIQUE. Non-unique names would lead to name conflicts and error's.
dbo.sp_standardize_names applies the following filters to identify auto-generated object names:
- It identifies auto-generated names as any name ending in an 8 character hexadecimal sequence
- The object type associated with the name must be one of the following: foreign key, column default, unique constraint or primary key
The following rules are used to rename objects:
- Column default names = 'df__' + table-name+'__'+ column-name +'__' + default-value. Brackets () are stripped from the default-value.
- Foreign Key names = 'fk__' + UPPER(table-name)+LOWER(first-referencing-column-name) +'__'+UPPER(referenced-table-name)+LOWER(first-referenced-column-name).
- Primary Key names = 'PK__' + table-name.
- Unique constraints = 'UQ__' + table-name + '__' + column-name.
The dbo.sp_standardize_names procedure has been designed to reside in the [master] database. Because the name starts with 'sp' it behaves as though it existed in every database. Just like the built-in system stored procedures like [sp_help].
I have updated the “p_standardize_names” procedure for compatibility with SQL 2000 & 2005.The SQL 2000 version stopped working in 2005 because many of the system tables are implimented "compatibility views". "sp" type procedures work in any database because the objects they reference are resolved at run-time and relative to the database from which the procedure is called. This dynamic resolution does not work for "compatibility views".
To get around this issue the current version is coded as a big string that is executed dynamically within the context of the current database.
The large string can be confusing to read or edit due to the need to double-escape all quoted strings. You can remove or add-back the double-escape using search & replace. Cut & past the red string constant into its own edit window. Then do a global search & replace from two single quotes to a single quote to un-escape. Do the opposite to re-escape.
This procedure returns a batch of SQL containing rename statements. I wrote a prior version of this procedure that automatically executed those rename statements. I removed the execute to force a manual review and minimize the danger of catastrophically renaming everything! (I learned that lesson the hard way)
The SQL to rename objects generated by this script should always be reviewed before executing.
Unit Test & Example
create database [test_sp_standardize_names]
go
use
[test_sp_standardize_names]
go
create schema [unit_test]
go
create table [unit_test].[family_name](
family_id int not null identity(1,1) primary key
,last_name nvarchar(50) not null default N'Doe' unique
)
create table [unit_test].[person](
person_id int identity(1,1) not null primary key
, family_id int not null references [unit_test].[family_name]( [family_id] )
, first_name nvarchar(50) not null default 'John'
)
exec dbo.sp_standardize_names
Running the dbo.sp_standardize_names procedure on the two tables created above generated the following script to rename the system generated names.
-- Rename Column Default Constraints
--------------------------------------------------------
exec sp_rename 'DF__family_na__last___5FB337D6', 'df__family_name__last_name__NDoe', 'OBJECT' --table: family_name column: last_name
exec sp_rename 'DF__person__first_na__6383C8BA', 'df__person__first_name__John', 'OBJECT' --table: person column: first_name
-- Rename Foreign Keys
--------------------------------------------------------
exec sp_rename 'FK__person__family_i__628FA481', 'FK__PERSONfamily_id__FAMILY_NAMEfamily_id', 'OBJECT'
-- Rename Primary Keys
--------------------------------------------------------
exec sp_rename 'PK__family_name__5DCAEF64', 'PK__family_name', 'OBJECT'
exec sp_rename 'PK__person__619B8048', 'PK__person', 'OBJECT'
-- Rename Unique Constraints
--------------------------------------------------------
exec sp_rename 'UQ__family_name__5EBF139D', 'UQ__family_name__last_name', 'OBJECT'
--------------------------------------------------------
dbcc freeProcCache
Procedure definition
use [master]
go
CREATE proc [dbo].[sp_standardize_names]
as
/* ---------------------------------------------------------------------
Author: Amadeus Colenbrander 03/29/2007
Functional Area: Database Administration
Purpose:
This procedure renames SQL Server names to a standard format to facilitate
the use of database diff tools. If the names are not standardized update scripts
need to be customized for each SQL server instance.
Notes: The procedure body as been "stringified" so that definition can reside in master
to edit run search and replace on string. Replace ' with '', or '' with '
----------------------------------------------------------------------- */
set nocount on
if (db_name() in ('master','model','msdb','tempdb'))
begin
print 'Error: You should only run this procedure in an application database.'
print 'Error: The current database is: ' + db_name()
return
end
declare @buf varchar(8000)
set @buf = '
select ''-- Rename Column Default Constraints'' as [sql text]
union all
select ''--------------------------------------------------------''
union all
select N'' exec sp_rename '''''' + old_name +N'''''', '''''' + new_name
+N'''''', ''''OBJECT'''' --table: '' + table_name +N'' column: ''+column_name
from (
select top 1000000
*
,N''df__''+table_name+''__''+column_name + N''__''+constraint_text as new_name
from (
select cnst_obj.name as old_name
, cnst_obj.id as constraint_id
, col.name as column_name
, tbl.name as table_name
, CAST(
replace(
replace(
replace( def.text,
''('',''''),
'')'','''' ),
'''''''', '''' )
as varchar(16)) as constraint_text
from dbo.sysobjects as cnst_obj
join dbo.sysconstraints as cnst on (cnst.constid = cnst_obj.id)
join dbo.sysobjects as tbl on (tbl.id = cnst_obj.parent_obj)
join dbo.syscolumns as col on (col.id = tbl.id)
and (col.colid = cnst.colid )
join dbo.syscomments as def on (def.id = cnst_obj.id)
where cnst_obj.name like N''DF[_][_]%[_][_][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f]''
and cnst_obj.xtype = ''D''
) as a
order by a.old_name
) as a
union all
select ''-- Rename Foreign Keys''
union all
select ''--------------------------------------------------------''
union all
select *
from (
select top 1000000
N'' exec sp_rename '''''' + old_name +N'''''', '''''' + new_name +N'''''', ''''OBJECT'''' '' as [sql text]
from (
select
fk.constid as objid
,fk_obj.name as old_name
,from_tbl.name as from_table
,from_col.name as from_column
,to_tbl.name as to_table
,to_col.name as to_column
,convert( varchar(128),
''FK__'' +UPPER(from_tbl.name) +LOWER(from_col.name)
+''__'' + UPPER(to_tbl.name) + LOWER(to_col.name)
) as new_name
from dbo.sysforeignkeys as fk
join dbo.sysobjects as fk_obj on (fk_obj.id = fk.constid) -- foriengn key name
join dbo.sysobjects as from_tbl on (from_tbl.id = fk.fkeyid) -- from table
join dbo.syscolumns as from_col on (from_col.id = fk.fkeyid)
and (from_col.colid = fk.fkey) -- from column
join dbo.sysobjects as to_tbl on (to_tbl.id = fk.rkeyid) -- to table
join dbo.syscolumns as to_col on (to_col.id = fk.rkeyid)
and (to_col.colid = fk.rkey) -- to column
where fk_obj.name like N''FK[_][_]%[_][_][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f]''
) as a
order by new_name
) as a
union all
select ''-- Rename Primary Keys''
union all
select ''--------------------------------------------------------''
union all
select *
from (
select top 1000000
N'' exec sp_rename '''''' + old_name +N'''''', '''''' + new_name +N'''''', ''''OBJECT'''' '' as [sql text]
from (
select
object_name( cnst.parent_obj ) as table_name
,cnst.name as old_name
,rtrim(cnst.xtype) +N''__'' + object_name(cnst.parent_obj) as new_name
from dbo.sysobjects as cnst
where cnst.xtype = ''PK''
and cnst.name like N''PK[_][_]%[_][_][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f]''
) as a
order by new_name
) as a
union all
select ''-- Rename Unique Constraints''
union all
select ''--------------------------------------------------------''
union all
select *
from (
select top 1000000
N'' exec sp_rename '''''' + old_name +N'''''', '''''' + new_name +N'''''', ''''OBJECT'''' '' as [sql text]
from (
select
object_name( cnst.parent_obj ) as table_name
,cnst.name as old_name
,rtrim(cnst.xtype) +N''__'' + object_name(cnst.parent_obj) +N''__'' + col.name as new_name
from dbo.sysobjects as cnst
join dbo.sysindexes as ix on (ix.id = cnst.parent_obj)
and (ix.name = cnst.name)
join dbo.sysindexkeys as [key] on ([key].id = ix.id)
and ([key].indid = ix.indid)
and ([key].keyno = 1 )
join dbo.syscolumns as col on (col.id = ix.id)
and (col.colid = [key].colid)
where cnst.xtype = ''UQ''
and cnst.name like N''UQ[_][_]%[_][_][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f][0-9,a-f]''
) as a
order by new_name
) as a
union all
select ''--------------------------------------------------------''
union all
select ''dbcc freeProcCache''
'
exec( @buf )
return