SET DEFAULT II

Hier mal eine einfache Version mit purem T-SQL zum setzen von DEFAUL-Values eines Feldes.

DECLARE
  @sql AS nvarchar(1000)

--- isActive = 1
IF NOT EXISTS (SELECT * FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'sys_User') AND name = 'isActive')
BEGIN
  print 'create field with defaults';
  SET @sql = 'ALTER TABLE sys_User ADD isActive BIT DEFAULT (1) NULL';
  EXEC sp_executesql @sql;
END ELSE
  BEGIN
    IF (SELECT cdefault FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'sys_User') AND name = 'isActive') > 0
      BEGIN
        print 'drop contraint';
        SET @sql = 'ALTER TABLE sys_User DROP CONSTRAINT ' +(SELECT name FROM sysobjects WHERE id = (SELECT cdefault FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'sys_User') AND name = 'isActive'));
        EXEC sp_executesql @sql;
      END
    print 'add contraint'
    SET @sql = 'ALTER TABLE sys_User ADD CONSTRAINT DF_'+(SELECT REPLACE(NEWID(),'-','_')) +' DEFAULT (1) FOR isActive';
    EXEC sp_executesql @sql;
  END