1

I have "read" access to a database back end however the tables and columns are named oddly and I am unable to find the information I am looking for. (there are also a lot of tables and lots of data)

I have been using the following method:

Run query:

SELECT * 
FROM information_schema.columns 
WHERE TABLE_SCHEMA = 'dbname' 
  AND DATA_TYPE = 'varchar'
  AND TABLE_NAME IN (SELECT TABLE_NAME 
                     FROM information_schema.tables 
                     WHERE TABLE_TYPE = 'BASE TABLE')

This gives me a list of columns which contain varchar values...

Then for each result, I run the following:

select top 1 [column name] 
from [tablename] 
where [column name] like 'value I'm searching for'

Is there a better way to do this? Or a way to combine these two queries together (as running the query on each result is a slow method)?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
penfold1992
  • 305
  • 2
  • 14
  • There can be more textual data types, like CHAR, NCHAR, NVARCHAR, TEXT, NTEXT; maybe even some more. – Arvo Oct 06 '14 at 13:57
  • I am aware of that, I was just trying to keep it simple to explain what I am trying to do... – penfold1992 Oct 06 '14 at 14:41
  • See this post: http://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server. I use the answer supplied by @regeter – Steve Oct 06 '14 at 15:02
  • I guess you are using sql server, I found a very nice tutorial here [findMyString](http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions--1/#2) – Markus Oct 06 '14 at 11:58
  • I am using "Aqua Data Studio" but the big problem is that I am not an administrator and I only have read access. I am unable to create procedures to run. – penfold1992 Oct 06 '14 at 12:30

0 Answers0