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

Daniel noyn 1 Reputation point
2022-01-17T19:37:21.003+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Phillips 17,716 Reputation points
    2022-01-17T20:08:42.15+00:00
    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);
    
    0 comments No comments

  2. LiHong-MSFT 10,046 Reputation points
    2022-01-18T08:34:00.38+00:00

    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

    0 comments No comments