Home / Questions / 564161
Accepted
carallot
Asked: 2022-10-26 01:15:35 +0800 CST 2022-10-26 01:15:35 +0800 CST

How to perform an action on all tables in a database that contain a specific field?

  • 772

I have a Database, in which I need to clean the spaces of a specific field in all the tables of the database. This field is called delegate in your table and idDelegado in your foreign ones. For example, if the code for a delegate is ' DEL012 'I need to do a Trim()on all tables where there is a idDelegado. To consult all the tables where I have a iDdelegadoI do it like this:

Select TABLE_NAME,COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'idDelegado' AND TABLE_NAME NOT like 'VIS_%'
ORDER BY COLUMN_NAME

And it occurs to me to go through the result of the query with a cursor and do the corresponding Trim(), but I doubt that this is the best solution. Is there a better way to do it?

sql-server

1 Answers

  1. Best Answer

    A suitable way for this type of work, which at the same time is simpler than building a loop with cursors, is to build a "factory query", that is, a query that builds another query, for example:

    Select  'UPDATE ' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + 
        '] SET ' + COLUMN_NAME + ' = RTRIM(LTRIM(' + COLUMN_NAME + '))'
        from INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = 'idDelegado' 
            AND TABLE_NAME NOT like 'VIS_%'
        ORDER BY COLUMN_NAME

    What you would do after executing it, would be to copy the results in another query and execute it.

    The only problem you have is that it seems that we are talking about a column IDso the modification can be complicated, particularly if it is FKin some tables, in the best scenario, you will have to set an update order starting with the detail tables and then the teachers.

    • 1