Created
May 20, 2019 16:49
-
-
Save jujiro/b893c4f6cf03e11626b122eb35a6bb35 to your computer and use it in GitHub Desktop.
Find default constraint names for tables (Sql Server)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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