How to retrieve a column name from a table that is stored as a value in another table

Question

I'm pretty new to sql so any help will be much appreciated

I have a table containing a list of table names in a column of a table and I need to retrieve a column called [Last Refreshed] from all the tables listed. The tables all have different structures but they all have the [Last Refreshed] Column. I have managed to insert the tablenames into a sql variable but up to this point I am kind of stuck. I hope I managed to explain what I need but I have attached my code as well.

Declare @tables nvarchar(max)
Declare @sql nvarchar(max)
Declare @cnt int 
DECLARE @Counter int
SET @Counter = 1
DECLARE @RowCount INT


SET @RowCount = (SELECT COUNT(*) 
FROM (
SELECT * FROM  TABLE_LIST1
UNION
SELECT * FROM  TABLE_LIST2) data )

DROP TABLE #DB_DUMMY

CREATE TABLE #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

WHILE ( @Counter <= @RowCount) 
BEGIN


SELECT @tables = FinalTable, @cnt = Row_num from (
SELECT FinalTable , ROW_NUMBER() OVER(ORDER BY FinalTable DESC) AS Row_num  
FROM (
SELECT FinalTable FROM  TABLE_LIST1
UNION 
SELECT FinalTable FROM ABLE_LIST2) data 
group by FinalTable) a
where Row_num = @Counter     



--This part doesnt work
INSERT INTO #DB_DUMMY(TABLENAME,LAST_REFRESHED)
SELECT @tables , [Last Refreshed] from @tables



SET @Counter = @Counter + 1


END


exec(@sql)

I expect to see a list of tablenames as well as the last refresh in the temporary table #DB_DUMMY

score 1 · Accepted Answer · answered Sep 28 '19 at 08:05

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)


SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'


PRINT(@COMMAND)

EXEC(@COMMAND)


SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

your welcome tanks for answer mark please set positive pole for answer thank you — Mahdi Rahimi, Sep 30 '19 at 07:31

score 0 · Answer 2 · answered Sep 28 '19 at 05:46

Just a suggestion You could use a INSERT SELECT

  INSERT INTO #DB_DUMMY(TABLENAME,LAST_REFRESHED)
  SELECT 'TABLE_LIST1', LAST_REFRESHED 
  FROM TABLE_LIST1 
  UNION ALL
  SELECT 'TABLE_LIST2', LAST_REFRESHED 
  FROM TABLE_LIST2
  UNION ALL 
  .....
  SELECT 'TABLE_LISTN', LAST_REFRESHED 
  FROM TABLE_LISTN

score 0 · Answer 3 · answered Sep 28 '19 at 06:18

Try something like this:

declare cur cursor for Select TableName From TABLE_LIST
declare @tablename nvarchar(max)
declare @sqlstring nvarchar(max)

open cur
fetch next from cur into @tablename
while @@fetch_status=0
  begin
    set @sqlstring = 'SELECT ''' + @tablename + ''' AS ''TABLE'', [LAST_REFRESHED] FROM ' + @tablename
    exec sp_executesql @sqlstring
    fetch next from cur into @tablename
  end
close cur
deallocate cur
;

It is the weekend and I don't have access to a database to test on, so it may need some adjusting. Here is a fiddle with the sample code, but it only returns the first table http://sqlfiddle.com/#!18/a5b55b/2 (I think the fiddle execution mechanism interferes with the cursor.)

This answer is based upon the code here: I have the same column in multiple tables, and want to update that column in all tables to a specific value. How can I do this?

Note that there is no need to maintain a list of tables with the column. You can generate it dynamically from INFORMATION_SCHEMA.COLUMNS

Zhorov · Answer 4 · 2019-09-28T08:50:27.707

Another possible approach is to generate and execute a dynamic statement (it's not possible to use a variable for the name of a column or a table):

Table:

CREATE TABLE #TableNames (
   [TableName] nvarchar(128)
)
INSERT INTO #TableNames
   ([TableName])
VALUES
   (N'Table1'),
   (N'Table2'),
   (N'Table3'),
   (N'Table4'),
   (N'Table5')

Statement:

-- Generate statement
DECLARE @stm nvarchar(max) = N''
SELECT @stm = CONCAT(
   @stm,
   N'INSERT INTO #DB_DUMMY (TABLENAME, LAST_REFRESHED) ',
   N'SELECT ''',
   [TableName],
   N''' AS [TableName], [LastRefreshed] FROM ',
   QUOTENAME([TableName]),
   N'; '
)
FROM #TableNames

-- Execute statement
PRINT @stm
EXEC sp_executesql @stm

How to retrieve a column name from a table that is stored as a value in another table

4 Answers4