SQL method to return data from multiple tables based on column names

Question

I'm trying to do something a little weird but can't figure out the right way to accomplish it. Essentially, I'm trying to extract all tables/views and columns where the column name is like a certain string. Beyond that, I want to extract 1 row of data from that table/view and column combination. The second part is where I get lost. I know I can extract the necessary tables/views and columns using the select statement below.

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%email%'
ORDER BY    TableName,ColumnName;

This way I will get something like the following

|ColumnName   |TableName   |
|emailAddress |all_emails  |
           ....

But I want to get something like this:

|ColumnName   |TableName   |Example             |
|emailAddress |all_emails  |[email protected]|
                    ....

Can anyone provide any insight?

Answer 1

I can't think of an easy way to do this in a query, but here is an option...

Put the list of columns and tables into a temporary table and run them through a loop, using dynamic SQL to select the largest row for each table.

I've added a bunch of comments below to explain it.

DECLARE @SQL NVARCHAR(1000)
DECLARE @TABLE NVARCHAR(1000)
DECLARE @COLUMN NVARCHAR(1000)
DECLARE @SAMPLE NVARCHAR(1000)

DROP TABLE IF EXISTS ##TABLELIST

SELECT      COLUMN_NAME AS 'ColumnName'
           ,TABLE_NAME AS  'TableName'
           ,ROW_NUMBER() OVER (ORDER BY COLUMN_NAME,TABLE_NAME)[RN]

INTO ##TABLELIST

FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%email%';



ALTER TABLE ##TABLELIST

ADD [Sample] NVARCHAR(1000) -- Add a column for your sample row.

DECLARE @ROWCOUNTER INT = 1 -- Add a counter for the loop to use.

WHILE @ROWCOUNTER