Prev: install ssis on windows server 2003
Next: Using Parameters and Loop to find all SQL Server versions
From: Jay Nimec on 12 Aug 2010 10:11
There is an article here that has the very simple script to find the default constraints. Hope this helps!

http://thiagsundar.wordpress.com/script-to-find-the-default-constraint-and-drop/

> On Thursday, March 01, 2007 8:55 AM Jac wrote:

> Hi,
>
> I am creating drop and create statements for default constraints on my
> database based on certain column data types.
> Is there a better way of doing this?
> I tried doing it with INFORMATION_SCHEMA.VIEWS but it I cant seem to get the
> full constraint name from there.
>
> -------
> SET NOCOUNT ON
> SELECT 'ALTER TABLE [' + 'dbo' + '].[' + OBJECT_NAME(sc.id)+ '] ' +
> 'DROP CONSTRAINT ' + OBJECT_NAME(sc.cdefault) + CHAR(13)+CHAR(10)
>
> AS [--Drop default constraint]
>
> from syscolumns sc
> join sysobjects as so on sc.cdefault = so.id
> join syscomments as sm on sc.cdefault = sm.id
> WHERE OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1 AND TYPE_NAME(sc.xtype) =
> 'money'
>
> SELECT 'ALTER TABLE [' + 'dbo' + '].[' + OBJECT_NAME(sc.id) + '] '
> + 'WITH NOCHECK ADD CONSTRAINT ' + OBJECT_NAME(sc.cdefault)+
> ' DEFAULT ' + sm.text +' For ' + '[' + OBJECT_NAME(sc.id) + ']' +
> CHAR(13)+CHAR(10)
>
> AS [--Create default constraint]
>
> from syscolumns sc
> join sysobjects as so on sc.cdefault = so.id
> join syscomments as sm on sc.cdefault = sm.id
> WHERE OBJECTPROPERTY(so.id, N'IsDefaultCnst') = 1 AND TYPE_NAME(sc.xtype) =
> 'money'
>
> --------
>
> Many Thanks.


> Submitted via EggHeadCafe - Software Developer Portal of Choice
> Scrolling in WPF Toolkit?s Column Chart
> http://www.eggheadcafe.com/tutorials/aspnet/0939d60c-8e17-4a27-b898-1fc772d2d6f6/scrolling-in-wpf-toolkits-column-chart.aspx
 | 
Pages: 1
Prev: install ssis on windows server 2003
Next: Using Parameters and Loop to find all SQL Server versions