Menu
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?
860
SELECT [schema] = SCHEMA_NAME([schema_id]), name FROM sys. procedures; For a specific database, you can just change the context to that database first, or change Marc's query slightly (my queries are no good in this case because they rely on functions that are context-sensitive):
You can get a list of the schemas using an SSMS or T-SQL query. To do this in SSMS, you would connect to the SQL instance, expand the SQL database and view the schemas under the security folder. Alternatively, you could use the sys. schemas to get a list of database schemas and their respective owners.
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.
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
