In SQL Server, how do I retrieve data values from

Question

I am using the following SQL to list all table and column names in my schema for tables containing columns whose names contain the string "code" using the following SQL server query:

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

Result:

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

I would like to now query the actual data in the a_code and another_code columns using a wrapper but cannot see how to get at the actual data (if doing for Tab 1 individually for example, I would

SELECT a_code FROM Tab_1

to get

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

but can't figure out or find anywhere how to code the outer query to wrap around the above such that I would get something along the lines of:

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.

i.e. a formatted list of all the data values in all table columns in my schema/DB whose names contain the word "code"?

Answer 1

Without dynamic SQL, this can't be done by anyway.

Here is something to get you started.

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

The above query return the following information : SchemaName TableName ColumnName Value

Beaware that by returning the value for all matching columns, you are very likely to encounter conversion problem and null conversion problem. In the query above, basic case are handled, but the conversion to 'NVARCHAR' might still fail with some complexes SQL column type.

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