I start with an object ID and a database ID. Inside a user defined function, I want to get the full name and ID of the database, schema, and object. How can I get the schema ID without using dynamic SQL, which is prohibited inside UDFS?
Note:
- I can get the database name with
DB_NAME
- I can get the object name by using
OBJECT_NAME
because it accepts the db ID as a second parameter - I can get the schema name by using
OBJECT_SCHEMA_NAME
because it also accespts a db ID as a second parameter - It's easy to get the schema ID using dynamic SQL to select from
[db_name_i_want].sys.schemas
, but this is not allowed in a UDF
Update (purpose)
For a possible solution to DB_ID context from farther up call stack, I'm adapting some call stack functions by Gabriel McAdams to work accross multiple databases. His version just pushes the proc ID at each call level onto the CONTEXT_INFO
stream. I've modified this to also push the db ID. I could push the schema ID as well, but then things start to get crowded because CONTEXT_INFO
is limited to 128 bytes. So, I was hoping to be able to reconstruct the schema ID from the db id and object id in the function the creates a view of the call stack (CallStackView).
Code
-- @db_id, @proc_id are saved in a logging function
DECLARE @db_name nvarchar(128) = DB_NAME(@db_id) -- OK
DECLARE @obj_name nvarchar(128) = OBJECT_NAME(@proc_id, @db_id) -- OK
DECLARE @schema_name nvarchar(128) = OBJECT_SCHEMA_NAME(@proc_id, @db_id) -- OK
DECLARE @schema_id int = ? -- What can I do here???
Best Answer
To answer the question literally, no there isn't a way to do this in
T-SQL
without using dynamicSQL
(but see Aaron's suggestion to use a view). You can, however, use aSQLCLR
function (which can be called from aT-SQL
function). The following is demo code to illustrate the principle:T-SQL
script:C#
source: