SQL Server batch modify database table PK name to PK table name

Keywords: SQL Sever SQL

1. When we create the primary key of the data table of SQL server, sometimes a string of random strings will be added, as shown in the figure:

 

2. If you have obsessive-compulsive disorder, you can use the following sql script to modify the name of the primary key to the PK UU table name

--Unify the primary key name of the table as PK_Table name
  
DECLARE @PK_Name_Old varchar(100),@Table_Name varchar(100),@PK_Name_New varchar(100),@Col_Name varchar(100)
DECLARE Cursor_Update_Table_PK_Name CURSOR FOR

SELECT 
  a.name AS 'tbl_name',
  e.name AS 'col_name',
  b.name,
  'PK_'+a.name as 'new_pk_name'
FROM sysobjects AS a          --Object table, combining a.xtype='U'Conditions, check user table
 LEFT JOIN sysobjects AS b --Object table, combining b.xtype='PK'Condition, check primary key constraint
  ON a.id=b.parent_obj
 LEFT JOIN sysindexes AS c --Index table, query the corresponding field index according to the (primary key) constraint name matching
  ON a.id=c.id AND b.name=c.name
 LEFT JOIN sysindexkeys AS d --The table of the corresponding key and column in the index, according to index matching, query the field id
  ON a.id=d.id AND c.indid=d.indid
 LEFT JOIN syscolumns AS e --Field table, based on fields id Match, check field name
  ON a.id=e.id AND d.colid=e.colid
WHERE a.xtype='U'
  AND b.xtype='PK'
  
OPEN Cursor_Update_Table_PK_Name
FETCH NEXT FROM Cursor_Update_Table_PK_Name INTO @Table_Name,@Col_Name,@PK_Name_Old,@PK_Name_New
WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT ''+@Table_Name
			-- Delete if there is a primary key constraint 
			--IF EXISTS(SELECT * FROM sysobjects WHERE name=@PK_Name_Old and xtype='PK')
			
			Exec('Alter TABLE '+@Table_Name+' Drop Constraint '+@PK_Name_Old)
			
			 -- Add primary key constraint again
			Exec('ALTER TABLE '+@Table_Name+' ADD CONSTRAINT '+@PK_Name_New+' PRIMARY KEY ('+@Col_Name+') ')
			
		FETCH NEXT FROM Cursor_Update_Table_PK_Name INTO @Table_Name,@Col_Name,@PK_Name_Old,@PK_Name_New
	END
CLOSE Cursor_Update_Table_PK_Name
DEALLOCATE Cursor_Update_Table_PK_Name

Use the above code to batch modify the primary key name of the table

3. you can modify the above scripts, such as adding a string to the front of the table in batches. You will know how to do it cleverly.

Posted by ollie007 on Wed, 08 Jan 2020 07:55:49 -0800