How can I search for fields and tables that match a wildcard?

1

Hello!

I want to search/select fields and tables that match a wildcard.

For example:
SELECT LIKE [Col1%]
FROM LIKE TABLE1%

Is that possible?

Thanks!

2

that query doesn't make sense. Are you trying to find all tables that have a column name like a certain value? You can try this then,

Select * from INFORMATION_SCHEMA.Columns
where Column_name like '%Col1%'

3

Thanks but this will not return the values of Col1* columns from a specific table. It will return the values from INFORMATION_SCHEMA where the values are IS_NULLABLE, _ORDINAL_POSITION etc.

I need to return the values from a specific data table (not information_schema) for columns that match a wildcard. Is that possible?

Thanks!

4

yes but might require some dynamic stuff

Select 'select ' + c.name + ' from ' + t.name  
  from sys.tables t
  join sys.columns c on t.object_id = c.object_id
where c.name like '%vmid%'
1 Like
5

Since you don't know what table you are querying, you can use that to generate the statements to check. Something like this:

declare @SQL varchar(max) = ''
   Select @sql = @SQL + 'select ''' + TABLE_SCHEMA + '.' + table_name + ''', * from ' + TABLE_SCHEMA + '.' + table_name + ' where ' + Column_Name + ' is null;'
    from INFORMATION_SCHEMA.Columns
   where Column_name like '%Col1%'

   exec( @SQL)
6

or another bootleg way of doing some forensics sampling

declare @query varchar(max), @tname varchar(100), @cname varchar(100) ;
create table #bootleg(tname varchar(100), 
cname varchar(100), cvalue varchar(max))

DECLARE moonshine CURSOR FOR  
Select 'select top 1 ''' + t.name + ''' as tname, ''' + c.name + 
 ''' as cname, '  + c.name + ' from ' + t.name  
  from sys.tables t
  join sys.columns c on t.object_id = c.object_id
where c.name like '%vmid%'

OPEN moonshine;  
FETCH NEXT FROM moonshine INTO @query;  
WHILE @@FETCH_STATUS = 0  
   BEGIN
		--select @query
   
	   insert into #bootleg
	   exec (@query);

      FETCH NEXT FROM moonshine INTO @query;  
   END;  
CLOSE moonshine;  
DEALLOCATE moonshine;


select * from #bootleg
drop table #bootleg