1

I have a legacy database that is a mess. I need to investigate a specific table that gets synced/updated using several sources… I need to know when and how the table gets updated.

How can I retrieve all the sources used to update/sync this table? (I guess it’s mainly done through different jobs using SPs).

Is there a way to search in all SP for ‘%table name%’ ?! (is the only way I can think of, is there any other reasonable way?)

Then, I would just need to check which jobs are running those SP, and I could get a better picture…

Chicago1988
  • 970
  • 3
  • 14
  • 35
  • Possible duplicate of [Search text in stored procedure in SQL Server](http://stackoverflow.com/questions/14704105/search-text-in-stored-procedure-in-sql-server) – Sean Lange Oct 26 '16 at 16:16
  • Right click on table => View Dependencies. But that might not be available depending on version of your SQL Server – trailmax Oct 26 '16 at 16:17
  • How do you know the tables are not updated through ad-hoc workloads? – Bridge Oct 26 '16 at 16:52

3 Answers3

2

This will generate list of all procs that refer to a object 'UserInfo':

> select object_name(object_id)  from sys.sql_modules where
> charindex('userinfo',definition)>0

This will not search SSIS or BCP packages which typically are on the file system or in the MSDB database. Many times there are jobs that invoke BCP and/or SSIS packages that update data.

To inspect only procedures you can use:

> select object_name(sm.object_id)  from sys.sql_modules sm inner join
> sys.objects so  on sm.object_id=so.object_id where
> charindex('userinfo',definition)>0  and type='P'
benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • Note this will also include UDFs, views and triggers, not just SPs. However, only triggers from that list are capable of changing the data, but not directly, only if an ad-hoc query is run against the table. – Bridge Oct 26 '16 at 16:54
1

You could try this approach too (looking for ones where is_updated is 1) but I would combine it with other approaches as I haven't found this to be 100% reliable.

DECLARE @TwoPartName nvarchar(500) = '[dbo].[YourTable]';

SELECT referencing_schema_name,
       referencing_entity_name,
       MAX(0 + is_selected) is_selected,
       MAX(0 + is_updated)  is_updated
FROM   sys.dm_sql_referencing_entities (@TwoPartName, 'OBJECT')
       CROSS APPLY sys.dm_sql_referenced_entities (QUOTENAME(referencing_schema_name) + '.'
                                                   + QUOTENAME(referencing_entity_name), 'OBJECT') CA2
WHERE  CA2.referenced_id = OBJECT_ID(@TwoPartName)
GROUP  BY referencing_schema_name,
          referencing_entity_name;
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

You can start by generating the "CREATE" scripts via the "Tasks / Generate Scripts..." command ... enter image description here

...for the stored procedures and functions defined in your database.

enter image description here

Then you can search the generated SQL files to see where your table of interest is referenced.

This will not indicate anything about applications with internal SQL that updates your table, but it is a good start for your analysis.

JohnH
  • 1,920
  • 4
  • 25
  • 32
  • 2
    Or you could just search the database with a query instead of creating a script for every single stored proc. sys.sql_modules. :) – Sean Lange Oct 26 '16 at 16:16
  • 2
    Or you could right click on the table name in SQL Server Management studio and select 'View Dependencies'. It will list all views, stored procedures, triggers, etc that depend on your table. – Sparrow Oct 26 '16 at 16:25