A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Friday, June 30, 2017

Rename default constraints

This week I'm dealing with synchronizing tables between environments and it seems that regardless of what tool I'm using for schema compare, it still gets hung up on the differences in default names for constraints. Rather than fight that battle, I figured it'd greatly simplify my life to systematically rename all my constraints to non default names. The naming convention I went with is DF__schema name_table name_column name. I know that my schemas/tables/columns don't have spaces or "weird" characters in them so this works for me. Use this as your own risk and if you are using pre-2012 the CONCAT call will need to be adjusted to classic string concatenation, a.k.a. +
DECLARE @query nvarchar(4000);
DECLARE
    CSR CURSOR
FAST_FORWARD
FOR
SELECT
    CONCAT('ALTER TABLE ', QUOTENAME(S.name), '.', QUOTENAME(T.name), ' DROP CONSTRAINT [', DC.name, '];', CHAR(10)
    , 'ALTER TABLE ', QUOTENAME(S.name), '.', QUOTENAME(T.name)
    , ' ADD CONSTRAINT [', 'DF__', (S.name), '_', (T.name), '_', C.name, ']'
    , ' DEFAULT ', DC.definition, ' FOR ', QUOTENAME(C.name)) AS Query
FROM
    sys.schemas AS S
    INNER JOIN
        sys.tables AS T
        ON T.schema_id = S.schema_id
    INNER JOIN
        sys.columns AS C
        ON C.object_id = T.object_id
    INNER JOIN
        sys.default_constraints AS DC
        ON DC.parent_object_id = T.object_id
        AND DC.object_id = C.default_object_id
WHERE
    DC.name LIKE 'DF__%'
    AND DC.name <> CONCAT('DF__', (S.name), '_', (T.name), '_', C.name);

OPEN CSR
FETCH NEXT FROM CSR INTO @query;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXECUTE sys.sp_executesql @query, N'';
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
        PRINT @query;
    END CATCH
    FETCH NEXT FROM CSR INTO @query;
END
CLOSE CSR;
DEALLOCATE CSR;

2 comments:

Robert said...

If you have containment type set to partial, this will fail with the error as below.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the concat operation.

The error will vary depending on the database collation. In this example, the database collation is SQL_Latin1_General_CP1_CI_AS.

The query for the cursor definition can be changed as below where the database collation is SQL_Latin1_General_CP1_CI_AS. This collation will need to be set to match the database collation in your environment.

SELECT
CONCAT(N'ALTER TABLE '
, QUOTENAME(S.name) COLLATE SQL_Latin1_General_CP1_CI_AS
, N'.'
, QUOTENAME(T.name) COLLATE SQL_Latin1_General_CP1_CI_AS
, N' DROP CONSTRAINT '
, QUOTENAME(DC.name) COLLATE SQL_Latin1_General_CP1_CI_AS
, N';'
, CHAR(10)
, N'ALTER TABLE '
, QUOTENAME(S.name) COLLATE SQL_Latin1_General_CP1_CI_AS
, N'.'
, QUOTENAME(T.name) COLLATE SQL_Latin1_General_CP1_CI_AS
, N' ADD CONSTRAINT [DF_'
, S.name COLLATE SQL_Latin1_General_CP1_CI_AS
, N'_'
, T.name COLLATE SQL_Latin1_General_CP1_CI_AS
, N'_'
, C.name COLLATE SQL_Latin1_General_CP1_CI_AS
, N']'
, N' DEFAULT '
, DC.definition
, N' FOR '
, QUOTENAME(C.name) COLLATE SQL_Latin1_General_CP1_CI_AS
) AS Query
FROM
sys.schemas AS S
INNER JOIN sys.tables AS T ON T.schema_id = S.schema_id
INNER JOIN sys.columns AS C ON C.object_id = T.object_id
INNER JOIN sys.default_constraints AS DC ON DC.parent_object_id = T.object_id
AND DC.object_id = C.default_object_id
WHERE
S.name = N'dbo'
AND DC.name COLLATE SQL_Latin1_General_CP1_CI_AS <> CONCAT(N'DF_'
, S.name COLLATE SQL_Latin1_General_CP1_CI_AS
, N'_'
, T.name COLLATE SQL_Latin1_General_CP1_CI_AS
, N'_'
, C.name COLLATE SQL_Latin1_General_CP1_CI_AS
);

Bill Fellows said...

Thank you for the catch. The database I was dealing with only had SQL_Latin1_General_CP1_CI_AS columns