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
ċ
Standardize primary key names across the database.sql
(0k)
Andy Hughes,
Jun 11, 2012, 12:35 PM
Comments