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:<\/p>\n
It’s a pretty straightforward error. The column ‘incidentId’ doesn’t exist in the table that the script was trying to update.<\/p>\n 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.<\/p>\n Option 1<\/strong> Option 2<\/strong> Turned out the update script just had the wrong column name.\u00a0 \ud83d\ude41<\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"_cloudinary_featured_overwrite":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[68],"tags":[12,16],"class_list":["post-2835","post","type-post","status-publish","format-standard","hentry","category-developer","tag-developer","tag-sql"],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pbVg43-JJ","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts\/2835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/comments?post=2835"}],"version-history":[{"count":0,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/posts\/2835\/revisions"}],"wp:attachment":[{"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/media?parent=2835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/categories?post=2835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/csimmons.dev\/blog\/wp-json\/wp\/v2\/tags?post=2835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Msg 207, Level 16, State 1, Line 1 Invalid column name 'incidentId'.<\/code><\/p>\n
\n-- Find all tables containing the column incidentId
\nSELECT TABLE_NAME
\nFROM INFORMATION_SCHEMA.COLUMNS
\nWHERE COLUMN_NAME = 'incidentId';
\n<\/code><\/p>\n
\n-- Find all tables and columns with a column name that contains 'incident'
\nSELECT TABLE_NAME, COLUMN_NAME
\nFROM INFORMATION_SCHEMA.COLUMNS
\nWHERE COLUMN_NAME LIKE '%incident%'
\nORDER BY COLUMN_NAME;
\n<\/code><\/p>\n