Best Query/Search Method

Hi,

I'm wondering about the following:

I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.
An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I have
reviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're found
inside some DLL.

I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.

So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?

Thanks in advance for your advise.

Stewart

Go into sql server management studio and open up a new query window, then set the query window to the the database in question.

Execute this query:

select 'union select ''[' + colu.table_schema + '].[' + colu.table_name + '].[' + colu.column_name + ']'' as "Schema.Table.Column" '
+ ',[' + colu.column_name + '] as "Value" '
+ ' from [' + colu.table_schema + '].[' + colu.table_name + '] '
+ ' where [' + colu.column_name + '] like ''ZZZ%'' '
from INFORMATION_SCHEMA.columns as colu
where colu.data_type in ('varchar','nvarchar','char','nchar','text','ntext')

Below the query will be the results, one sql statement per text-based column.

You can click, then right-click on the top-left button-looking box in the grid header and copy the text into the buffer.

Paste it into a new query window.

Delete the first "union" on the first line and execute the query.

It will return one row per column value per table that matches ZZZ%.

Depending upon the number of tables/columns in the database, and the number of rows in the table, you might need to split the results into multiple, smaller queries.

Enjoy!

|||

Hi David,

Thanks very much for the assistance. It worked perfectly!

Regards,
Stewart

|||

The views in the master database are very powerful. Try the information_schema views first, and switch to the sys... views if the information_schema views don't have what you need.

Glad it helped!

sql