how to select sample records from any table that has a field named ____ (whatever)

Question

I am working with a large database and sometimes I just need to know if that table has any information for a particular field. For example, there are 54 tables that have a field called "ordernum" and there are six tables that have "updatedOd" etc

Is there a way to write a query that would step through each table that has a field of a particular name and query for a specific value in that field.

For example, find all tables that have a field called ordernum and also have an entry that has a particular ordernum (say 4545)

this query here will select all the tables with the fields

SELECT
TABLE_SCHEMA AS SchemaName
,TABLE_NAME AS TableName
,COLUMN_NAME AS ColumnName
FROM INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = 'ordernum'
ORDER BY
SchemaName
,TableName
,ColumnName;

So, I would like to take the fifty four results from this query and loop through them with something like 'select * from myTableList where ordernum = 4545

thanks

Answer 1

DECLARE @sqlcmd VARCHAR(MAX);

;with cte as (
SELECT
TABLE_SCHEMA 
,TABLE_NAME 
,STRING_AGG(QUOTENAME(COLUMN_NAME),', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION)  as ColumnList
FROM INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE 'Purch%'
GROUP BY TABLE_SCHEMA ,TABLE_NAME 
)
SELECT @sqlcmd = STRING_AGG('SELECT TOP 100 ''' + TABLE_SCHEMA + ''' as TABLE_SCHEMA, ''' + TABLE_NAME + ''' AS TABLE_NAME, ' + ColumnList + ' FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME), CHAR(10)) WITHIN GROUP (ORDER BY TABLE_SCHEMA, TABLE_NAME)
FROM cte

EXEC (@sqlcmd);

Answer 2

Hi,@Daniel noyn
If you only want to query all tables and columns that contain a particular value,then please refer to this link.

Best regards,
LiHong