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"?
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.
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
