I think you can get ideas from this blog post
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-get-information-about-all-databas>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.
If it's not broken, fix it until it is.
My Blog