В SQL Server как получить значения данных из имен столбцов таблицы, указанных в подзапросе?

Question

Я использую следующий SQL, чтобы перечислить все имена таблиц и столбцов в моей схеме для таблиц, содержащих столбцы, имена которых содержат строку "code", используя следующий запрос SQL-сервера:

SELECT 
    a.table_name, a.column_name from (SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
FROM 
    sys.tables AS t
INNER JOIN 
    sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE 
    c.name LIKE '%code%') a

Результат:

Table Name     Column Name
----------     -----------
Tab_1_name     a_code
Tab_2_name     another_code
Tab_3_name     yet_another_code
and so on...

Я хотел бы теперь запросить фактические данные в столбцах a_code и another_code, используя оболочку, но не могу понять, как получить фактические данные (например, если делать для Tab 1 отдельно, я бы

SELECT a_code FROM Tab_1

получить

a_code
------
value 1
value 2
value 3

но нигде не могу понять или найти, как закодировать внешний запрос, чтобы обернуть его вокруг так, чтобы я получил что-то вроде:

Tab1_name  a_code
---------  ------
tab_name 1 value 1
tab_name 1 value 2
tab_name 2 value 1
tab_name 2 value 2
tab_name 3 value 1
tab_name 3 value 2 ... etc.

то есть отформатированный список всех значений данных во всех столбцах таблицы в моей схеме / БД, имена которых содержат слово "код"?

Answer 1

Без динамического SQL это не может быть сделано в любом случае.

Вот кое-что, чтобы вы начали.

DECLARE @SearchTerm NVARCHAR(50)

SELECT @SearchTerm = '%id%'

SELECT  t.name AS table_name,
        SCHEMA_NAME(schema_id) AS schema_name,
        c.name AS column_name
INTO #temp
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE @SearchTerm
ORDER BY t.name

DECLARE @Query      NVARCHAR(MAX),
        @tableName  NVARCHAR(250),
        @schemaName NVARCHAR(10),
        @columnName NVARCHAR(250)


SELECT @Query = 'SELECT SchemaName = '''',
                        TableName = '''',
                        ColumnName = '''',
                        Value = CONVERT(NVARCHAR(MAX), '''')
                 WHERE 0 = 1'

WHILE(EXISTS(SELECT TOP 1 1 FROM #temp))
BEGIN

    SELECT TOP 1    @tableName = table_name,
                    @schemaName = [schema_name],
                    @columnName = column_name
    FROM #temp

    SELECT @Query = @Query + ' UNION ALL SELECT SchemaName = ''' + @schemaName + ''',
                                                TableName = ''' + @tableName + ''',
                                                ColumnName = ''' + @columnName + ''',
                                                Value = CASE WHEN ' + @columnName + ' IS  NULL THEN ''NULL'' ELSE CONVERT(NVARCHAR(MAX), ' + @columnName + ') END
                                         FROM ' + @tableName

    DELETE #temp
    WHERE table_name = @tableName
    AND @schemaName = [schema_name]
    AND @columnName = column_name

END

PRINT @Query

EXEC  sp_executesql @Query

DROP TABLE #temp

Приведенный выше запрос возвращает следующую информацию: SchemaName TableName ColumnName Значение

Помните, что, возвращая значение для всех совпадающих столбцов, вы, скорее всего, столкнетесь с проблемой преобразования и проблемой нулевого преобразования. В приведенном выше запросе основной случай обрабатывается, но преобразование в 'NVARCHAR' может все же завершиться неудачно с некоторыми комплексными типами столбцов SQL.

Answer 2

use master
GO
declare
  @sql varchar(max) = '',
  @colpattern varchar(100) = '%name%'

;with cteSchema as
(
  select
    object_schema_name(t.object_id) + '.' + quotename(t.name) as tabname,
    quotename(c.name) as colname
  from sys.tables t
  inner join sys.columns c on c.object_id = t.object_id
  where c.name like @colpattern
)
select @sql = 
  (
    select
        cast('
        select cast(t.' as varchar(max)) + t.colname + ' as varchar(1000)) as [value] '
        + ', cast(''' + t.tabname + '.' + t.colname + ''' as nvarchar(2000)) as [source] '
        + ' from ' + t.tabname + ' t
        union all '
    from cteSchema t
    order by t.tabname, t.colname
    for xml path(''), type
  ).value('.', 'varchar(max)') 
  + ' 
  select null, null where 1=0
  order by [source], [value]'

print @sql

exec (@sql)
GO