Это решение возникло внезапно, во время вынужденного переезда с MS SQL 2008R2 на MS SQL 2012.
Что имеем
Когда система НСИ построена на реальных таблицах (которые пользователь может физически изменять с помощью предоставляемых ему системных механизмов), поиск по классификатору и использование разных представлений для данных в каждой таблице требуют применение динамических запросов. Это неизбежно. Но при попытке INSERT INTO #table FROM OPENQUERY(LINKED_SERVER,'SELECT <...>') сервер MS SQL 2012 может нам намекнуть, что он хотел бы более явно знать, что мы хотим вернуть. Например, WITH RESULT SETS (раз-два). И это сбивает с толку, поскольку этот запрос динамический и служит для извлечения данных из любой таблицы, какую только пожелает пользователь — мы никогда не знаем, какой набор столбцов вернётся. Перестраивание парадигмы всей системы не обсуждалось, так что решением стало создание статических таблиц с тем же набором столбцов, а потом работа с ними.
Угадывание
Это CLR-функция, которая возвращает угаданную схему всякий раз, когда мы предоставляем ей запрос.
public class tableDefinitionClass
{
[SqlFunction(DataAccess= DataAccessKind.Read)]
public static SqlString getSchemaByQuery(SqlString query)
{
try
{
string schema="";
string sql=query.ToString();
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand command = null;
SqlDataReader reader = null;
while(sql.IndexOf("'{") > 0)
sql = sql.Substring(0,sql.IndexOf("'{") + 1) + sql.Substring(sql.IndexOf("}'") + 1, sql.Length - sql.IndexOf("}'"));
while(sql.IndexOf("{") > 0)
sql = sql.Substring(0,sql.IndexOf("{"))
+ (sql.Substring(sql.IndexOf("{"),sql.IndexOf("}")-sql.IndexOf("{")).Contains("|") ? " 1=0 " : "''")
+ sql.Substring(sql.IndexOf("}") + 1, sql.Length - sql.IndexOf("}") - 1);
try
{
command = new SqlCommand(String.Format(sql),conn);
reader = command.ExecuteReader();
}
catch
{
conn.Close();
return null;
}
DataTable td = reader.GetSchemaTable();
foreach (DataRow myField in td.Rows)
{
string ColumnName="";
string ColumnSize="";
string NumericPrecision="";
string NumericScale="";
string DataTypeName="";
foreach (DataColumn myProperty in td.Columns)
{
switch (myProperty.ColumnName.ToString())
{
case "ColumnName": {ColumnName = myField[myProperty].ToString();break;}
case "ColumnSize": {ColumnSize = myField[myProperty].ToString();break;}
case "NumericPrecision": {NumericPrecision = myField[myProperty].ToString();break;}
case "NumericScale": {NumericScale = myField[myProperty].ToString();break;}
case "DataTypeName": {DataTypeName = myField[myProperty].ToString();break;}
}
}
schema += ColumnName + " " + DataTypeName;
if( DataTypeName == "binary"
|| DataTypeName == "char"
|| DataTypeName == "nchar"
|| DataTypeName == "nvarchar"
|| DataTypeName == "varchar"
|| DataTypeName == "varbinary"
)
schema += "(" + (ColumnSize=="2147483647"?"max":ColumnSize) + ")";
else if(DataTypeName == "datetime2"
|| DataTypeName == "datetimeoffset"
|| DataTypeName == "time"
)
schema += "(" + NumericScale + ")";
else if(DataTypeName == "decimal"
|| DataTypeName == "numeric"
)
schema += "(" + NumericPrecision + "," + NumericScale + ")";
schema += ",";
}
if(schema.Length > 1)
schema = schema.Substring(0, schema.Length - 1);
return schema;
}
catch
{
return null;
}
}
}
Запрос
SELECT SYSDB.dbo._getSchemaByQuery('SELECT * FROM AdventureWorks2008.SalesLT.Customer')
Результат
Примечания
Вопрос к нам: какое соединение использовать нашей функции для выполнения запроса? Передать в саму функцию какой-то ConnectionString?
Ответ нам:
SqlConnection conn = new SqlConnection("context connection=true");Неважно, в контексте какой базы данных мы работаем — он будет утрачен. Поэтому, попросив
USE AdventureWorks2008 GO SELECT SYSDB.dbo._guessSchemaByQuery('SELECT * FROM Address')получим в ответ
NULL. Если мы хотим, чтобы этот запрос заработал, нужно указывать БД и схему явно:SELECT SYSDB.dbo._guessSchemaByQuery('SELECT * FROM AdventureWorks2008.SalesLT.Address')Функция, которая подключается к серверу, должна иметь флажок
[SqlFunction(DataAccess=DataAccessKind.Read)]. В противной случае, могут быть проблемы с подключением из-за безопасности.
Попробовать
Если вы хотите применить эту функцию, проследуйте сюда.