Im used to basic SQL statements only so far, but Im full of Ideas how to realise some tasks.
Hopefully someone is more skilled and could help:
I am about to alter columns types in different tables from TEXT/MEMO to VARCHAR(MAX). There are really a lot of tables needed to be checked and querys to be individually made. BUT, luckily we have an table, which holds that information and gives an overview in which table there are TEXT/MEMO columns.
The result of such an table (WHERE FIELD_TYPE ='M') looks for e.g. like that:
| TAB_DESC | FIELD_NAME | FIELD_TYPE |
|---|---|---|
| DADA2 | REMARKS | M |
| DADA3 | COMMENT | M |
| DADA4 | REMARK | M |
That result means in our system, for e.g. that the Table "TABLE_QQ_DADA2" has a column "REMARKS" which is TEXT/MEMO. Now it needs to be VARCHAR(MAX).
So based on that table I would simply type create manually
ALTER TABLE TABLE_QQ_DADA2 ALTER COLUMN REMARKS VARCHAR(MAX)
ALTER TABLE TABLE_QQ_DADA3 ALTER COLUMN COMMENT VARCHAR(MAX)
ALTER TABLE TABLE_QQ_DADA4 ALTER COLUMN REMARK VARCHAR(MAX)
But I am not satisfied with that and it tingles me to create some kind of loop, which would do that ALTER based on such result. Some kind of pre-peraded Query, which replaces variables with the results in the columns above. (I hope you get my idea).
I tried to look up some String_Agg functions, but as it seems, sql server 2016 does not support String_Agg yet:/ but i would take that too, in case its easier. Temporarily i could use newer sql-server versions which support String_Agg.
I will be greatful for every idea :)
Thanks
