>How do I scan my all tables in my database for null values and replace them with 0 for a numeric field and an empty string for character fields ??

Here's a sample code( not tested)

DECLARE @Table_name varchar(100), @Column_name varchar(100), @Data_type varchar(100)
DECLARE @UpdateStr varchar(255)

DECLARE NullableColumns CURSOR READ_ONLY FOR
  SELECT Table_name, Column_name, Data_type FROM INFORMATION_SCHEMA.Columns
	WHERE Is_Nullable = 'YES' And table_name In (
		SELECT table_name FROM INFORMATION_SCHEMA.TABLES
			WHERE table_type = 'BASE TABLE' 
				AND table_name <> 'dtproperties')

Open NullableColumns
FETCH NEXT FROM NullableColumns
	INTO @Table_name, @Column_name, @Data_type

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @UpdateStr = 'UPDATE ' + RTRIM(@Table_name) + ' SET ' + 
		RTRIM( @Column_name ) + ' = ' 
	IF @Data_type = 'number' OR @Data_type = 'decimal' 
		-- Add check for all numeric data types above
		SET @UpdateStr = @UpdateStr + '0'
	ELSE
		SET @UpdateStr = @UpdateStr + 'space(0)'

        SET @UpdateStr = @UpdateStr + ' WHERE ' + RTRIM( @Column_name ) + ' IS NULL ' 

	print @UpdateStr
        
        -- sp_executesql @UpdateStr

	FETCH NEXT FROM NullableColumns
		INTO @Table_name, @Column_name, @Data_type
END

CLOSE NullableColumns
DEALLOCATE NullableColumns

Remember that datetime fields in Sql Server cannot be empty.