Is it possible to get schema ID in another database without using dynamic sql?

Question

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??? 

Answer 1

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;
            }
        }
    }
}

Answer 2

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.

Answer 3

You can create the @Aaron Bertrand view with dynamic sql inside a stored procedure and run it with task manager every night or first time in the morning, as schemas tend to be a little static anyway