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