Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Moe1950
Starting Member
3 Posts |
 Posted - 2012-04-18 : 11:47:55
|
| I know that if I wish to change a data type I can use the ALTER TABLE query. However, that is going to change only one field at a time, and only the field I specify in the query.I have a table that is shipped over to me once a month from another department. I have no control over how it is created. Many of the fields are created, through their process, with a data type of "Float" - that doesn't work for me. I need to change all those fields from "Float" to "Decimal" - Up to now, I did them one at a time with ALTER TABLE. However, the number of fields in this table keeps growing and I'd like to be able to run some t-sql through Management Studio that goes through the entire table and checks the data type for every field. When it finds "Float" change it to "Decimal" How would I do that? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-18 : 11:59:32
|
There are probably very elegant ways to do this, but what I am going to propose below is a low-tech approach. But it works, and what is better, it lets you see what you are going to alter BEFORE you pull the trigger. So here it goes:Assume your table name is XYZ in schema dbo. Then, run a query like this:SELECT 'alter table dbo.XYZ alter column ' + column_name + ' decimal(18,2) 'FROM INFORMATION_SCHEMA.[COLUMNS]WHERE TABLE_NAME = 'XYZ' AND TABLE_SCHEMA = 'dbo' AND DATA_TYPE = 'float' The result of this query will be a set of alter statements. Copy that to a query window, look at it carefully to make sure that is what you want to do, and run it. |
 |
|
|
Moe1950
Starting Member
3 Posts |
Posted - 2012-04-18 : 13:30:57
|
| This does work quite nicely actually.I will continue attempts to completely automate this but I think having a completely automated way of doing it (stored procedure where you just feed in the parameters of tablename, data type you want to change, what you want to change it to) as well as your code that merely creates the alter table queries and allows you to review them before execution, would be a good "one-two" punch for the old SQL Server tips and tricks bag.Thanks!!!!!!! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-18 : 14:33:24
|
Glad it worked out for you.As for automating such a task, I am weary of anything where one needs to change the database schema automatically. While I am a relative novice at databases compared to many people on this forum, the vibe I get from most of them is that schema changes are something that need to be reviewed and performed carefully. If the task requires schema changes, they would say that there is room for improvement in the processes or design. For example, they might suggest a better approach to the problem you are trying to solve might be as follows:a) Load the data received from the external party into a staging database.b) Write a script (or stored proc, or SSIS task) to read the data from the staging table, do the appropriate conversions, and load them into your destination tables.I will stop pontificating.... I can be annoying when I pontificate  |
|
|
|
Moe1950
Starting Member
3 Posts |
Posted - 2012-04-19 : 07:08:48
|
| "If the task requires schema changes, they would say that there is room for improvement in the processes or design." - I agree with this 1000% However, as I mentioned in my original post, this table is sent to me once a month from another department within my company and I have no control whatsoever over the process used to create it. I have, repeatedly, asked if something could be done to change their process such that these fields would not be created as 'float' but, rather, as decimal.So much for interdepartmental cooperation. |
|
|
|
|
|
|
|
|
Changing Field Data Type