CODEWITHSUNDEEP
sqlsql-serversql-server-2005
kingofoceans
kingofoceans

Reputation: 63

how to select table name from the table itself in a dynamic way?

I want to select table name from the table itself

for example

Select * ,(TableName) from Table1

union

Select *,(TableName) from Table2

i don't want to do it in a static way

Select * ,'TableName' as tbl from Table1

union

Select *,'TableName'  as tbl from Table2

thanks in advance

Upvotes: 2

Views: 13469

Answers (2)

Las Ten
Las Ten

Reputation: 1175

The solution can be dynamic but it's a lot more complex than your first code block up there.

Declare a varchar variable large enough to hold your select script (eg. @script). Declare a cursor for iterating through your tables in sysobjects table. It's something like:

declare cc cursor for select name from sysobjects 
where type='U' and name like '%yourTableNamePatternHere%'
order by name

Go through the records in a while loop using fetch next and @@fetch_status, assemble your big union-query into @script and finally use sp_executesql to have @script executed by the server.

That's it. Here is the script in one listing:

declare @n varchar(100), @script nvarchar(2000)
set @script = ''

declare cc cursor for select name from sysobjects where type='u' and name like '%yourTableNamePatternHere%'
open cc
fetch next from cc into @n

while @@fetch_status = 0
    begin
    if @script <> '' set @script = @script + ' union '
    set @script = @script + 'select <column1>, <column2>, ''' + @n + ''' as tableName from ' + @n 
    fetch next from cc into @n
    end

close cc
deallocate cc

-- print @script
exec sp_executesql @script

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453707

You can't.

There is no metadata function like SELECT OBJECT_NAME(this) you can use.

The FROM Table1 bit is static so what's the problem anyway?

Upvotes: 1

Related Questions