I have a list of market data tables in one of our environments and need to search every column in the list for a certain string. Can anyone point me in the right direction.
1> select @@version
2> go
Adaptive Server Enterprise/15.0.3/EBF 16558 ESD#1/P/Solaris AMD64/OS 5.10/
Thanks
You can query syscolumns and sysobjects in each database to get the information you need.
select
distinct so.name as TABLE_NAME,
sc.name as COLUMN_NAME
from
syscolumns sc,
sysobjects so
where
sc.id = so.id
and sc.name like '%searchstring%'
order by so.name
If you need to limit your search to specific tables, then you can add an additional 'and' clause:
and so.name in ("Table1","Table2","Table3")
master contain the details on all syscolumns and sysobjects in the server? That seems to be what your first sentence is describing. Or does each database keep its own syscolumns and sysobjects data?
need to search every column in the list for a certain string --> So the OP is asking to search every column in the list for a particular string. Also, your query has syntax error. Did you try running it in sybase 15.0.3 instance ? It should be SELECT so.name, sc.name as column_name FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id where so.type = 'U' and lower(sc.name) like '%test%'