local table missing data

I stole this from the V11 migration customization report. All you have to do is supply the name of your local table, this will then find all procedures that reference the table and then you can check the procedure to see if it is doing a truncate or delete. At least one piece of the puzzle.

Marty

/*

Use this query to find any odject that may reference another object

*/

DECLARE     @cur_like VARCHAR(100)

SET @cur_like = '%<Local Table Name Here>%' -- Include the wildcards

SELECT DISTINCT

      CASE WHEN OBJECTPROPERTY(id, 'IsProcedure') = 1 THEN 'Procedure'

            WHEN OBJECTPROPERTY(id, 'IsTrigger') = 1 THEN 'Trigger'

            WHEN OBJECTPROPERTY(id, 'IsView') = 1 THEN 'View'

            WHEN OBJECTPROPERTY(id, 'IsScalarFunction') = 1

            THEN 'Scalar_Function'

            WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1 THEN 'Table_Function'

            ELSE 'Unknown'

      END AS category ,

      OBJECT_NAME(id) AS Detail

FROM

      syscomments

WHERE

      [text] LIKE @cur_like

ORDER BY

From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Dale Aucoin
Sent: Thursday, July 05, 2012 4:35 PM
To: Martin A. Jones
Subject: [Tessitura Technical Forum] local table missing data

I have a bit of a mystery.  One of our local tables has been truncated and I can't figure out how or why.  

It is always possible that someone deleted the data through the application or with a query but its not likely.

Does anyone have any ideas of how I can investigate this further?

I can probably restore the data but I don't want this to happen again.

Thanks,
Dale 

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!