Skip to content

Instantly share code, notes, and snippets.

@jujiro
Created May 20, 2019 16:49
Show Gist options
  • Save jujiro/b893c4f6cf03e11626b122eb35a6bb35 to your computer and use it in GitHub Desktop.
Save jujiro/b893c4f6cf03e11626b122eb35a6bb35 to your computer and use it in GitHub Desktop.
Find default constraint names for tables (Sql Server)
/*
Sql server will not let you dropa table if it has default contraints (like default values for columns.)
This script will let you find them.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment