-2

I want to lookup if a column by the name of 'LOB' exist among thousands of views. Schema name: PACONE

any help is appreciated here. Thanks.

ak1976
  • 5
  • 1
  • 4

3 Answers3

2

That solution will depend on what Database Server you have.

If you have Oracle, go to Search an Oracle database for tables with specific column names?

If you have mySQL, go to How do i search a mysql database for a specific column name

If you have PostgreSQL, go to How to find a table having a specific column in postgresql

If you have SQL Server, this is a possible solution:

How-To: Find Fields or Tables Within a SQL Server Database

Suppose you need to find a field called GLASS_ID, you simply run this:

-- SEARCHING FOR A CERTAIN FIELD NAME -- SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(COLUMN_NAME) = 'GLASS_ID'

Similarly, you can look for all field names that end with 'CO'

-- SEARCHING FOR FIELD NAMES THAT END WITH... -- SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE UPPER(COLUMN_NAME) LIKE '%CO'

Of course, this is supposed to work under SQL Server, and that was tested in SQL Server 2005 only, but I guess that it will work in any recent version too.

Community
  • 1
  • 1
Pedro Carneiro Jr.
  • 31
  • 4
0
 use [your database name]
 SELECT t.name AS table_name,
 SCHEMA_NAME(schema_id) AS schema_name,
 c.name AS column_name
 FROM sys.tables AS t
 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
 WHERE c.name = 'LOB'
 ORDER BY schema_name, table_name;
BrianMichaels
  • 522
  • 1
  • 7
  • 16
  • Can you help me plug in the variables, bc I am getting errors here; Schema name: PACONE looking for table has a 'LOB' column – ak1976 Feb 16 '16 at 18:41
  • Which database server are you using ? My solution works with Microsoft sql server. You should add that to your tags – BrianMichaels Feb 16 '16 at 18:47
0

First of all you should provide some information about your environment.

If you use Oracle you can query the dba_tab_columns with dab_view (user_ or all_ is also possible) e.g.:

select a.view_name 
from dba_views a 
join dba_tab_columns b 
on a.view_name = b.table_name 
where lower(b.column_name) = 'lob'
and lower(b.owner) = <view_owner>;
SanHolo
  • 46
  • 6