ingenious code ::

Its been a while since I wrote in my blog but I wrote and interesting script (in my opinion...) that I thought is worse while to publish. Problem: I have a database that has several hundred tables and on some of them a bit field that is set to not null but doesn't have a default value. Solution: I wrote a script using the system tables which goes over all the tables and if it doesn't have a default value, and "ALTER TABLE" command is run and sets a default for this field. Here is the script:

declare @sqlString nvarchar(4000),@tablename nvarchar(100),

@ColumnName nvarchar(100)select

so.name table_name

,sc.name column_name

,st.name data_type

,so.id table_id

,sc.colid column_id

into #temp

from sysobjects so

inner join syscolumns sc on (so.id = sc.id)

inner join systypes st on (st.type = sc.type)

where so.type = 'U'

and st.name IN ('BIT')

select c.TABLE_NAME, c.COLUMN_NAME into #temp2

from INFORMATION_SCHEMA.COLUMNS c, #temp t

where  c.TABLE_NAME = t.table_name and c.COLUMN_NAME = t.column_name

and c.data_type = 'bit' and c.COLUMN_DEFAULT is null

drop table #temp

while (select count(*) from #temp2) > 1

begin

select top 1 @tablename = table_name, @columnname = column_name

from #temp2

set @sqlString = 'ALTER TABLE ['+ @tablename + ']
 WITH NOCHECK ADD CONSTRAINT [Df_'+ @tablename +   '_' + @columnname +  ']
 DEFAULT (0) FOR ['+ @columnname +']'

print 'setting default on table: ' + @tablename + ' Column: ' + @columnname

EXEC (@sqlString)

delete #temp2 where table_name = @tablename and column_name = @columnname

end

drop table #temp2