Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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??? 
share|improve this question
1  
Can you tell us a bit about why you want to do this? Give us some context of how/where this would be used? –  Mark Storey-Smith Dec 12 '12 at 23:31
    
@MarkStorey-Smith I'm actually adapting a stack tracing function written by Gabriel McAdams (see my other question: dba.stackexchange.com/questions/30310/…) to work accross multiple databases. I'm storing both the db id and proc id in the CONTEXT_INFO stream, and I was trying to find a way to extract the schema ID as well for the view function (see his code for more detail) –  Joshua Honig Dec 13 '12 at 13:09
    
Interesting stuff, be interested to hear if you get it working. –  Mark Storey-Smith Dec 13 '12 at 13:25

2 Answers 2

up vote 5 down vote accepted

To answer the question literally, no there isn't a way to do this in T-SQL without using dynamic SQL (but see Aaron's suggestion to use a view). You can, however, use a SQLCLR function (which can be called from a T-SQL function). The following is demo code to illustrate the principle:

-- Returns 7 from any database context
SELECT dbo.GetSchemaID (N'AdventureWorks2012', N'Production');

T-SQL script:

CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004815C9500000000000000000E00002210B010800000A000000060000000000006E2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001829000053000000004000009002000000000000000000000000000000000000006000000C000000802800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007409000000200000000A000000020000000000000000000000000000200000602E72737263000000900200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050290000000000004800000002000500042100007C07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040084000000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A07176F0900000A0772350000700203280A00000A6F0B00000A076F0C00000A72BE0000701E6F0D00000A186F0E00000A076F0F00000A26076F0C00000A166F1000000A6F1100000AA5120000010CDE14072C06076F1200000ADC062C06066F1200000ADC082A011C000002001700576E000A0000000002000B006D78000A000000001E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC010000237E0000380200009002000023537472696E677300000000C8040000C80000002355530090050000100000002347554944000000A0050000DC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000002000000130000000400000001000000010000000200000000000A0001000000000006003700300006007B0068000B008F0000000600BE009E000600DE009E000A0027010C010A0052013C010A00730160010A0085013C010A009F0160010A00A90100010600C50130000A00E3013C010A0009023C010A00160200010A00240260010A003002000106007402300006007A0230000000000001000000000001000100010010001200000005000100010050200000000096003E000A000100FC200000000086184A0010000300000001005000000002005D0011004A00140021004A001A0029004A00100031004A00100039004A00670141008001100049004A001000490090016C015100B50172016100CC0178015100D30167014900FA017F01690020028401810043028C0151005102920169006102960181006A029C0199008602100009004A001000200023001F002E000B00A8012E001300B1012E001B00BA01A001048000000000000000000000000000000000FC000000020000000000000000000000010027000000000002000000000000000000000001000001000000000000003C4D6F64756C653E00434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400476574536368656D614944002E63746F720044617461626173654E616D6500536368656D614E616D650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C520053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E645479706500537472696E6700466F726D6174007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E5175657279006765745F4974656D006765745F56616C756500496E7433320049446973706F7361626C6500446973706F7365000000003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D0020007400720075006500008087530045004C00450043005400200040006900640020003D0020005B0073006300680065006D0061005F00690064005D002000460052004F004D0020007B0030007D002E007300790073002E0073006300680065006D006100730020005700480045005200450020005B006E0061006D0065005D0020003D00200027007B0031007D0027003B000107400069006400000000088C123D0CFB464594BEC04885D418C10008B77A5C561934E089050002080E0E0320000105200101110D04200101088146010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A446174614163636573730000000054020F497344657465726D696E6973746963005402094973507265636973650154557F4D6963726F736F66742E53716C5365727665722E5365727665722E53797374656D446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038391053797374656D4461746141636365737301000000042001010E05200101121D05200101112D0600030E0E1C1C042000123507200212390E113D052001011145032000080520011239080320001C070703121D1225080801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000004815C95000000000020000007A0000009C2800009C0A000052534453FB53DD6509D3B74AAE81824B93BABACF01000000633A5C55736572735C5061756C2057686974655C446F63756D656E74735C56697375616C2053747564696F20323031305C50726F6A656374735C4461746162617365365C4461746162617365365C6F626A5C52656C656173655C434C522E7064620000004029000000000000000000005E2900000020000000000000000000000000000000000000000000005029000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000340200000000000000000000340234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000000000000000000000000000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00494010000010053007400720069006E006700460069006C00650049006E0066006F0000007001000001003000300030003000300034006200300000002C0002000100460069006C0065004400650073006300720069007000740069006F006E000000000020000000300008000100460069006C006500560065007200730069006F006E000000000030002E0030002E0030002E003000000030000800010049006E007400650072006E0061006C004E0061006D006500000043004C0052002E0064006C006C0000002800020001004C006500670061006C0043006F0070007900720069006700680074000000200000003800080001004F0072006900670069006E0061006C00460069006C0065006E0061006D006500000043004C0052002E0064006C006C000000340008000100500072006F006400750063007400560065007200730069006F006E00000030002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000030002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000703900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION [dbo].[GetSchemaID] (@DatabaseName [nvarchar](4000), @SchemaName [nvarchar](4000))
RETURNS [int]
AS EXTERNAL NAME [CLR].[UserDefinedFunctions].[GetSchemaID];

C# source:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [SqlFunction
        (
        DataAccess=DataAccessKind.None,
        IsDeterministic=false, 
        IsPrecise=true, 
        SystemDataAccess=SystemDataAccessKind.Read
        )
    ]
    public static int GetSchemaID(string DatabaseName, string SchemaName)
    {
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = String.Format("SELECT @id = [schema_id] FROM {0}.sys.schemas WHERE [name] = '{1}';", DatabaseName, SchemaName);
                cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
                return (int)cmd.Parameters[0].Value;
            }
        }
    }
}
share|improve this answer

Another way would be to create a view that unions the sys.schemas view from each of the databases you want to consider. This may or may not be feasible depending on how many databases you have and the answer to Mark's question in his comment above (why exactly are you doing this and how is the result used).

CREATE VIEW dbo.Schemas
AS
    SELECT 'foo', schema_id, name FROM foo.sys.schemas
    UNION ALL
    SELECT 'bar', schema_id, name FROM bar.sys.schemas
    ...

Now your UDF can just look this up in the view. It's not pretty if your database list changes constantly, but you can set up a background job whose sole responsibility is to re-create the view based on the current list of databases.

share|improve this answer
    
See the update to the question for why I'm trying to do this. –  Joshua Honig Dec 13 '12 at 13:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.