SQLTeam.com | Weblogs | Forums

Dynamic SQL - Displaying table name variable in select statement as string

In my dynamic SQL I am using a variable for the table name, which is populated using a loop in the statement, for a number range. There are 10 tables so it works like this:

Table[Int...10] within the select statement. However is there a way to display the table value as a string so I can show it as a column value rather than as a value I am trying to select from the table? Much like using a placeholder such as:

Select '@TableName' as [Table Name]

When I do this it shows '@TableName' in select statement instead of the actual value.

Is this even possible?

Thanks

yes

hope this helps :slight_smile:
https://www.sqlshack.com/using-variables-in-dynamic-sql/

' Select ' + @TableName + ' as [Table Name] '

Tried that and many different combos of 1/2/3/4 quotes either side of variable, just says incorrect syntax near @TableName or similar.

In my From statement I have:

FROM ' + @TableName + '

Which works

However if I want to show the table name in the select part of the statement, but not physically select it, as it is not a defined DB value, what is the script to do that?

you need extra QUOTES because tableName is a string. Not sure what you are trying to do, but this gets all tables

select 
	' Select ''' + Table_Name + ''' as [Table Name] '
from information_schema.tables

this will run select * against all tables

IF OBJECT_ID('tempdb..#Results') IS NOT NULL 
    DROP TABLE #Results
create table #Results (id int identity(1,1), tablename varchar(100))

declare @Rows int,
		@CurRow int = 1,
		@TableName varchar(100)

insert into #results(TableName)
select  Table_Schema + '.' + Table_Name
from information_schema.tables

set @ROws = @@RowCount

while @CurRow <= @Rows 
begin
	select @TableName = tableName  
	  from #Results
	 where ID = @CurRow

	 exec('select ''' + @TableName + ''' as [TableName], * from ' + @TableName)

	set @CurRow = @CurRow + 1
end

I think it is related to this

Tried that and many different combos of 1/2/3/4 quotes either side of variable, just says incorrect syntax near @TableName or similar.

In my From statement I have:

FROM ' + @TableName + '

Which works

However if I want to show the table name in the select part of the statement, but not physically select it, as it is not a defined DB value, what is the script to do that?

hi

could you please explain what that means !!!

not able to understand !!!

thanks :slight_smile:

The dynamic sql is in a loop, and the table name is the same except for a number at the end, so tablename1, tablename2 etc.

Obviously, in the dynamic sql in the loop, the variable that holds the table name is ued in the FROM statement i.e. FROM @Tablename, however if I wished to show this tablename variable value in the select statement, like a placeholder string i.e.

SELECT '@TableName' AS TableName

And tried using the variable @TableName, then as the variable is a string, and I put it in the SELECT statement conventionally, it will try to "select" the value from the table itself, which is not there.

It's hard to explain this but I needed a way to display the value as a string in itself, not being interpreted as a value that I can select from the table if that makes sense?

I tried the combination as suggested above and it now works, like this:

''' + @TableName + ''' as [Table Name]

That works, thanks, I think I was using the incorrect number of quotes around the variable, meaning it was being interpreted as a value that could be selected from the table, rather than a string in itself.