Kaedah SQL untuk mengembalikan data daripada berbilang jadual berdasarkan nama lajur

Question

Saya cuba melakukan sesuatu yang agak pelik tetapi tidak dapat mengetahui cara yang betul untuk mencapainya. Pada asasnya, saya cuba mengekstrak semua jadual/pandangan dan lajur di mana nama lajur adalah seperti rentetan tertentu. Selain itu, saya ingin mengekstrak 1 baris data daripada gabungan jadual/pandangan dan lajur itu. Bahagian kedua ialah tempat saya tersesat. Saya tahu saya boleh mengekstrak jadual/pandangan dan lajur yang diperlukan menggunakan pernyataan pilih di bawah.

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

Dengan cara ini saya akan mendapat sesuatu seperti di bawah

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

Tetapi saya ingin mendapatkan sesuatu seperti ini:

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

Bolehkah sesiapa memberikan sebarang pandangan?

Answer 1

Saya tidak dapat memikirkan cara mudah untuk melakukan ini dalam pertanyaan, tetapi inilah pilihan...

Letakkan senarai lajur dan jadual ke dalam jadual sementara dan jalankannya melalui gelung, menggunakan SQL dinamik untuk memilih baris terbesar bagi setiap jadual.

Saya menambah banyak komen di bawah untuk menjelaskannya.

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 <= (SELECT MAX([RN]) FROM ##TABLELIST) -- Keep the loop running until the end of the list.

BEGIN

UPDATE ##TABLELIST
SET @TABLE  = TableName WHERE [RN] = @ROWCOUNTER -- Get the table name into a variable.

UPDATE ##TABLELIST
SET @COLUMN = ColumnName WHERE [RN] = @ROWCOUNTER -- Get the column name into a variable.


SET @SQL = 'SELECT @SAMPLE = MAX([' + @COLUMN + ']) FROM [' + @TABLE + ']' -- Create SQL statement to pull max column from table specified in variables.
 
EXEC SP_EXECUTESQL @SQL, N'@SAMPLE NVARCHAR(1000) OUTPUT', @SAMPLE OUTPUT -- Execute SQL and put the output into the @SAMPLE variable.


UPDATE ##TABLELIST
SET [Sample] = CAST(@SAMPLE AS NVARCHAR(1000)) WHERE [RN] = @ROWCOUNTER -- Insert the SQL output into the sample column.



SET @ROWCOUNTER = @ROWCOUNTER+1 -- Add one to the row counter to move to the next column and table.

END


SELECT * FROM ##TABLELIST -- Select final output.