0

I have a hospital database from Philips, but the hospital I work at doesn't have the funding to get them to help me find where data in the application lives in the backend SQL Server database. The schema documentation isn't well done. I need to build a query to find the data but I don't yet know where the data lives.

I'm looking for a highly unique value that will tell me the column where most of the data I need is one I find it.

I'd really not like to pull in all of the greater than 100 tables into Access or Filemaker to try to figure out where the data lives.

Is there an easier method where you can query an entire database, and it will search for a certain value in all columns, across all tables to tell you which table and column that bit of data lives?

I'd also be ok with a software solution like Business Objects that might be able to do something like this (not that I know if Business Objects has that ability... I just know that it can help you find data in a database a little earlier so far).

So does anyone know the best approach to accomplishing this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chain
  • 597
  • 2
  • 8
  • 24
  • The SSMS Tools Pack addin (free for versions of Management Studio <= 2008) has a search function built in or you would need to write some dynamic SQL that loops through all tables and looks at all columns of the relevant datatype. – Martin Smith Mar 10 '13 at 22:11
  • @MartinSmith: thanks. Copy & Paste Error. The answers here http://stackoverflow.com/q/5818598/330315 doe not only list the redgate tool. And this question has been asked several time. A quick search also turns up this one: http://stackoverflow.com/q/5818598/330315 –  Mar 10 '13 at 22:26
  • How do I find a value anywhere in a SQL Server Database?http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database – Aleksandr Fedorenko Mar 10 '13 at 22:52
  • Business Objects does not have this capability. – Dan Bracuk Mar 10 '13 at 23:06

2 Answers2

6

Try using ApexSQL Search. It's a free SSMS add-in that should help you find what you need without writing excessive queries.

Dragan Radivojevic
  • 1,992
  • 2
  • 13
  • 14
3

If you have a way of finding/reporting the data in the frontend application, you could try using SQL Profiler as you retrieve the data in the application. Likewise you can run profiler when you save the data to see how it is written.

Duncan Howe
  • 2,965
  • 19
  • 18
  • Hi, Thank you! I'm looking into SQL Profiler now: I'm not sure what you mean by 'finding/reporting the data' or 'as you retrive the data': In the GUI: Doctors just pull up the interface and view the patient's data. – Chain Mar 11 '13 at 04:10
  • Sorry if I'm not understanding you correctly... I've lightly heard that there's a metod of making a SQL query that can target a full database as opposed to a specific table. I'd like to learn more about SQL Profiler. Can it help with my situation? Thanks for your response. – Chain Mar 11 '13 at 04:27
  • Also, are you referring to a 'saving/export' function when you say 'save the data to see how it is written'? Thanks! Shana – Chain Mar 11 '13 at 04:38
  • @Chain - when I say "finding/reporting" and "save the data" I mean using the GUI to get or save the data. SQL Profiler will capture all of the activity on the database so if you use the application against a SQL instance, you can monitor the activity on that instance. The hardest work can be finding the part of the trace that you need - but if you use a known patients identifier, you can search the trace file for that. Good place to start would be http://www.sqlservercentral.com/Books/ – Duncan Howe Mar 11 '13 at 21:47