Modifying default values for SQL Server database-related fields

The original database has many types of data default values are null values, for later in the actual development process with a lot of unchanged. This null is actually good, I think can save the database space, in the new data can also improve the speed. However, the following code should be written at the request of the leader. Done with the help of Csdn's heroes.

Declare @t table (ID int identity (1,1), Tbname varchar (256), colname varchar (256), xtype varchar (20))

INSERT INTO @t

Select A.name,b.name, C.name

From sysobjects a

INNER JOIN syscolumns B on a.id=b.id

INNER JOIN systypes c on b.xusertype = C.xusertype

where a.xtype= ' u '

and c.name in (' varchar ', ' int ')

and b.status<>0x80--remove self-adding columns

And not exists--filters out columns that already have default values

(SELECT 1

From

(SELECT

(select name from sysobjects where id=c.id) table name,

(select name from syscolumns where cdefault=a.id) field name

From sysobjects B,syscolumns c,syscomments a

where b.xtype= ' d '

and A.id=b.id

and B.parent_obj=c.id

and A.colid=c.colid

) T

where a.name=t. Table name

and b.name=t. Field name)

--select * from @t

DECLARE @i int

Set @i=1

DECLARE @tbname varchar (256), @colname varchar (256), @xtype varchar, @sql nvarchar (4000)

While @i <= (select MAX (ID) from @t)

Begin

Select @tbname =tbname, @colname =colname, @xtype = Xtype from @t where id=@i

Set @sql = ' ALTER TABLE [' + @tbname + '] add constraint ' + ' df_ ' + replace (@tbname, '-', ') + ' _ ' + replace (@colname, '-', ') + ' Default '

If @xtype = ' int '

Begin

Set @sql = @sql + ' 0 '

End

else if @xtype = ' varchar '

Begin

Set @sql = @sql + ' "'

End

Set @sql = @sql + ' for [' + @colname + '] '

EXEC (@sql)

Set @i = @i + 1

End

Xiao Kee

After registering a good basic has not come, later will be the point of their own learning are recorded here. Record your own growth.