2

Using SSMS2014 w/ SQL Server 2008 R2. I have a table that I am trying to find out how and from where data inside it is coming from. (I didn't create the table and am unable to contact the person who did) I've tried the following:

  1. RMB on the table -> View Dependencies. Result: This comes back empty.enter image description here

  2. I've tried running the following code:

    select object_name(object_id) as DatabaseName, last_user_update, * from sys.dm_db_index_usage_stats where database_id = DB_ID('PackardIntranet') and object_id=object_id('tbl_Johnstone_Packard_SalesReport')

    Result: This just shows NULLs and 0's except when I queried the table previously

  3. I've tried using RedGate's SQL Search using the table name or a column name as a parameter. Result: The only result is the table itself

Am I missing something or can it be that this table was just created and populated once by the person who created it? Or can this be part of a script that is used to create and populate this table? Is there any way I can attempt to find out more about this table?

Improve this question
2
  • 2
    You could do something like this but with an insert trigger instead of update.
    – Aaron Bertrand
    Commented Jan 28, 2015 at 18:10
  • 1
    Why don't you create a Trace, filtering only whats happening with this table? I'm sure you will get some inserts
    – Racer SQL
    Commented Oct 7, 2019 at 18:42

4 Answers 4

Reset to default
2

Index statistics should tell you when the table was last updated, so maybe it really has been just populated once. Of course you could for example add a trigger to the table to track of someone inserts / updates it, but don't forget to remove it later.

Improve this answer
2
  • And of course you could check plan cache, but I don't really expect you to find it there if index statistics doesn't show updates
    – James Z
    Commented Jan 28, 2015 at 17:59
  • I went with JamesZ's idea of the trigger, being that this table is updated only once a month if at all. Thanks.
    – Kram_Koorbse
    Commented Jan 28, 2015 at 20:58
2

If I inherit a database and try to hunt down where DML is coming from, I usually right click the database in SSMS and use the 'Generate Scripts...' menu option and then select Stored procedures and UDFs to be scripted. You can save it in a file or into a new query window and then do a string search for your table.

You should also check any link server references to your server on remote servers for your database and do perform the same task. That's assuming you have access to such information.

If all else fails, you can check profiler and see what applications are sending SQL to that database and ultimately table. It's sort of the brute-force method I admit.

Improve this answer
1
  • This method didn't find what I wanted but useful anyway.
    – Cortado-J
    Commented Oct 7, 2019 at 16:02
2

It's always possible that this table is populated by something external to the database instance that the table is situated on - from an application, via a linked server, from a script on the server. There are probably other possibilities but you get the idea.

The best way I can think to track activity on the table is to run SQL Server Profiler (via the Tools menu of SSMS on the 2012 version). I'm not going to write a tutorial on that software here but you essentially want to filter the activity on the server by the name of the table that you are interested in. If anything updates that table then it should appear in the profiler results.

Improve this answer
2
  • This is the same idea I had in mind. Although if this table only gets updated once a month or something, I'll be waiting for awhile.
    – Kram_Koorbse
    Commented Jan 28, 2015 at 18:20
  • There's a couple of other possibilities. The first is to add a trigger to the table as JamesZ suggested. The second is to look into the Auditing facilities of SQL Server (under the Security folder in SQL Server 2012). I haven't really played with that yet but it seems reasonable to assume that it can audit data modifications and flag some kind of alert for you.
    – paulH
    Commented Jan 28, 2015 at 18:29
-1 
SELECT name
FROM   sys.procedures
WHERE  Object_definition(object_id) LIKE '%WhatYouWantToFind%'

I use this to at least start me in the right direction

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.