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.
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
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?
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:
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.
