SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert into Table from ????
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gpc44
Starting Member

35 Posts

Posted - 07/23/2013 :  13:53:13  Show Profile  Reply with Quote
I have one SQL Server table (one of many) that night will be filled. Nobody knows which program, service or whatever fills this Table every Night.
Is there a quick way to find out when and where insert/update into this table? (many jobs, many tables, stored procedures and many many linked servers).. of course search-routine, profiler, watch interfaces ???

I am new in this Company, and nobody can give me Feedback about this.

kind Regards
Nicole

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 07/23/2013 :  14:00:54  Show Profile  Reply with Quote
You can use CDC or trace to identify at least some of the information:

http://msdn.microsoft.com/en-us/library/bb522489(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms191006(v=sql.105).aspx

Also, you might try the following query to see if any stored procs in your database refers to the table which probably may give some clue
SELECT * FROM sys.objects WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourTableNameHere%'
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 07/23/2013 :  14:15:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Or you can put a trigger on the table to audit INSERT/UPDATE/DELETE.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 07/23/2013 :  14:26:38  Show Profile  Reply with Quote
In most cases triggers, at the very least, should tell you when the data was inserted if you add a timestamp to whatever you are saving. You can use OBJECT_NAME(@@Procid) to find the name of the stored proc if it is coming in from a stored proc.

The only case where I can think of where this would not work is if the data is coming in via bulk inserts. Unless the FIRE_TRIGGERS option is set, the inserts won't fire the triggers. By default FIRE_TRIGGERS is off.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000