USE master
GO
IF OBJECT_ID('sp_RenameKeys') IS NULL EXEC ('CREATE PROCEDURE sp_RenameKeys AS SELECT 1 AS ID')
GO
ALTER PROCEDURE sp_RenameKeys
@RenameFK BIT = 1,
@RenamePK BIT = 1,
@RenameUnique BIT = 1,
@RenameCheck BIT = 1,
@RenameDefault BIT = 1
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @FKSQL VARCHAR(MAX),
@PKSQL VARCHAR(MAX),
@UNIQSQL VARCHAR(MAX),
@CheckSQL VARCHAR(MAX),
@DefaultSQL VARCHAR(MAX),
@SQL VARCHAR(MAX)
IF @RenameFK = 1
BEGIN
IF OBJECT_ID('tempdb..#ForeignKeys') IS NOT NULL
DROP TABLE #ForeignKeys ;
WITH Step1
AS (SELECT TOP 99999999
pt.OBJECT_ID AS ParentObjectId,
ps.name AS ParentSchema,
pt.name AS ParentTable,
pc.name AS ParentColumn,
fc.constraint_column_id AS ColumnId,
fk.name AS ForeignKey,
rt.OBJECT_ID AS ReferencedObjectId,
rs.name AS ReferencedSchema,
rt.name AS ReferencedTable,
rc.name AS ReferencedColumn,
fk.is_disabled AS IsDisabled,
fk.is_not_trusted AS IsNotTrusted,
fk.is_system_named AS IsSystemNamed
FROM sys.foreign_keys fk (NOLOCK)
INNER JOIN sys.tables pt (NOLOCK) ON fk.parent_object_id = pt.OBJECT_ID
INNER JOIN sys.schemas ps (NOLOCK) ON ps.SCHEMA_ID = pt.SCHEMA_ID
INNER JOIN sys.columns pc (NOLOCK) ON pc.OBJECT_ID = pt.OBJECT_ID
INNER JOIN sys.tables rt (NOLOCK) ON fk.referenced_object_id = rt.OBJECT_ID
INNER JOIN sys.schemas rs (NOLOCK) ON rs.SCHEMA_ID = rt.SCHEMA_ID
INNER JOIN sys.columns rc (NOLOCK) ON rc.OBJECT_ID = rt.OBJECT_ID
INNER JOIN sys.foreign_key_columns fc (NOLOCK) ON fc.constraint_object_id = fk.OBJECT_ID
AND fc.parent_column_id = pc.column_id
AND fc.referenced_column_id = rc.column_id
WHERE fk.is_ms_shipped = 0
ORDER BY pt.OBJECT_ID,
ps.name,
pt.name,
fk.name,
rs.name,
rt.name,
fk.is_disabled,
fk.is_not_trusted,
fk.is_system_named,
fc.constraint_column_id)
SELECT ParentSchema,
ParentTable,
ForeignKey,
COUNT(*) AS ColumnCount,
--ParentTable + '_' + ReferencedTable + '_FK' AS DefaultName,
ParentTable + '$' + Util.dbo.StringConcat(ParentColumn, '$') + '_' + ReferencedTable + '_FK' AS NewName,
Util.dbo.StringConcat('[' + ParentColumn + ']', ', ') AS ParentColumns,
ReferencedSchema,
ReferencedTable,
Util.dbo.StringConcat('[' + ReferencedColumn + ']', ', ') AS ReferencedColumns,
IsDisabled,
IsNotTrusted,
IsSystemNamed
INTO #ForeignKeys
FROM Step1
GROUP BY ParentObjectId,
ParentSchema,
ParentTable,
ForeignKey,
ReferencedSchema,
ReferencedTable,
IsDisabled,
IsNotTrusted,
IsSystemNamed
SELECT @FKSQL = Util.dbo.StringConcat('EXEC sp_rename ''[' + ParentSchema + '].[' + ForeignKey + ']'', ''' + NewName + ''', ''OBJECT''
GO
', '')
FROM #ForeignKeys
WHERE ForeignKey <> NewName
END
IF @RenamePK = 1
BEGIN
SELECT @PKSQL = Util.dbo.StringConcat(DISTINCT 'EXEC sp_rename ''[' + S.name + '].[' + b.name + ']'', ''' + a.name + CASE WHEN b.type = 1 THEN '_PKC'
ELSE '_PK'
END + ''', ''OBJECT''
GO
', '')
FROM sys.objects a (NOLOCK)
INNER JOIN sys.indexes b (NOLOCK) ON a.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.schemas S (NOLOCK) ON a.SCHEMA_ID = S.SCHEMA_ID
WHERE a.OBJECT_ID = b.OBJECT_ID
AND (b.is_primary_key = 1)
AND a.is_ms_shipped = 0
AND a.type = 'U'
AND b.name <> a.name + CASE WHEN b.type = 1 THEN '_PKC'
ELSE '_PK'
END
END
IF @UNIQSQL = 1
BEGIN
DECLARE @ExcludeDiabloExceptions BIT = 1 ;
WITH uniq
AS (SELECT s.name AS SchemaName,
a.name AS TableName,
b.name AS IndexName,
ROW_NUMBER() OVER (PARTITION BY a.OBJECT_ID ORDER BY b.index_id) AS UniqueKeyId,
b.index_id
FROM sys.objects a (NOLOCK)
INNER JOIN sys.indexes b (NOLOCK) ON a.OBJECT_ID = b.OBJECT_ID
INNER JOIN sys.schemas S (NOLOCK) ON a.SCHEMA_ID = S.SCHEMA_ID
WHERE a.OBJECT_ID = b.OBJECT_ID
AND (b.is_unique_constraint = 1)
AND a.is_ms_shipped = 0
AND a.type = 'U')
SELECT @UNIQSQL = Util.dbo.StringConcat(DISTINCT 'EXEC sp_rename ''[' + SchemaName + '].[' + IndexName + ']'', ''' + NewIndexName + ''', ''OBJECT''
GO
', '')
FROM uniq
CROSS APPLY (SELECT TableName + '_UNIQ' + CASE WHEN index_id = 1 THEN 'C'
ELSE ''
END + CASE WHEN UniqueKeyId = 1 THEN ''
ELSE '_' + CAST(UniqueKeyId AS VARCHAR)
END AS NewIndexName) u
WHERE u.NewIndexName <> IndexName
AND (@ExcludeDiabloExceptions = 0
OR (@ExcludeDiabloExceptions = 1
AND IndexName NOT IN ('OPtOutSource_Description_UNIQ', 'OPtOutSource_OptSource_UNIQ')))
END
IF @RenameCheck = 1
BEGIN
SELECT @CheckSQL = Util.dbo.StringConcat('EXEC sp_rename ''[' + SchemaName + '].[' + CheckConstraint + ']'', ''' + cn.CheckName + ''', ''OBJECT''
GO
', '')
FROM Metadata.CheckConstraints c (NOLOCK)
CROSS APPLY (SELECT TableName + '_' + REPLACE(Columnlist, ', ', '$') + CASE WHEN CheckConstraint LIKE '%validyear%' THEN '_ValidYear'
WHEN CHARINDEX('AllSeq_gt_0', CheckConstraint) > 0 THEN '_AllSeqGt0'
WHEN CHARINDEX('_NotBlank', CheckConstraint) > 0 THEN '_NotBlank'
WHEN CHARINDEX('_AuditEnabled', CheckConstraint) > 0 THEN '_AuditEnabled'
WHEN Definition = '([' + c.ColumnList + ']>(0))' THEN '_GreaterThan0'
END + '_CK' AS CheckName) cn
WHERE CHARINDEX(cn.CheckName, c.CheckConstraint) = 0
END
IF @RenameDefault = 1
BEGIN
SELECT @DefaultSQL = Util.dbo.StringConcat('EXEC sp_rename ''[' + SchemaName + '].[' + DefaultName + ']'', ''' + NewName + ''', ''OBJECT''
GO
', '')
FROM Metadata.Defaults
CROSS APPLY (SELECT TableName + '$' + ColumnName + CASE definition
WHEN '((0))' THEN '_Zero'
WHEN '((1))' THEN '_One'
WHEN '(CONVERT([datetime],''19000101'',(0)))' THEN '_19000101'
WHEN '(getdate())' THEN '_GETDATE'
END + '_DF' AS NewName) AS nd
WHERE NewName <> DefaultName
END
SET @SQL = ISNULL('-- FOREIGN KEYS
' + @FKSQL + '
', '') + ISNULL('-- PRIMARY KEYS
' + @PKSQL + '
', '') + ISNULL('-- UNIQUE KEYS
' + @UNIQSQL + '
', '') + ISNULL('-- CHECK CONSTRAINTS
' + @CheckSQL + '
', '') + ISNULL('-- DEFAULTS
' + @DefaultSQL + '
', '')
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject sp_RenameKeys
GO