>How would I create a single script that would do an alter table on every table in a database (over 100) changing the datatype of every column that is char(36) to uniqueidentifier?
>
>I'm thinking sp_msforeachtable but the logic after than is based on a columns data type ...
>
>And while I'm asking, then make every column named cid ( and uniqueidentifier) the PK
>
>(okay that one may be able to do with sp_msforeachtable)
>
>(this is part of a conversion of VFP/VFE data and is going to possibly be needed if I can't figure out how to hack the Sedna upsizer to recognize C(36) NOCPTRANS as a uniqueidentifier.
>
>( And yes, I probably will explore grabbing the data from the SQL side with SSIS but I'm not sure that will be any easier - isn't there an issue with memo fields?)
>
>TSQL gurus, your help appreciated.

DECLARE @Test varchar(max)
SET @Test = ''
SELECT @Test = @Test +'ALTER TABLE '+Object_Name(Object_Id)+' ALTER COLUMN '+NAME+' Uniqueidentifiier'+CHAR(13)+CHAR(10)
 FROM sys.columns
 WHERE system_type_id = 175 
   AND max_length = 36
   AND Object_Name(Object_Id) NOT LIKE 'sys%'
 print @test

Then just copy and paste generated script from Message area to editor.
BUT, I'm not sure if this could work, if the column have some constraints you should drop them and then recreate.