0

I have a basic knowledge of SQL using Tables but I only have access to SQL Views and I want to do these tasks frequently

a) List the View name which contains the column name "SEX"

This is what I did wrote for searching view names with specific column 
values

select distinct TABLE_NAME
   from INFORMATION_SCHEMA.COLUMNS
   where COLUMN_NAME in ('Sex')
   and TABLE_SCHEMA = 'BTJrView' ;

I get an error Relation 'COLUMNS' does not exist

b) List the View names that contain column value "IFX"

Need help with these two cases

Again, I am working with Views dont have access to Tables

Bridgeport Tucker Jr
  • 69
  • 2
  • 8
  • 1
    where is your code? what error you get? give us at least what you tried so far. Try visit this http://stackoverflow.com/questions/193780/how-to-find-all-the-tables-in-mysql-with-specific-column-names-in-them – Jun Rikson Jan 22 '15 at 04:47
  • The query you have written seems to be for SQL Server – sqluser Jan 22 '15 at 05:56

1 Answers1

0

You can try this:

select a.name View_name,b.name column_name
from sys.all_objects a
    inner join sys.all_columns b
        on a.object_id=b.object_id
where a.type_desc = 'View'
and b.name in ('SEX','IFX')
Y.S
  • 1,860
  • 3
  • 17
  • 30