給資料表加上默認值

 

-- 給所有的欄位加上默認值
Declare   @TableName   varchar ( 40 )    -- 定義存儲數據表名稱
            , @ColName   varchar ( 40 )      -- 定義存儲字段名
            , @ColType   varchar ( 20 )        -- 定義存儲字段類型
Declare  DS_Cur  Cursor   for
select  SysObjects.name TableName
      --  ,syscolumns.colorder
      ,syscolumns.name ColName 
      , Case  systypes.name 
             when   ' int '   Then   ' int '
             when   ' tinyint '   Then   ' tinyint '
             when   ' char '   Then   ' C '
             when   ' varchar '   Then   ' VC '
             when   ' numeric '   Then   ' N '
         End   coltype
   From  SysObjects SysObjects
        Join  syscolumns syscolumns
             On  SysObjects.id  =  syscolumns.id
        Left   Outer   Join  sysproperties sysproperties
             On  syscolumns.id  =  sysproperties.id
            And  syscolumns.colorder  =  sysproperties.smallid
        Join  systypes systypes
             On  syscolumns.xtype  =  systypes.xtype
  Where  SysObjects.status  >   0
    And  SysObjects.xtype  =   ' U '
    And   Not  (SysObjects.name  like   ' %Log ' )
    order   by   SysObjects.Name
            ,syscolumns.colorder
open  DS_Cur    while   1 =   1
Begin
   
        fetch   next   from  DS_Cur 
                                  into   @tableName
                                        , @ColName
                                        , @ColType
           IF   NOT  ( @@FETCH_STATUS   =   0 )
             Break

                          if ( @ColType   =   ' VC ' )
                         Begin
                ALTER   TABLE   ' [dbo].[ '   +   @tableName   +   ' ] '   ADD  
                         CONSTRAINT   [ 'DF_'+@tableName+'_'+@ColName ]   DEFAULT  ( '' )  FOR   [ @ColName ]
                         end
                        
                      if ( @ColType   =   ' C '  )
                         Begin
                ALTER   TABLE   [ dbo ] . [ @tableName ]   ADD  
                         CONSTRAINT   [ 'DF_'+@tableName+'_'+@ColName ]   DEFAULT  ( '' )  FOR   [ @ColName ]
                         end

                          if ( @ColType   =   ' int ' )
                         Begin
                ALTER   TABLE   [ dbo ] . [ @tableName ]   ADD  
                         CONSTRAINT   [ 'DF_'+@tableName+'_'+@ColName ]   DEFAULT  ( 0 )  FOR   [ @ColName ]
                         end

                         if ( @ColType   =   ' tinyint ' )
                         Begin
                ALTER   TABLE   [ dbo ] . [ @tableName ]   ADD  
                         CONSTRAINT   [ 'DF_'+@tableName+'_'+@ColName ]   DEFAULT  ( 0 )  FOR   [ @ColName ]
                         end
                        
                         if ( @ColType   =   ' N ' )
                         Begin
                ALTER   TABLE   [ dbo ] . [ @tableName ]   ADD  
                         CONSTRAINT   [ 'DF_'+@tableName+'_'+@ColName ]   DEFAULT  ( 0 )  FOR   [ @ColName ]
                         end
end
Close  DS_Cur
Deallocate  DS_Cur