AlterWorld AlterWorld - 2 months ago 6
SQL Question

Get schema name of the currently executing stored procedure

Is there a way to retrieve schema owner of a store procedure from within its implementation?

The stored procs are

World.Perform_Task
and
Universe.Perform_Task
. When the stored procedure gets executed, I need to retrieve name of the schema to perform some schema level tasks and also lookup objects (tables, columns, etc.) in that schema.

I tried
Schema_Name()
but it returns the default schema of the logged in user (which is
dbo
) not the schema owner of the stored procedure.

How do I get the schema of the executing stored procedure?

gbn gbn
Answer

Here you go...

OBJECT_SCHEMA_NAME(@@PROCID)

Links to MSDN:

Returns the database schema name for schema-scoped objects

Returns the object identifier (ID) of the current Transact-SQL module.