How to find and change data type of a specific field in all tables in a database?
Posted on 2008-06-18
I would like to change the data type of a specific field in my database. This field is named "TRANS_TYPE_ID". In most cases, this is a foreign key to the "TRANS_TYPE" table. The issue is some of the fields have different datatypes [either numeric(10,0) or deciamal(10,0)]. It is a mystery on how this happened but there are relationships between the fields even though the datatypes are different. The previous database format must of allowed this and was able to import it into SQL 2005 without any errors. I don't know what has happened before me, but I am now asked to change all the "TRANS_TYPE_ID" fields to numeric(10).
Can anyone provide me with a script to query the database tables that contain the "TRANS_TYPE_ID" field name with database other than numeric(10,0) ? Also, is there a way to loop through these tables and change the datatype with T-SQL?