Finding a column by name with wildcard

Question

I am trying to find in a dental system where they store some info about the confirmers to an appointments.

There is a employee table and they use OperatorID with a code and I have seen this code on scheduler column in a report. But never can I locate the confirmer even tho the system has own reports that show this.

Can I run a query which will return all tables, views, sp that have this column, and perhaps something like Operator* as a wild card.

we need to know what platform you are using because meta data is stored in a different place on each platform. — Hogan, Feb 09 '17 at 16:47
SQL search would be helpful but I can't put that as an aswer without someone down voting me. so here you go. [link](http://www.red-gate.com/products/sql-development/sql-search/) — MistaGoustan, Feb 09 '17 at 16:56

score 2 · Answer 1 · edited May 23 '17 at 11:53

In ANSI-SQL there is INFORMATION_SCHEMA. Please look here (https://en.wikipedia.org/wiki/Information_schema).

You can query that to get all columns, tables, indexes and so on.

Because you work with views, you can use sql-syntax and use wildcards and everything else, you are used to in sql.

For example, look here: Find the real column name of an alias used in a view?

From @Arions answer to this question:

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='viewTest'

SELECT * 
FROM INFORMATION_SCHEMA.COLUMNS AS UsedColumns 
WHERE UsedColumns.COLUMN_NAME like 'Operator%''

adapted to your question it should look like that:

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.COLUMN_NAME like 'Operator%'

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='viewTest'

Finding a column by name with wildcard

1 Answers1