Tuesday, March 8, 2011

Get default constraint name for Table/Column (SQL Server 2005/2008)

Recently I had to change an application where the lazy bum before me had hard coded foreign keys with value=1 as column defaults. The guy assumed that the referenced default row must/will always be created with primary key = 1. Such sloppiness makes you wanna go postal. So, here is how I dealt with the issue without making extensive modifications to the code: 1. Determine the constraint name.
if object_id('[dbo].[GetDefaultConstraintName]') is not null
    drop function [dbo].[GetDefaultConstraintName]
go

create function [dbo].[GetDefaultConstraintName] (
    @TableName varchar(max),
    @ColumnName varchar(max))
returns varchar(max)
as
begin

    -- Returns the name of the default constraint for a column

    declare @Command varchar(max)
    select 
        @Command = d.name
    from 
        ((
        sys.tables t join
        sys.default_constraints d
            on 
                d.parent_object_id = t.object_id) join
        sys.columns c
            on 
                c.object_id = t.object_id and 
                c.column_id = d.parent_column_id)
    where 
        t.name = @TableName and 
        c.name = @ColumnName
    return @Command
end
go
2. Generate dynamic sql to drop the constraint. Assume that the table name is MyTable and the column name is MyColumn.
execute ('alter table MyTable drop constraint '+
           [dbo].[GetDefaultConstraintName]('MyTable','MyColumn'))
3. Re-create the constraint with the desired default value. Assume that the new default value should be 33.
alter table MyTable add constraint [DF_MyTable_MyColumn] default (33) for MyColumn

1 comment: