When user defined functions are referenced as defaults values for columns and there is a need to Alter or Drop the function, SQL Server will not allow this without dropping the dependencies first.
It is manageable if the function is referred in one or two columns.However if these functions are referred across the database, it would become time consuming and cumbersome to manually delete these default constraints.
The below queries will provide the table,schema,constraint and column names that are needed to successfully drop and recreate the constraints.
I was able to come up with three ways of doing this.This one is my personal favorite since this uses just two tables and does the job.Here in this example [schema].[defaultfunction()] will be the function that is being referenced in the default constraints
SELECT '[' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + ']' AS 'Table Name',col.name AS 'Column Name',def.name AS 'Constraint Name',def.definition AS 'Default Value'
FROM sys.default_constraints def,sys.columns col
WHERE def.parent_OBJECT_ID = col.object_id
AND def.parent_column_id = col.column_id
AND def.definition = '([schema].[defaultfunction()])'
This is the next approach.The results are as good as the first.
SELECT '[' + SCHEMA_NAME(obj.uid) + '].[' + OBJECT_NAME(obj.parent_obj) + ']' AS 'Table Name',col.name AS 'Column Name',obj.name AS 'Constraint Name',com.text AS 'Default Value'
FROM sysobjects obj,syscolumns col,syscomments com
WHERE col.cdefault = obj.id
AND obj.id = com.id
AND com.text = '([schema].[defaultfunction()])'
This is the third approach.This would work fine if there is just one default schema.However there is a drawback.When there are multiple schema this would need the schema name and the existing columns will not be able to supply that information.There needs to be an extra join on sysusers.uid to fetch this information.
SELECT OBJECT_NAME(con.id) AS 'Table Name',col.name AS 'Column Name',OBJECT_NAME(con.constid) AS 'Constraint Name', com.text AS 'Default Value'
FROM SYSCONSTRAINTS con,sys.columns col,syscomments com
WHERE con.constid = col.default_object_id
AND con.constid = com.id
AND com.text = '([schema].[defaultfunction()])'
Now, going back to the first one, lets execute the it on AdventureWorks2012, using getdate() as the function referenced in default constraints .This is going to bring up the 77 rows.
The first column will provide the syntax to drop the constraint and the second column will recreate it.
USE AdventureWorks2012
GO
SELECT 'ALTER TABLE [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] DROP CONSTRAINT ' + def.name as 'drop constraint',
'ALTER TABLE [' + SCHEMA_NAME(def.schema_id) + '].[' + OBJECT_NAME(def.parent_object_id) + '] WITH NOCHECK ADD CONSTRAINT ' + def.name + ' DEFAULT(getdate()) FOR ' + col.name as 'create constraint'
FROM sys.default_constraints def,sys.columns col
WHERE def.parent_OBJECT_ID = col.object_id
AND def.parent_column_id = col.column_id
AND def.definition = '(getdate())'
-Aravind
