TSQL - Identify the table name as a column of the single or multiple tables in a select statement WITHOUT HARDCODING it as part of the query

I need to display the table name in my select statement.

select col1, table_name_1 as table_name  from table_name_1 

Which I insert into another table.

My problem is I have to do this dynamically not as explained by this question.

Display the table name in the select statement

Not sure if this available with a single key word like for an eg. the currentdate we could use something like GETDATE().

Thank you for your answer.

1 answer

  • answered 2019-08-24 06:13 Shekar Kola

    I don't think there is inbuilt function available in T-SQL other than OBJECT_NAME (object_id('TableName')) which required to hard-code Table name.

    However, as workaround, following query could fulfill your requirements:

    Declare @tbname varchar(50); 
    Declare @SelectQuery varchar(2000); 
    
    set @tbname = 'YourTable';
    set @SelectQuery = 'select *, OBJECT_NAME (object_id(' + '''' + @tbname + '''' +')) from ' + @tbname;
    --print @SelectQuery;
    exec (@SelectQuery);