I needed to find all columns with an underscore (_) in their name and came up with this query:
|
1 2 3 4 5 6 7 |
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like '%_%' ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME |
But instead of showing me columns with a _ in their name, I got every column back that exists in my database. When I replace _ with anything else, I got the expected result. What is so special with the _ character?
As it turns out, the _ is a wildcard to match one character in SQL Server – as it is in Oracle. To get the result I wanted, I had to escape the _ character and rewrite my where-clause to this:
|
1 |
WHERE COLUMN_NAME like '%\_%' {escape '\'} |
If you are used to regular expressions, you may find this alternative useful:
|
1 |
WHERE COLUMN_NAME like '%[_]%' |
With this small change, I finally got the result I was expecting.