Cool Tips‎ > ‎Data Formatting‎ > ‎

Data Formatting Standardise PK and FK Names

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        11 May 2010

Description

When creating primary or foreign keys using TSQL or the GUI, there is currently no way of enforcing a naming convention at creation time - the system automatically generates a name.  A separate command must be run afterwards to rename the constraint.  
Most ER Design tools offer the option of providing or enforcing a naming convention at design time, before scripts are generated and rolled out, but not every DBA shop can afford these often pricy additions to the arsenal.
 
I find it very helpful, not only on a personal level, but for development teams, to have a standard naming convention that allows easy and immediate understanding of the PK/FK relationship scope and direction
 
The following two scripts will generate TSQL commands for changing all PK and FK names in a database.
They are written to enforce the following standard:
 
  • Primary Key:     PK_<TableName>
  • ForeignKey:      FK_<ParentTableName>_<ChildTableName>
but may be easily altered to whatever standard that you use.

Code

Standardise Primay Key Names:

SELECT
     'EXEC sp_rename '
   + '''['
   + CCU.TABLE_SCHEMA
   + '].['
   + CCU.CONSTRAINT_NAME
   + ']'',''PK_'
   + CCU.TABLE_NAME
   + ''',''OBJECT'''
   + CHAR(10)
   + 'GO'
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
   ON CCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'  

 
 

Standardise Foreign Key Names:

SELECT
     'EXEC sp_rename '
   + '''['
   + CCU.TABLE_SCHEMA
   + '].['
   + CCU.CONSTRAINT_NAME
   + ']'',''FK_'
   + CCU.TABLE_NAME
   + '_'
   + TC1.TABLE_NAME
   + ''',''OBJECT'''
   + CHAR(10)
   + 'GO'
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
   ON CCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
   ON TC.CONSTRAINT_NAME=RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC1
   ON RC.UNIQUE_CONSTRAINT_NAME=TC1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE='FOREIGN KEY' 




ċ
Standardize foreign key names across the database.sql
(1k)
Andy Hughes,
Jun 11, 2012, 12:35 PM
v.1
ċ
Standardize primary key names across the database.sql
(0k)
Andy Hughes,
Jun 11, 2012, 12:35 PM
v.1
Comments