sql - Retrieve primary key value of table when search for values in other column -

i using snippet find specific string in table of database!

declare @searchstring  nvarchar(255) set @searchstring = '%foo%'  declare @sql nvarchar(max)  select @sql = stuff((     select ' union select ' + column_name + ' ' +     ' ' + table_name +      ' ' + column_name + ' ''' + @searchstring + ''''     information_schema.columns     data_type in ('nvarchar', 'varchar', 'char','nchar')     xml path('') ) ,1, 11, '')   exec (@sql) 

but primary key value of these entries!

to primary key of specific table use

select ccu.column_name information_schema.table_constraints tc      inner join information_schema.constraint_column_usage ccu on tc.constraint_name = ccu.constraint_name      tc.constraint_type = 'primary key'      , tc.table_name = 'tbl_table' 

but can't combine both?!

some tables may have more 1 column in primary key , other may have no primary key. makes task bit harder , resulting column needs list of primary key values in 1 column.

select @sql = stuff((     select ' union select ' + quotename(c.column_name) + ', ' +                isnull(stuff((select '+'', ''+cast('+quotename(column_name)+' varchar(max))'                              information_schema.table_constraints tc                                inner join information_schema.constraint_column_usage cu                                  on tc.constraint_name = cu.constraint_name ,                                     tc.table_name = cu.table_name                              cu.table_name = c.table_name ,                                    tc.constraint_type = 'primary key'                              xml path('')), 1, 6, ''), '''''')+' primarykeys'+     ' ' + quotename(c.table_name) +      ' ' + quotename(c.column_name) + ' ''' + @searchstring + ''''     information_schema.columns c     c.data_type in ('nvarchar', 'varchar', 'char','nchar')     order c.table_name     xml path('') ) ,1, 11, '') 

sql fiddle

using tables in fiddle generated query this.

select [col],         cast([id] varchar(max)) primarykeys  [t1]  [col] '%foo%'  union  select [col],         cast([col] varchar(max))+', '+cast([id] varchar(max)) primarykeys  [t2]  [col] '%foo%'  union  select [col],         '' primarykeys  [t3]  [col] '%foo%'