A SQL script was provided for me to run on behalf of a workgroup yesterday. The script was supposed to run a simple update on some problematic records. The database is one that is in use by many different clients with varying degrees of customization. The script failed with this error message:
Msg 207, Level 16, State 1, Line 1 Invalid column name 'incidentId'.
It’s a pretty straightforward error. The column ‘incidentId’ doesn’t exist in the table that the script was trying to update.
Since I am unfamiliar with the database I wanted to provide the workgroup any potentially useful information they could take to the developer such as tables which did contain the column or something close. Below are the queries I used.
Option 1
-- Find all tables containing the column incidentId
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'incidentId';
Option 2
-- Find all tables and columns with a column name that contains 'incident'
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%incident%'
ORDER BY COLUMN_NAME;
Turned out the update script just had the wrong column name. 🙁