[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 473
  • Last Modified:

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
0
sath350163
Asked:
sath350163
1 Solution
 
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
 
venk_rCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now