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 |
|
rajarajan
Starting Member
48 Posts |
 Posted - 2011-10-11 : 12:14:38
|
| I have one existing table A in sql ,in that I am adding new column I just want to get the what sp are hitting this table if some error happen during the process that information has to be logged in the another Table B with SP name Is there any way or setting available in the table or we have to do some step to get this information |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-11 : 12:26:14
|
| you need to get name of sp which modifies the data in the table? No direct way to do that other than manually adding the logging population step in relevant sps. you can find dependent sp names using sp_depends though------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
rajarajan
Starting Member
48 Posts |
Posted - 2011-10-11 : 12:35:37
|
| we any option in table to log this information |
|
|
|
rajarajan
Starting Member
48 Posts |
Posted - 2011-10-11 : 12:36:40
|
| WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P' and o.name like'%RPT_SLA%') SELECT proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-11 : 13:08:22
|
you can even use select * from sys.sql_modules where definition like '% Tablename %' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rajarajan
Starting Member
48 Posts |
Posted - 2011-10-12 : 10:33:17
|
| good idea |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 11:57:27
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Need Idea