С помощью таблицы INFORMATION_SCHEMA.COLUMNS
в SQL можно получить следующее:
TABLE_NAME, COLUMN_NAME
mytab0, mytab0_col0
mytab0, mytab0_col1
.
.
.
mytab1, mytab1_col0
mytab1, mytab1_col1
.
.
.
SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
Я пробовал различные способы, функции и CTE для создания нового столбца, скажем, «пример», который будет использовать значения из TABLE_NAME
и COLUMN_NAME
для проведения того, что эквивалентно оператору выбора, который даст результат, например:
SELECT TOP(1) mytab0_col0 WHERE mytab0_col0 IS NOT NULL FROM mytab0
Затем этот вывод нужно будет ввести в пример столбца.
Я попытался использовать функцию:
CREATE FUNCTION dbo.foo(@input_table VARCHAR(250), @input_columns VARCHAR(250))
RETURNS VARCHAR(250)
AS BEGIN
DECLARE @return_value VARCHAR(250)
DECLARE @tab_name VARCHAR(250)
SET @tab_name = @input_table
SELECT @return_value = @input_columns
FROM @tab_name
WHERE @input_columns IS NOT NULL
RETURN @return_value
END;
Это не удается, потому что FROM @tab_name
ожидает реальную таблицу, а не varchar. Это просто не сработает по многим причинам, хотя это работает:
declare @xvar as VARCHAR(250)
select @xvar = [mytab0_col0]
from mytab0
where [mytab0_col0] is not null
print @xvar
Я также пытался создать CTE, а затем INSERT INTO или использовать функцию с CTE, но безуспешно.
вам нужно исследовать динамические запросы sql. Создайте запрос в переменной nvarchar
, затем вы можете выполнить его с помощью sp_executesql
: geeksforgeeks.org/dynamic-sql, но вы не можете сделать это из функции.
@jarlh Таким образом, это выводит эквивалент правильного оператора выбора (SELECT 'select top(1) ' + COLUMN_NAME + ' from ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
), следующим шагом будет использование этих значений для фактического запуска запроса для обновления столбца. Любые советы о том, как действовать дальше? Обновлено: Потенциально следующим шагом является то, что упомянул @ChrisSchaller?
Итак, вы только что после процедуры, которая будет SELECT
произвольной строкой/столбцом из вашей таблицы, где значение столбца не равно NULL
? Вы не можете делать то, что вам нужно, в FUNCTION
, поскольку вы не можете использовать динамический SQL в функции.
@Larnu По сути, мне нужно использовать значения из TABLE_NAME
и COLUMN_NAME
для создания столбца, который представляет собой вывод каждого значения в TABLE_NAME
в сочетании со всеми возможными COLUMN_NAME
, где оно не равно нулю. Фактически это то же самое, что и оператор SELECT 'select top(1) ' + COLUMN_NAME + ' from ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
, выполняемый построчно.
Итак, вы хотите, чтобы в вашей базе данных был один столбец для каждого значения, отличного от NULL
? Это ужасная идея; разные типы данных плохо сочетаются в одном столбце. Для меня пахнет XY проблемой; какую проблему вы на самом деле пытаетесь решить здесь?
Это не так. Я создаю таблицу, которая имеет следующие элементы, простите за формат csv: имя_таблицы, имя_столбца, тип, пример в качестве заголовков. Пример строки: table1,column0,varchar,hello. Вторая строка будет, например, table1,column1,varchar,baz. Я надеюсь, что это прояснит, @Larnu, не стесняйтесь запускать information.schema.columns самостоятельно. Я буду анализировать все различные типы как строки, поскольку, например, мне не нужны примеры дат.
Я знаю об объектах INFORMATION_SCHEMA
, @clownshoez, хотя я не использую их намеренно, поскольку объекты sys
гораздо лучше, поскольку они включают всю необходимую информацию для объектов в SQL Server... Что касается проблемы, это все еще пахнет XY проблемой для меня. Что вы хотите, чтобы произошло, когда у вас есть столбцы datetime2
, int
, varbinary
и varchar
в одной таблице? Вы не можете неявно (или явно) преобразовать int
в datetime2
, так что же тогда происходит? Ошибка?
Извините, что затягиваю. Проще говоря, мне не нужно заниматься принудительными типами. Мне нужен только пример, который не является нулевым, из этой таблицы в этом конкретном столбце в соответствии с остальной частью строки. Если это не строка или ее нельзя преобразовать в строку, ее можно оставить нулевой. @Larnu, спасибо за терпение. Я не в состоянии обсуждать здесь обоснованность цели, это задача, которую мне дали. Составьте таблицу вручную или иным образом из всех столбцов в нашей БД с примером.
Мне не нужно заниматься принуждением типов" Но вы должны. В одном столбце не может быть двух разных типов данных, столбец должен быть определен как один тип данных. Если у вас задействовано несколько типов данных, то будет использоваться Приоритет типов данных, что, скорее всего, приведет к ошибке.
@Larnu Могу ли я не просто использовать CAST() как varchar и ожидать NULL в случае сбоя?
Вы могли бы, но вы могли бы легко получить некоторые бессмысленные данные и потерю данных.
С удовольствием рискнул. Ручной вариант - это не то, что я хочу делать, у нас есть сотни таблиц и тысячи столбцов, @Larnu.
Честно говоря, это беспорядок, но это работает. Хотя я мог бы попытаться объяснить, более важно, чтобы вы сами поняли, как это работает, и вам нужно потратить на это время. Динамический SQL — это не простой в использовании инструмент, а в чужих руках он опасен. Я сделал это максимально безопасным от инъекционных атак, поэтому, если вы собираетесь изменить его, убедитесь, что вы не открываете пути.
DECLARE @SchemaName sysname,
@TableName sysname;
SET @SchemaName = N'dbo';
SET @TableName = N'YourTable';
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
DECLARE @SelectDelimiter nvarchar(20) = N',' + @CRLF + N' ',
@ValuesDelimiter nvarchar(50) = N',' + @CRLF + N' ';
SELECT @SQL = N'SELECT s.[name] AS SchemaName,' + @CRLF +
N' t.[name] AS TableName,' + @CRLF +
N' V.ColumnName,' + @CRLF +
N' ct.[name] AS DataType,' + @CRLF +
N' V.MaxColumnValue' + @CRLF +
N'FROM (SELECT ' + STRING_AGG(CONVERT(nvarchar(MAX),N'MAX(' + QUOTENAME(c.[name]) + N') AS ' + QUOTENAME(c.[name])),@SelectDelimiter) + @CRLF +
N' FROM ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + N') YT' + @CRLF +
N' CROSS APPLY (VALUES' + STRING_AGG(CONVERT(nvarchar(MAX),N'(N' + QUOTENAME(c.[name],'''') + N', CONVERT(nvarchar(MAX),YT.' + QUOTENAME(c.[name]) + N'))'),@ValuesDelimiter) + N')V(ColumnName,MaxColumnValue)' + @CRLF +
N' JOIN sys.schemas s ON s.[name] = @SchemaName' + @CRLF +
N' JOIN sys.tables t ON s.schema_id = t.schema_id' + @CRLF +
N' JOIN sys.columns c ON t.object_id = c.object_id' + @CRLF +
N' AND c.[name] = V.ColumnName' + @CRLF +
N' JOIN sys.types ct ON c.system_type_id = ct.system_type_id' + @CRLF +
N'WHERE t.[name] = @TableName;'
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE s.[name] = @SchemaName
AND t.[name] = @TableName
GROUP BY s.[name],
t.[name];
--SELECT @SQL; --Your debugging friend
EXEC sys.sp_executesql @SQL, N'@SchemaName sysname, @TableName sysname', @SchemaName, @TableName;
Спасибо за ваше терпение и понимание. Я найду время, чтобы прочитать и реализовать этот сценарий.
Вы пробовали что-то вроде
SELECT 'select ' + COLUMN_NAME + ' from ' + ... FROM INFORMATION_SCHEMA.COLUMNS
?