WebProfIT Consulting
  • Home
  • Cloud
  • SQL
  • Big Data
  • Contact Us
  • RSS

Categories

  • AWS (1)
  • Big Data (1)
  • Cloud (1)
  • SQL (4)
    • Microsoft SQL Server (3)
  • Tips and Tricks (2)
  • Tutorials (1)

Recent Comments

    • Home
    • Microsoft SQL Server
    • SQL
    • Renaming Primary Keys to Standard

    Renaming Primary Keys to Standard

    2016-09-03 Leave a Comment Written by Pavel Danilov

    We prefer to name all primary key constraints explicitly and not let SQL server come up with its own names. In this case:

    1. We always know what name to look for.
    2. Standard name, which in our case looks like PK_tableName, simplifies automatic maintenance of primary keys: if all names are standard, they can be used in stored procedures and triggers with no need of lookup in metadata, which significantly simplifies maintenance SQL scripting.

    Script below goes through the list of database primary keys and renames them all to standard. We recommend to run this procedure once in a while manually or as a maintenance job, because sometimes users forget about explicit naming of constraints and default constraint names assigned by SQL server tend to accumulate in the database.

    You can download the script from Dropbox location, no need to copy/paste it.

    IF EXISTS (
     SELECT 1 FROM sys.objects
     WHERE name='usp_Util_RenameConstraintsPK')
     DROP PROCEDURE [dbo].[usp_Util_RenameConstraintsPK]
     GO
    
    CREATE PROCEDURE [dbo].[usp_Util_RenameConstraintsPK]
     AS
     BEGIN
     SET NOCOUNT ON;
    
    DECLARE @constraintName NVARCHAR(4000)
     DECLARE @tableName NVARCHAR(100)
     DECLARE @colName NVARCHAR(100)
     DECLARE @newName NVARCHAR(4000)
    
    DECLARE BADPK CURSOR FOR
     SELECT cons.name, tab.name, col.name
     FROM sys.key_constraints cons, sys.index_columns indcol,
     sys.columns col, sys.tables tab, sys.indexes idx
     WHERE cons.parent_object_id = indcol.object_id
     AND cons.parent_object_id = tab.object_id
     AND cons.type='PK'
     AND cons.parent_object_id = col.object_id
     AND indcol.column_id = col.column_id
     AND indcol.is_included_column=0
     AND indcol.object_id = idx.object_id
     AND indcol.index_id = idx.index_id
     AND idx.type = 1
     AND indcol.column_id = 1
     ORDER BY tab.name, col.name, indcol.index_column_id
    
    DECLARE @q VARCHAR(4000)
     DECLARE @prevConstraintName NVARCHAR(100)
     DECLARE @prevTableName NVARCHAR(100)
    
    OPEN BADPK
     FETCH NEXT FROM BADPK INTO @constraintName,
     @tableName, @colName
    
    WHILE @@FETCH_STATUS = 0
     BEGIN
     SET @newName = 'PK_' + @tableName
     SET @q = 'EXEC sp_rename ''' + @constraintName +
     ''', ''' + @newName + ''''
     PRINT @q
     EXEC (@q)
     FETCH NEXT FROM BADPK INTO @constraintName,
     @tableName, @colName
     END
    
    CLOSE BADPK
     DEALLOCATE BADPK
     END
     GO
    
    Microsoft SQL Server, SQL
    primary key, SQL, SQL Server
    INCLUDE Clause in Non-clustered Indexes
    Data Normalization

    Leave a Reply Cancel reply

    You must be logged in to post a comment.

    Recent Posts

    • How to shrink size of Linux EBS volume in AWS
    • Data Normalization
    • Renaming Primary Keys to Standard
    • INCLUDE Clause in Non-clustered Indexes
    • Using a DDL trigger to control and automate history of data changes

    Archives

    WebProfIT Consulting, Inc. 2016