SQLTeam.com | Weblogs | Forums

How to search for a term within a database

sql2008

#1

I am using SQL Server. I am trying to match the tables to the frontend for reporting. I wanted to take a field from the frontend and run a search on the backend to find out what table and column it is stored in.

how would I do a database search for a term or data fields.


#2

This will give you a start:

select *
  from sys.all_columns
;

Edit: actually this might be more usefull:

select a.name as tablename
      ,b.*
  from sys.tables as a
       inner join sys.all_columns as b
	           on b.object_id=a.object_id
 where a.type='U'
;

#3

Thanks that did get me started


#4

Why not just look at the source code for the front-end? Of is this for a 3rd party application?


#5

It a 3rd party application. No source code in the front-end...and hundreds of tables in the back end.


#6

I tend to find "spying" on 3rd party queries is often faster than guessing which Table / Column something relates to, particularly if the APP does some JOINs to get some code-lookup descriptions etc.

SQL Profiler would be my choice, filtered for the specific login / workstation / database / however narrow you can get! (might be that ALL users connect with the same SQL Login, if not that would be ideal), and then run a report in the APP and see what SQL arrives at the server.