SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure [dbo].[p_sys_md_generate_bit_defaults]
as
	DECLARE	@table	SYSNAME,
			@col	SYSNAME,
			@def	SYSNAME,
			@sql	VARCHAR(500)


	DECLARE c_defaults INSENSITIVE CURSOR 
	FOR 
		select	t.name, c.name,  d.name 
		from	sys.all_columns c
				inner join sys.tables t on t.object_id = c.object_id
				inner join sys.default_constraints d on d.object_id	= c.default_object_id 
				inner join sys.types y on y.system_type_id = c.system_type_id and y.name = 'bit'
		where	d.name != 'DF__' + t.name + '__' + c.name

	OPEN c_defaults

	FETCH NEXT FROM c_defaults INTO @table, @col, @def

	WHILE @@FETCH_STATUS = 0 
	BEGIN
	
		

		-------------------------------------------------------------------------------------------------------------------	
		SELECT @sql = 'ALTER TABLE {table} DROP CONSTRAINT {default}'
		
		SELECT @sql = REPLACE(@sql, '{table}', @table)
		SELECT @sql = REPLACE(@sql, '{default}', @def)
		
		EXEC (@sql)
		-------------------------------------------------------------------------------------------------------------------


		
		-------------------------------------------------------------------------------------------------------------------	
		SELECT @sql = 'ALTER TABLE {table} ADD CONSTRAINT  DF__{table}__{col} DEFAULT 0 FOR {col}  '
		
		SELECT @sql = REPLACE(@sql, '{table}', @table)
		SELECT @sql = REPLACE(@sql, '{col}', @col)
		
		EXEC (@sql)
		-------------------------------------------------------------------------------------------------------------------


		FETCH NEXT FROM c_defaults INTO @table, @col, @def
	END
		

	CLOSE c_defaults
	DEALLOCATE c_defaults

GO
