October 26, 2015 at 9:04 am
Hello All:
I have a table and a specific column inside this table. I know this table is being updated, by using sys.dm_db_index_usage_stats, I was able to determine this, by some process (stored procedure / SQL Job / etc), but the problem is, I am not sure what process is doing it.
How would I search our SQL Server 2008 database to find any process that manipulates this table / column (I only care about Inserts / Updates and Deletes, but do not really care for SELECT).
Thanks for any help
October 26, 2015 at 9:10 am
An easy way to do it, is by using SQL Search: https://www.red-gate.com/products/sql-development/sql-search/
You could also query things on sys.sql_modules.
October 26, 2015 at 9:26 am
Thank you for the suggestion, but unfortunately I am unable to install that product (despite making the argument it is helpful for exactly these types of situations). Our corporate software policy prohibits me from installing third party tools, regardless of their cost, usefulness or reputation of the company providing the software. For instance: I was hired to support an ASP.Net MVC application, but I am prohibited from installing MVC on my machine to support the application (figure that one out).
October 26, 2015 at 9:50 am
Then you need to trust your queries. Try tweaking this to get what you need.
SELECT OBJECT_NAME( object_id), *
FROM sys.sql_modules
WHERE definition LIKE '%ColumnName%'
AND definition LIKE '%TableName%'
SELECT OBJECT_NAME( object_id), OBJECT_NAME( referenced_major_id)
FROM sys.sql_dependencies
October 26, 2015 at 10:30 am
Besides searching across text of all database objects, another approach is to search the text of cached query plans. This will narrow the search to only those objects and statements that are executed, and it will include Ad-Hoc statements that would be missed by an object search.
SELECT distinct cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text like '%UPDATE%TableName%ColumnName%';
P.S.
If anyone knows how to include the date/time that each cached plan was created or last used, that would improve the usefulness of this query a great deal.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2015 at 10:49 am
Eric:
Thank you for the query, but this doesn't show me the procedure / job / etc that ran against the object, just the execution plan, correct?
October 26, 2015 at 10:56 am
Thank you Luis....
October 26, 2015 at 11:03 am
Eric M Russell (10/26/2015)
Besides searching across text of all database objects, another approach is to search the text of cached query plans. This will narrow the search to only those objects and statements that are executed, and it will include Ad-Hoc statements that would be missed by an object search.
SELECT distinct cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text like '%UPDATE%TableName%ColumnName%';
P.S.
If anyone knows how to include the date/time that each cached plan was created or last used, that would improve the usefulness of this query a great deal.
Yes, here it also includes database and object name. However, for 'Adhoc' and 'Prepared' plans the object name will be NULL, because it's not related to a database object but rather an executed command coming from something like an application or SSMS.
SELECT distinct db_name(dbid)dbname, object_name(objectid)objname
, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text like '%UPDATE%TableName%ColumnName%';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 26, 2015 at 3:45 pm
Also look at sys.sql_expression_dependencies (or, if SQL 2005 only, at sys.sql_dependencies). Naturally that view is only for non-dynamic SQL references.
Or just right-click on the table name in SSMS and select "View Dependencies" 😉
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply