USE [DatabaseName]
GO
--
-- find all system named default constraint
select
t.name TABLE_NAME
, c.name COLUMN_NAME
, d.name CONSTRAINT_NAME
, cm.text DEFAULT_VALUE
, 'ALTER TABLE [dbo].['+t.name +'] DROP CONSTRAINT [' +d.name +']' AS DROP_STATEMENT
, 'ALTER TABLE [dbo].['+t.name+'] ADD CONSTRAINT ['+d.name+'] DEFAULT '+cm.text+' FOR ['+c.name+']' as CREATE_STATEMENT
, 'ECHO ALTER TABLE [dbo].['+t.name+'] ADD CONSTRAINT ['+d.name+'] DEFAULT '+cm.text+' FOR ['+c.name+'] > ' + d.name+'.defconst.sql' as CREATE_BATCH_COMMAND
from sys.default_constraints d
inner join sys.tables t
on t.object_id = d.parent_object_id
inner join sys.columns c
on c.object_id = t.object_id and c.column_id = d.parent_column_id
inner join sys.syscomments cm
on cm.id=d.object_id
where d.is_system_named=1 and t.is_ms_shipped=0
order by 1,2
GO
SELECT
b.name AS TABLE_NAME,
d.name AS COLUMN_NAME,
a.name AS CONSTRAINT_NAME,
c.text AS DEFAULT_VALUE
,a.*
FROM sys.sysobjects a
INNER JOIN
(SELECT name, id
FROM sys.sysobjects
WHERE xtype = 'U') b
ON (a.parent_obj = b.id)
INNER JOIN sys.syscomments c
ON (a.id = c.id)
INNER JOIN sys.syscolumns d ON (d.cdefault = a.id)
WHERE a.xtype = 'D'
ORDER BY b.name, a.name
