Thanks for reminding me about OBJECT_NAME function which for some reason I alwasy forget.

I have conditional logic that sets all cid (UID) columns to NOT NULL and all other altered UID to NULL but a case statement in line can handle that.

Should mention for lurkers @Print has an 8000 character limit. I have a work around ( may be from Phil Factor at SimpleTalk) which works really well if anyone ever needs it)

>>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.