/* Suppose: in order to interface with the legacy CISC system, no
   blanks can be permitted in City names that are interfaced. The IS 
   director has requested a data fix script to remove blanks from all 
   interfaced files. The Stores table does not get interfaced. 	
*/

declare @table varchar(30),  		-- table name working field
        @city varchar(20),		-- city name working field
	@inner_cursor varchar(255),	-- literal to define the inner cursor
	@action_to_take varchar(255)	-- literal to define the action to complete 

set nocount on

declare outer_cursor cursor
for
select name 
  from sysobjects 
  where id in (select id
                from syscolumns 
                where name = 'city')	-- tables that have a city field
  and name like "%rs" 			-- authors & publishers but not stores    
  and type = 'U' 			-- user tables only

  open outer_cursor
    fetch next from outer_cursor into @table
    while (@@fetch_status != -1)
      begin
        select @inner_cursor = "declare inner_cursor cursor for select city from " + @table + " for update of city"
        exec (@inner_cursor) 
          open inner_cursor
            fetch next from inner_cursor into @city
            while (@@fetch_status != -1)
              begin
                /*SQL Server needs a little help with apostrophes in the column */ 
                if (PATINDEX("%'%", @city) != 0)
		    select @city = STUFF(@city,1,PATINDEX("%'%",@city)-1,substring(@city,1,PATINDEX("%'%",@city)-1)+"'")
                /*Take all the spaces out */   
                while (PATINDEX("% %",@city) != 0)
                    select @city = STUFF(@city,1, PATINDEX("% %",@city),substring (@city,1, PATINDEX("% %",@city)-1))
                select @action_to_take = "update " + @table + " set city ='"+ @city + "' where current of inner_cursor" 
                exec (@action_to_take)
                fetch next from inner_cursor into @city
              end
            deallocate inner_cursor
        fetch next from outer_cursor into @table
      end        
    deallocate outer_cursor