Использование INFORMATION_SCHEMA.COLUMNS в SQL для поиска примера из таблицы в SQL Server

С помощью таблицы 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, но безуспешно.

Вы пробовали что-то вроде SELECT 'select ' + COLUMN_NAME + ' from ' + ... FROM INFORMATION_SCHEMA.COLUMNS?

jarlh 23.11.2022 11:53

вам нужно исследовать динамические запросы sql. Создайте запрос в переменной nvarchar, затем вы можете выполнить его с помощью sp_executesql: geeksforgeeks.org/dynamic-sql, но вы не можете сделать это из функции.

Chris Schaller 23.11.2022 11:53

@jarlh Таким образом, это выводит эквивалент правильного оператора выбора (SELECT 'select top(1) ' + COLUMN_NAME + ' from ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS), следующим шагом будет использование этих значений для фактического запуска запроса для обновления столбца. Любые советы о том, как действовать дальше? Обновлено: Потенциально следующим шагом является то, что упомянул @ChrisSchaller?

clownshoez 23.11.2022 11:56

Итак, вы только что после процедуры, которая будет SELECT произвольной строкой/столбцом из вашей таблицы, где значение столбца не равно NULL? Вы не можете делать то, что вам нужно, в FUNCTION, поскольку вы не можете использовать динамический SQL в функции.

Larnu 23.11.2022 12:41

@Larnu По сути, мне нужно использовать значения из TABLE_NAME и COLUMN_NAME для создания столбца, который представляет собой вывод каждого значения в TABLE_NAME в сочетании со всеми возможными COLUMN_NAME, где оно не равно нулю. Фактически это то же самое, что и оператор SELECT 'select top(1) ' + COLUMN_NAME + ' from ' + TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS, выполняемый построчно.

clownshoez 23.11.2022 13:19

Итак, вы хотите, чтобы в вашей базе данных был один столбец для каждого значения, отличного от NULL? Это ужасная идея; разные типы данных плохо сочетаются в одном столбце. Для меня пахнет XY проблемой; какую проблему вы на самом деле пытаетесь решить здесь?

Larnu 23.11.2022 13:20

Это не так. Я создаю таблицу, которая имеет следующие элементы, простите за формат csv: имя_таблицы, имя_столбца, тип, пример в качестве заголовков. Пример строки: table1,column0,varchar,hello. Вторая строка будет, например, table1,column1,varchar,baz. Я надеюсь, что это прояснит, @Larnu, не стесняйтесь запускать information.schema.columns самостоятельно. Я буду анализировать все различные типы как строки, поскольку, например, мне не нужны примеры дат.

clownshoez 23.11.2022 13:37

Я знаю об объектах INFORMATION_SCHEMA, @clownshoez, хотя я не использую их намеренно, поскольку объекты sys гораздо лучше, поскольку они включают всю необходимую информацию для объектов в SQL Server... Что касается проблемы, это все еще пахнет XY проблемой для меня. Что вы хотите, чтобы произошло, когда у вас есть столбцы datetime2, int, varbinary и varchar в одной таблице? Вы не можете неявно (или явно) преобразовать int в datetime2, так что же тогда происходит? Ошибка?

Larnu 23.11.2022 13:42

Извините, что затягиваю. Проще говоря, мне не нужно заниматься принудительными типами. Мне нужен только пример, который не является нулевым, из этой таблицы в этом конкретном столбце в соответствии с остальной частью строки. Если это не строка или ее нельзя преобразовать в строку, ее можно оставить нулевой. @Larnu, спасибо за терпение. Я не в состоянии обсуждать здесь обоснованность цели, это задача, которую мне дали. Составьте таблицу вручную или иным образом из всех столбцов в нашей БД с примером.

clownshoez 23.11.2022 13:45

Мне не нужно заниматься принуждением типов" Но вы должны. В одном столбце не может быть двух разных типов данных, столбец должен быть определен как один тип данных. Если у вас задействовано несколько типов данных, то будет использоваться Приоритет типов данных, что, скорее всего, приведет к ошибке.

Larnu 23.11.2022 13:46

@Larnu Могу ли я не просто использовать CAST() как varchar и ожидать NULL в случае сбоя?

clownshoez 23.11.2022 13:47

Вы могли бы, но вы могли бы легко получить некоторые бессмысленные данные и потерю данных.

Larnu 23.11.2022 13:48

С удовольствием рискнул. Ручной вариант - это не то, что я хочу делать, у нас есть сотни таблиц и тысячи столбцов, @Larnu.

clownshoez 23.11.2022 13:49
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
13
50
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Честно говоря, это беспорядок, но это работает. Хотя я мог бы попытаться объяснить, более важно, чтобы вы сами поняли, как это работает, и вам нужно потратить на это время. Динамический 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;

Спасибо за ваше терпение и понимание. Я найду время, чтобы прочитать и реализовать этот сценарий.

clownshoez 23.11.2022 14:49

Другие вопросы по теме