patternsqlMinor
Is it possible to get schema ID in another database without using dynamic sql?
Viewed 0 times
withoutsqldatabasepossiblegetanotherusingdynamicschema
Problem
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:
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
Code
Note:
- I can get the database name with
DB_NAME
- I can get the object name by using
OBJECT_NAMEbecause it accepts the db ID as a second parameter
- I can get the schema name by using
OBJECT_SCHEMA_NAMEbecause 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???Solution
To answer the question literally, no there isn't a way to do this in
```
CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004815C9500000000000000000E00002210B010800000A000000060000000000006E2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001829000053000000004000009002000000000000000000000000000000000000006000000C000000802800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007409000000200000000A000000020000000000000000000000000000200000602E72737263000000900200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050290000000000004800000002000500042100007C07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040084000000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A07176F0900000A0772350000700203280A00000A6F0B00000A076F0C00000A72BE0000701E6F0D00000A186F0E00000A076F0F00000A26076F0C00000A166F1000000A6F1100000AA5120000010CDE14072C06076F1200000ADC062C06066F1200000ADC082A011C000002001700576E000A0000000002000B006D78000A000000001E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC010000237E0000380200009002000023537472696E677300000000C8040000C80000002355530090050000100000002347554944000000A0050000DC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000002000000130000000400000001000000010000000200000000000A0001000000000006003700300006007B0068000B008F0000000600BE009E000600DE009E000A0027010C010A0052013C010A00730160010A0085013C010A009F0160010A00A90100010600C50130000A00E3013C010A0009023C010A00160200010A00240260010A003002000106007402300006007A0230000000000001000000000001000100010010001200000005000100010050200000000096003E000A000100FC200000000086184A0010000300000001005000000002005D0011004A00140021004A001A0029004A00100031004A00100039004A00670141008001100049004A001000490090016C015100B50172016100CC0178015100D30167014900FA017F01690020028401810043028C0151005102920169006102960181006A029C0199008602100009004A001000200023001F002E000B00A8012E001300B1012E001B00BA01A001048000000000000000000000000000000000FC000000020000000000000000000000010027000000000002000000000000000000000001000001000000000000003C4D6F64756C653E00434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400476574536368656D614944002E63746F720044617461626173654E616D6500536368656D614E616D650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C520053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E645479706500537472696E6700466F726D6174007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E5175657279006765745F4974656D006765745F56616C756500496E7433320049446973706F7361626C6500446973706F7365000000003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D0020007400720075006500008087530045004C00450043005400200040006900640020003D0020005B0073006300680065006D0061005F00690064005D002000460052004F004D0020007B0030007D002E007300790073002E0073006300680065006D006100730020005700480045005200450020005B006E0061006D0065005D0020003D00200027007B0031007D0027003B000107400069006400000000088C123D0CFB464594BEC04885D418C10008B77A5C561934E089050002080E0E0320000105200101110D04200101088146010004005455794D6963726F736F6674
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 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004815C9500000000000000000E00002210B010800000A000000060000000000006E2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001829000053000000004000009002000000000000000000000000000000000000006000000C000000802800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007409000000200000000A000000020000000000000000000000000000200000602E72737263000000900200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050290000000000004800000002000500042100007C07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040084000000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A07176F0900000A0772350000700203280A00000A6F0B00000A076F0C00000A72BE0000701E6F0D00000A186F0E00000A076F0F00000A26076F0C00000A166F1000000A6F1100000AA5120000010CDE14072C06076F1200000ADC062C06066F1200000ADC082A011C000002001700576E000A0000000002000B006D78000A000000001E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC010000237E0000380200009002000023537472696E677300000000C8040000C80000002355530090050000100000002347554944000000A0050000DC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000002000000130000000400000001000000010000000200000000000A0001000000000006003700300006007B0068000B008F0000000600BE009E000600DE009E000A0027010C010A0052013C010A00730160010A0085013C010A009F0160010A00A90100010600C50130000A00E3013C010A0009023C010A00160200010A00240260010A003002000106007402300006007A0230000000000001000000000001000100010010001200000005000100010050200000000096003E000A000100FC200000000086184A0010000300000001005000000002005D0011004A00140021004A001A0029004A00100031004A00100039004A00670141008001100049004A001000490090016C015100B50172016100CC0178015100D30167014900FA017F01690020028401810043028C0151005102920169006102960181006A029C0199008602100009004A001000200023001F002E000B00A8012E001300B1012E001B00BA01A001048000000000000000000000000000000000FC000000020000000000000000000000010027000000000002000000000000000000000001000001000000000000003C4D6F64756C653E00434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400476574536368656D614944002E63746F720044617461626173654E616D6500536368656D614E616D650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C69747941747472696275746500434C520053797374656D2E44617461004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E4174747269627574650053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E0053716C436F6D6D616E64007365745F436F6E6E656374696F6E004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E645479706500537472696E6700466F726D6174007365745F436F6D6D616E64546578740053716C506172616D65746572436F6C6C656374696F6E006765745F506172616D65746572730053716C506172616D657465720053716C44625479706500416464004462506172616D6574657200506172616D65746572446972656374696F6E007365745F446972656374696F6E00457865637574654E6F6E5175657279006765745F4974656D006765745F56616C756500496E7433320049446973706F7361626C6500446973706F7365000000003363006F006E007400650078007400200063006F006E006E0065006300740069006F006E0020003D0020007400720075006500008087530045004C00450043005400200040006900640020003D0020005B0073006300680065006D0061005F00690064005D002000460052004F004D0020007B0030007D002E007300790073002E0073006300680065006D006100730020005700480045005200450020005B006E0061006D0065005D0020003D00200027007B0031007D0027003B000107400069006400000000088C123D0CFB464594BEC04885D418C10008B77A5C561934E089050002080E0E0320000105200101110D04200101088146010004005455794D6963726F736F6674
Code Snippets
-- Returns 7 from any database context
SELECT dbo.GetSchemaID (N'AdventureWorks2012', N'Production');CREATE ASSEMBLY [CLR] AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103004815C9500000000000000000E00002210B010800000A000000060000000000006E2900000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000001829000053000000004000009002000000000000000000000000000000000000006000000C000000802800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E746578740000007409000000200000000A000000020000000000000000000000000000200000602E72737263000000900200000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C000000006000000002000000100000000000000000000000000000400000420000000000000000000000000000000050290000000000004800000002000500042100007C07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30040084000000010000117201000070730500000A0A066F0600000A730700000A0B07066F0800000A07176F0900000A0772350000700203280A00000A6F0B00000A076F0C00000A72BE0000701E6F0D00000A186F0E00000A076F0F00000A26076F0C00000A166F1000000A6F1100000AA5120000010CDE14072C06076F1200000ADC062C06066F1200000ADC082A011C000002001700576E000A0000000002000B006D78000A000000001E02281300000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C000000CC010000237E0000380200009002000023537472696E677300000000C8040000C80000002355530090050000100000002347554944000000A0050000DC01000023426C6F620000000000000002000001471502000900000000FA2533001600000100000013000000020000000200000002000000130000000400000001000000010000000200000000000A0001000000000006003700300006007B0068000B008F0000000600BE009E000600DE009E000A0027010C010A0052013C010A00730160010A0085013C010A009F0160010A00A90100010600C50130000A00E3013C010A0009023C010A00160200010A00240260010A003002000106007402300006007A0230000000000001000000000001000100010010001200000005000100010050200000000096003E000A000100FC200000000086184A0010000300000001005000000002005D0011004A00140021004A001A0029004A00100031004A00100039004A00670141008001100049004A001000490090016C015100B50172016100CC0178015100D30167014900FA017F01690020028401810043028C0151005102920169006102960181006A029C0199008602100009004A001000200023001F002E000B00A8012E001300B1012E001B00BA01A001048000000000000000000000000000000000FC000000020000000000000000000000010027000000000002000000000000000000000001000001000000000000003C4D6F64756C653E00434C522E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A65637400476574536368656D614944002E63746F720044617461626173654E616D6500536368656D614E616D650053797374656D2E446961676E6F73746963730044656275676761626C6541747using 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;
}
}
}
}Context
StackExchange Database Administrators Q#30317, answer score: 7
Revisions (0)
No revisions yet.