Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gpc44
Starting Member
35 Posts |
 Posted - 2013-07-23 : 13:53:13
|
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 RegardsNicole  |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 14:00:54
|
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).aspxhttp://msdn.microsoft.com/en-us/library/ms191006(v=sql.105).aspxAlso, you might try the following query to see if any stored procs in your database refers to the table which probably may give some clueSELECT * FROM sys.objects WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%YourTableNameHere%' |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 14:15:00
|
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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-23 : 14:26:38
|
| 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. |
|
|
|
|
|
|
|
|
Insert into Table from ????