Renaming all constraint names with user defined names

Hello,

How do I query the database for all types of constraints such as Primary Key,Foreign Key, Unique Key, and Default Constraint, and rename its system generate name to a name in following format:

- PK<TableName>_ColumnName1_ColumnName2
- FK<TableName>_ColumnName1
- UK<TableName>_ColumnName1_ColumnName2
- DF<TableName>_ColumnName1
sath350163Asked:
Who is Participating?
 
venk_rConnect With a Mentor Commented:
Please see below to rename FK and PK dynamically.Let me know if you have question
--Rename FK

DECLARE curFK CURSOR FAST_FORWARD FOR
 SELECT so.object_id,
 so.name,
 child.name as ChildName,
 parent.name as ParentName,
 OBJECT_SCHEMA_NAME(child.object_id) as schema1
 FROM SysForeignKeys sfk
 INNER JOIN Sys.Objects so ON sfk.constid = so.object_id
 INNER JOIN Sys.Objects child ON sfk.fkeyid = child.object_id
 INNER JOIN Sys.Objects parent ON sfk.rkeyid = parent.object_id
 WHERE so.name <> child.name + '_' + parent.name + '_'
 + CAST(so.object_id AS VARCHAR(20)) + '_FK'
 
DECLARE @soid BIGINT, @name VARCHAR(1000), @childname VARCHAR(1000),
@parentname VARCHAR(1000), @sql NVARCHAR(4000),@schema VARCHAR(1000)
OPEN curFK
FETCH NEXT FROM curFK INTO @soid, @name, @childname, @parentname,@schema
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'exec sp_rename ' + char(39) + @schema +'.' + @name + char(39) + ', ' +
 CHAR(39) + @childname + '_' + @parentname + '_' +
 CAST(@soid as varchar(20)) + '_FK'+ char(39)
PRINT @sql
EXECUTE sp_executesql @sql
FETCH NEXT FROM curFK INTO @soid, @name, @childname, @parentname,@schema
END
CLOSE curFK
DEALLOCATE curFK

--Rename PK

DECLARE curPK CURSOR FAST_FORWARD FOR
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) as TableName
FROM sys.objects
WHERE type_desc IN ('PRIMARY_KEY_CONSTRAINT')

DECLARE @soid BIGINT, @name VARCHAR(1000), @childname VARCHAR(1000),
@parentname VARCHAR(1000), @sql NVARCHAR(4000),@schema VARCHAR(1000)
OPEN curPK
FETCH NEXT FROM curPK INTO  @name,@schema,@parentname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'exec sp_rename '+ char(39) + @schema+ '.'+ @name + char(39)+ ','+ char(39) + 'PK_'+ @parentname + char(39)


EXECUTE sp_executesql @sql
FETCH NEXT FROM curPK INTO @name,@schema,@parentname
END
CLOSE curPK
DEALLOCATE curPK
0
 
momi_sabagCommented:
you can rename using sp_rename
you can query the information_schema tables in order to generate the commands you want to run
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.