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