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?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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