Home > SQL Server > Procedure metadata function – @@Procid – SQL Server

Procedure metadata function – @@Procid – SQL Server


@@Procid : this function returns the current T-SQL module id. A module can be a stored procedure, user-defined function, or trigger. It returns an integer object id for the  module.

Implementation

Get procid value from non t-sql module.

pic1we get null for module name because it is not a valid module such as SP, UDF or Trigger.

Now, I am using the statement inside a stored procedure.

pic2executing this stored procedure

pic3now we get the id and name for the module. We can use this id to get the module information using catalog views sys.objects and sys.sql_modules.

pic4sys.objects view gives us high level details about the stored procedure. But, sys.sql_modules gives us the module definition and its settings.

we can also use Object_definition() to get the module definition for a given object id.

pic5

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: