object functions – SQL Studies

How do I tell the stored procedure name from inside the stored procedure?

Kenneth Fisher — Wed, 24 Apr 2013 14:59:04 +0000

I’ve occasionally had the problem of trying to put “smart” logging messages into a process. You know the ones, you’re trying to set up logging for a process and you really want to create a somewhat generic piece of code that you can throw into each stored procedure. Something that will put a useful message into a log table. “Stored procedure xyz was run at abc time”. I don’t know about you but I hate having to hard code the name of each stored procedure, function or trigger into that type of coding. And yes I realize I still have to hard code in a message if it needs to be specific to that piece of code. This just avoids part of the work. I mean anything I can automate is to the better right?

Well during some recent reading I finally found it. @@PROCID. It returns the object id for the current transact-sql module. So for example:

 -- Create a table for my log
CREATE TABLE TestLog (
	Id INT NOT NULL IDENTITY(1,1),
	Calling_Procedure varchar(50),
	[Message] varchar(50)
)
GO

-- Create a stored procedure for my test
CREATE PROCEDURE usp_LoadTestLog AS
BEGIN
   INSERT INTO TestLog 
   VALUES 
      (
         OBJECT_NAME(@@PROCID),  -- Use OBJECT_NAME and @@PROCID to get the name of this SP
         'Test Message'
      )
END
GO

-- Run the stored procedure.
EXEC usp_LoadTestLog
GO
-- Check the table to see the results.
SELECT * FROM TestLog
GO

-- Clean up after myself
DROP TABLE TestLog
DROP PROCEDURE usp_LoadTestLog
GO

You will see that usp_LoadTestLog gets stored in the Calling_Procedure column of TestLog.

Of course there is also ERROR_PROCEDURE but per BOL that only works “where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.”

IsMSShipped

Kenneth Fisher — Wed, 23 Jan 2013 14:51:46 +0000

I just wanted to do a brief highlight of a handy little object property. For those of you who aren’t aware there are a handful of “property” functions available that can give you all sorts of information about your object, database etc. For example DATABASEPROPERTY, DATABASEPROPERTYEX, OBJECTPROPERTY, OBJECTPROPERTYEX, FILEPROPERTY etc.

In this particular case I’m looking at one of the properties available using OBJECTPROPERTY. IsMSShipped returns a 1 if the object was created during the installation of SQL Server.

So if you run the following code you will get back a list of all of the “user” objects in the AdventureWorks2008 database.

 USE AdventureWorks2008
GO

SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 0

And of course the reverse is true.

 USE AdventureWorks2008
GO

SELECT * FROM sys.objects
WHERE objectproperty(object_id,'IsMSShipped') = 1

This code returns all of the objects that were orriginally shipped with SQL Server.

I will agree that this isn’t the most useful property in the world for most people, but it does come in handy if you are the type of person (like me) who ends up querying the system object tables on a fairly regular basis.

fn_OBJECT_TYPE

Kenneth Fisher — Fri, 19 Oct 2012 17:39:18 +0000

I recently started using OBJECT_NAME, OBJECT_SCHEMA_NAME and OBJECT_ID functions; unfortunately I really could have used OBJECT_TYPE but it doesn’t exist (yet?). So for now I wrote one of my own.

FYI I originally wrote it as sp_ and created it in the master db thinking that this would cause it to be available from any database. Unfortunately it appears that this doesn’t work for functions, only stored procedures, views and would you believe tables? I’d love it if someone would explain that one to me sometime.

CREATE FUNCTION dbo.fn_OBJECT_TYPE (@Object_Id INT) 
RETURNS nvarchar
AS
BEGIN
DECLARE @type_desc nvarchar(120)

SELECT @type_desc = type_desc
FROM sys.all_objects
WHERE object_id = @Object_Id

RETURN @type_desc
END
GO