[dba-SQLServer]Temporarily Disable Default Constraint

I always hate to answer my own posts :( but here is a script I came up 
w/ to facilitate this.. it scrolls through my entire database to remove 
the Default constraint for a specific column.  In my database I have a 
user stamp that auto-adds them to each record they add/edit.. no 
deletes, deletes are handled w/ a flag instead...To edit the udf I 
needed to remove the default constraints and thus was gonna be a 
challange to type up all the table names (too many really)...

the way this script works is you add the name of your default column you 
are after in this case entryUSER, and then if you make the statement 1=1 
(or some other true statment) it will run and drop all constraints that 
match the criteria, I kept the default naming convention, but if you 
have your own you can modify those statements to suit your needs...

if you make the statment false such as 1=2 then it adds the constraints 
back.  Thanks for taking the time to read and I hope this comes in handy 
for someone :)

btw, this script is a variant from the db_columnHelp from 
sqlServerCentral.com (a great site for sql scripts)


IF EXISTS (select * from tempdb.dbo.sysobjects where id = 
object_id(N'tempdb..#tempTables')) BEGIN
    DROP TABLE #tempTables
END
DECLARE    @colname nvarchar(776),     -- object name we're after
        @colvalue varchar(255)
Set @ColName = 'entryUser'

    -- PRELIMINARY
    set nocount on
    declare    @dbname    sysname
        declare @table nvarchar(776)
        declare @tableid int
        -- INFO FOR EACH COLUMN
        print ' '
        select  Table_Name                   = o.name,
            Column_name            = c.name,
                        Table_ID                        = c.id
                into #tempTables
        from syscolumns c
                inner join sysobjects o on c.id= o.id
                where c.name = @colname AND  o.xtype = 'U'
                order by o.name,c.name
           
        select * from #tempTables
        DECLARE tables_cursor CURSOR FOR
    SELECT table_name, table_id
    FROM #tempTables
 
    OPEN tables_cursor
    FETCH NEXT FROM tables_cursor INTO @table, @tableid
        WHILE @@FETCH_STATUS = 0
    BEGIN

    If 1 = 2 --TO DROP Make this TRUE, to ADD make this FALSE
       BEGIN
          if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[DF_' + @table + '_' + @Colname + ']'))
           BEGIN
            exec(' ALTER TABLE ' + @table + ' DROP CONSTRAINT [DF_' + 
@table + '_' + @Colname + ']' )
   
   
           END
       END
    ELSE
       BEGIN
        if NOT exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[DF_' + @table + '_' + @Colname + ']'))
           BEGIN
            exec(' ALTER TABLE ' + @table + ' ADD CONSTRAINT  [DF_' + 
@table + '_' + @Colname + '] DEFAULT ([dbo].[udfCurrent_User]()) FOR [' 
+ @ColName +']' )   
           END
       END

      FETCH NEXT FROM tables_cursor INTO @table, @tableid
   
    END
        CLOSE tables_cursor
    DEALLOCATE tables_cursor

drop table #tempTables
 
 




Francisco H Tapia wrote:

> I had created a UDF that spits back some data, I need to alter the UDF
> but it's listed in more than one table as the default... How do I 
> temporarily disable it?
>
> The error I get is:
>
> Server: Msg 3729, Level 16, State 3, Procedure udfMYFunction, Line 31
> Cannot ALTER 'udfMyFunction' because it is being referenced by object 
> 'DF_tbl_MyTable_MyColumn'.
>
>
> any ideas?
>
> I thought I could just do this  but it doesnt work..
>
> ALTER TABLE Tbl_MyTable NOCHECK CONSTRAINT DF_tbl_MyTable_MyColumn
>
> but that doesn't seem to help because immediately I have my other script
>
> alter Function udfMYFunction() AS ...





-- 
-Francisco