At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!
COURSE of the MONTH
11 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS SQL DROP DEFAULT CONSTRAINT
Posted on 2014-12-27
I noticed, from the object sys.default_constraints, that MS SQL generates individual default constraints for each field set with a default value.
Please, note that I set the default values with one Alter table table1 add constraint tableconstr1 default ... for ...., and this statement was built using information from information_schema.columns
Is there a way to drop all the default constraints generated automatically by MS SQL with one statement like
ALTER table table1 DROP CONSTRAINT tableconstr1 (name previously used in the statement above).
Thanks in advance for your time.
Please, note that I set the default values with one Alter table table1 add constraint tableconstr1 default ... for ...., and this statement was built using information from information_schema.columns
Is there a way to drop all the default constraints generated automatically by MS SQL with one statement like
ALTER table table1 DROP CONSTRAINT tableconstr1 (name previously used in the statement above).
Thanks in advance for your time.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
6 Comments
Active 2 days ago
Is there a way to drop all the default constraints generated automatically by MS SQL with one statement
As the syntax for ALTER TABLE shows you can drop as many constraints as you like with one statement.
As the syntax for ALTER TABLE shows you can drop as many constraints as you like with one statement.
Active 6 days ago
@Portlet Paul,
Since the link that was providing an answer to my question is deleted, how can I recover it.
Since the link that was providing an answer to my question is deleted, how can I recover it.
to drop default constraint that you don't knowing its name:
you can also refer to this link:
DROP DEFAULT (Transact-SQL)
declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N’Department’
set @col_name = N’ModifiedDate’
select t.name, c.name, d.name, d.definition
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 = @table_name
and c.name = @col_name
you can also refer to this link:
DROP DEFAULT (Transact-SQL)
Hi,
You try the following
Or can try this as well
http://www.source-code.biz/snippets/mssql/2.htm
Hope this would help
You try the following
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) +
' DROP CONSTRAINT ' + QUOTENAME(name) + ';'
FROM sys.foreign_keys;
--PRINT @sql;
EXEC sp_executesql @sql;
Or can try this as well
http://www.source-code.biz/snippets/mssql/2.htm
Hope this would help
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses
Course of the Month11 days, 22 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.