Skip to content

SqlServer Migrations: Simplify drop default constraint query #34620

Open
@bricelam

Description

@bricelam

Current query:

DECLARE @var1 sysname;
SELECT @var1 = [d].[name]
FROM [sys].[default_constraints] [d]
INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
WHERE ([d].[parent_object_id] = OBJECT_ID('[someSchema].[SomeTable]') AND [c].[name] = 'SomeColumn');
IF @var1 IS NOT NULL EXEC('ALTER TABLE [someSchema].[SomeTable] DROP CONSTRAINT [' + @var1 + '];');

Simpler query:

DECLARE @var1 sysname;
SELECT @var1 = OBJECT_NAME([c].[default_object_id])
FROM [sys].[columns] [c]
WHERE [c].[object_id] = OBJECT_ID('[someSchema].[SomeTable]') AND [c].[name] = 'SomeColumn';
IF @var1 IS NOT NULL EXEC('ALTER TABLE [someSchema].[SomeTable] DROP CONSTRAINT [' + @var1 + ']');

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions