0

I have one database on my server I want to filter one column name from the whole database where that column is used.

E.g: If column name is "AlternativeID" is exist in 5 tables and than I want the query that find this column name exists in which tables.?

I find the following query to find specific column name from database.

SELECT * FROM sys.columns WHERE name LIKE '%AlternativeID%'

I hope this is make sense to everyone. Any help will appreciated. Thanks!!

Iffi
  • 608
  • 1
  • 7
  • 16
  • Duplicate of http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them – Incognito Jul 06 '15 at 07:22
  • @Incognito That link I already try but its not meet my requirement what I want is something else from that all queries. – Iffi Jul 06 '15 at 07:26
  • `sys.columns` is sql server specific. your question is tagged with mysql. which one are you using? – ughai Jul 06 '15 at 07:27
  • @ughai I updated. I need for sql ... – Iffi Jul 06 '15 at 07:28
  • Do you mean you want a vendor agnostic query which works across sql server, mysql, oracle etc... In that case use `INFORMATION_SCHEMA.COLUMNS` – ughai Jul 06 '15 at 07:30
  • possible duplicate of [find all tables containing column with specified name](http://stackoverflow.com/questions/4849652/find-all-tables-containing-column-with-specified-name) – kasim Jul 06 '15 at 11:55

2 Answers2

1

Try this:

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%AlternativeID%'

OR...

SELECT COLUMN_NAME, TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%AlternativeID%'

Source : Find all tables containing column with specified name

Hope this helps...

Community
  • 1
  • 1
PKirby
  • 859
  • 3
  • 16
  • 36
0

Try:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('AlternativeID')
    AND TABLE_SCHEMA='YourDatabaseName';
Evans Murithi
  • 3,197
  • 1
  • 21
  • 26