January 10, 2012 at 2:23 pm
I have a table which has few table names. I need to use the table names in from statement to get data from the tables.
For example: tblNames (Name) has data Sales, Credits
I need to use
Select Avg(Price) from Name
I can manually get the data by running
Select Avg(Price) from Sales
Select Avg(Price) from Credits
But as it has many table names, I need to write like below so that it gives me all together.
Select Avg(Price) from Name
January 10, 2012 at 2:30 pm
Here it is :
declare @cmd varchar(max)
SET @cmd = ''
SELECT @cmd = @Cmd + 'select Avg(Price) from ' + name + '
go
'
from sys.objects where type = 'U'
select @cmd
exec (@cmd)
January 10, 2012 at 2:39 pm
When i run it it gives me error, saying not a valid identifier.
January 10, 2012 at 2:42 pm
Sorry.
What about this :
declare @cmd varchar(max)
SET @cmd = ''
SELECT @cmd = @Cmd + 'select Avg(Price) from [' + s.name + '].[' + o.name + ']
go
'
from sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
where type = 'U'
select @cmd
exec (@cmd)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply