How can I find a sql statement executed on a single table?

Question

How can I find a sql statement executed on a single table?

Mark Eckeard

Hall of Fame

Points: 3462
More actions
April 14, 2014 at 9:14 am

#285700

All,
I have a very large web app with inbedded sql that I cannot find. This sql needs to be changed because it's not pulling correct data but I can't track it down within the application.
Is there an easy way to trace against a single table? This can be done on the development box to avoid any overhead caused by the trace. If that won't work I could put a trigger on the table if they work on select statements (which I didn't think they did).
These are 2 things I thought of but I'm open to any suggestions to get this done.
Thanks,
Mark

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

You could use SQL Trace and put a filter on Text Data for the table name or you could use Extended Events and put a filter on the SQL Statement, but what information do you need to get back to identify where the code is coming from? The server that is requesting the data, the user that connected to the SQL Server, etc.? Will that be enough information for you track down where the code is coming from?

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 2

Keith,

I'd like to see the actual SQL. That would allow me to conduct a more specific search within the app to (hopefully) find it.

Thanks,

Mark

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 3

Keith,

It's worth noting that I'm not 100% sure it's imbedded sql. There's a chance it's a sproc. I would need to account for that possibility.

Thanks,

Mark

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 4

Mark Eckeard (4/14/2014)
It's worth noting that I'm not 100% sure it's imbedded sql. There's a chance it's a sproc. I would need to account for that possibility.

Let's rule that out first by searching sys.sql_modules (definition column) to make sure that the query isn't a proc, function, trigger, etc.

How much of the query do you have? Do you have the entire query?

Edit: Added the URL for sys.sql_modules

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Mark Eckeard (4/14/2014)
Keith,
I'd like to see the actual SQL. That would allow me to conduct a more specific search within the app to (hopefully) find it.
Thanks,
Mark

I would get SQL Search and search for your table name. (It's free from Redgate, our sponsor). Running a trace is likely to help too.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 6

Keith Tate (4/14/2014)
Mark Eckeard (4/14/2014)
It's worth noting that I'm not 100% sure it's imbedded sql. There's a chance it's a sproc. I would need to account for that possibility.
Let's rule that out first by searching sys.sql_modules (definition column) to make sure that the query isn't a proc, function, trigger, etc.
How much of the query do you have? Do you have the entire query?
Edit: Added the URL for sys.sql_modules

I have none of the query, just someone who could tell me 2 of the tables involved. This person is a BA or PM and knows that much but nothing more.

I did a search in the project on those 2 table names and came up with nothing.

As far as the tables are concerned, I did right-click and view dependencies and looked at the views and sprocs in the list but none look like correct.

Mark

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 7

Sean Lange (4/14/2014)
Mark Eckeard (4/14/2014)
Keith,
I'd like to see the actual SQL. That would allow me to conduct a more specific search within the app to (hopefully) find it.
Thanks,
Mark
I would get SQL Search and search for your table name. (It's free from Redgate, our sponsor). Running a trace is likely to help too.

Wow, that's a nice tool and it's free!

While I haven't found the query I believe the 2 tables mentioned to me were wrong. None of the columns in either table match fields on the report. That's not to say that they aren't part of the query but they aren't the main ones. I'll use this tool to see if I can track down other tables by columns I see in the report.

Thanks,

Mark

sqldriver SSChampion Points: 10440 More actions · Answer 8

I was just doing something similar:

SELECT cp.objtype AS ObjectType,

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.text like '%YOUR TABLE HERE%'