Is There A Way To View Tables Updated By An Application?

Question

Post reply

Is There A Way To View Tables Updated By An Application?

EdA ROC

SSCrazy

Points: 2734
More actions
January 10, 2010 at 3:03 pm

#229017

I develop in house reports, using Access, to complement our ERP Application. Often I need to figure out what tables are part of a procedure, i.e. add new orders, add new purchase orders, etc.
I'm stomped on my latest project and was wondering if there's a way to "audit" the statements the application is making on the SQL Server.
I'm reading about SQL Profiler, tried to trace but haven't been successful. Got a long log - including other users' activity on the system, lots of my activity, but nothing that helps. I'm assuming that I want to locate the INSERT statements and that will identify what tables are part of the procedure.
What am I missing? Where am I going wrong? Am I even in the ball park? 🙂
Any help will be appreciated - especially links to tutorials. (It's better to learn how to fish, than be given fish. Being given fish to eat while learning how to fish is a good strategy too).
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

This will return a list of all SPs in a SQL 2000 db

SELECT so.name, sob.name as 'Stored Procedure name' FROM sysobjects so

LEFT OUTER JOIN (sysobjects sob left outer join sysdepends on sob.id = sysdepends.id)

on sysdepends.depid = so.id

WHERE so.xtype = 'u' AND sob.xtype = 'p'AND NOT so.name = 'dtproperties'

GROUP BY so.name, sob.name

ORDER BY so.nameThis will return the SP name and its complete text

select ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION

From INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME ='your SPs name'

Output this to text in QueryAnalyzer and you can cut and paste into a word document and then you can then search each routines text for the "FROM" and "JOIN" clauses to determine table names. Slow method but it does work.

And after all that work save the word doc for I am sure you would not want to have to go do it all over again

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

EdA ROC SSCrazy Points: 2734 More actions · Answer 2

Thanks for the reply.

I looked at Stored Procedures in SQL Server Enterprise wondering if that would be the same as what is returned in your first statement. The first thing I noticed is there are 85 asp_'s, and 4 sp_'s, as well as a bunch of dt_'s (probably not relevent to the case at hand). It appears it is except that some asp_'s are repeated more than once, with a different so.name which I notice that is a table name.

Anyway, looked at a number of asp_'s with names that related to the task being investigated and they all appear to be 'little routines' that may be repeated in the application. Example:

-------

Asp_opstd0001

CREATE PROCEDURE asp_opstd0001 @orderno int AS select case count(distinct osr.rowid) - sum(case dd.comp_flg when 'C' then 1 else 0 end ) when 0 then 'Shipped Complete' else case isnull(sum(dd.ship_qty), 0) when 0 then NULL else 'Shipped Partial' e

-------

You're right - this documentation may come in handy in the future.

It appears to me that what I'm looking for is buried in the application code, not as SP's. Sound right to you?

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

If you believe that the T-SQL is dynamic and coming from an application, you might want to examine (Books On Line) the command DBCC INPUTBUFFER it will return up to 255 characters so can be somewhat limited in what you can learn from it. But then again it is another tool you might elect to use.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Chuck Hottle SSCarpal Tunnel Points: 4777 More actions · Answer 4

If users connect to the application using their Windows login, you could filter the Profiler trace on login and limit the results. Then someone could submit transactions and you could profile just them. I've had to do this for various accounting systems and soemtimes the table naming convention is literal enough to help too.

EdA ROC SSCrazy Points: 2734 More actions · Answer 5

OK. Thanks.

I ran profiler again this morning, a few users happened to be active. But, I did see my transactions mixed in with the others. Then I got pulled away. Your reply comes at the right time. I'll run profiler again with the login ID filtered. Should make the detective work much easier.

Thanks.

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]

EdA ROC SSCrazy Points: 2734 More actions · Answer 6

I'm (slowly) figuring out how to use Profiler and getting close - other higher priority projects are demanding my time.

Thanks everyone for your help!

[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]