I need to identify the current stored procedure to update a Log table.
In MSSQL I would be using the following command:
SET @vProcName = OBJECT_NAME(@@PROCID) ;
Is there an equivalent in Teradata ?...
Thank you for your assistance.
you can verify all the queries that are currently being executed using the viewpoint or the performance monitor. if you want to know those which were executed today you can verify the DBQL tables in DBC database, if the DBQL has been enabled. DBC.DBQLObjTBL holds all the objects that were accessed today. select * from dbc.dbqlobjtbl where objectdatabasename=<databasename> and objecttablename=<procedure name>
Thank you for your response. However, my question is how can I obtain the SP name within a SQL stored procedure with a sql statement of some sort, so I can log the start/end, error etc... of that sp process dynamically ?.. This is to avoid hard coding the name in the SP itself...
---Avoiding hard coding of procedure , if there is a call of procedure inside another one ...... I dont know how the tracking is done
Probably you may need more info too besides proc name.Will it not be more handy to code the logic inside the procedure itself, logging the information you want in a tracker table such as procedure name, status,start time, end time etc. Get the status by looking at start and end time.
It can be something like this :
SET Currently_Running = 'Procedure abc running' ;
Then log info into a tracker table, where you can use it any time.
Thank you for your feedback. That is "plan B".
There could be 100's of SPs, so the idea is to use a generic command that would retrieve the name of the SP when it is executed rather than have the name hardcoded in the SP. If the SP name was to change, (e.g.: mysp_v1.2 => mysp_v1.3 etc...) the code would take care of getting the current name without having to remember to change it manually.
BTW there is a standard command in MSSQL for this.