CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
james.mullan
james.mullan

Reputation: 149

Display Table Name in Query Results; SQL Server 2008

I'm trying generate a column based on the title of a table.

Is there a way to display the table name specified in the FROM clause in the query results?

Thanks

Upvotes: 2

Views: 13387

Answers (4)

Michael Green
Michael Green

Reputation: 1491

This query..

select
    st.text
from sys.dm_exec_requests as r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
where r.session_id = @@SPID;

..will give you the raw text of the currently-executing SQL batch. If it's a simple, single-query batch with no joins, or you follow a rigorous naming convention, it should be relatively easy to parse the table name out of st.text. You will have to find a way of including the above into your actual query or batch without subverting its logic.

This is the very essence Cowboy Coding, however. It will be difficult to get right, understand and change. It does, however, answer the question posed. @Gordon Linoff's solution of layering a view over the year-specific tables is the way to go for any professional deployment. The process which creates a new table each year can also re-create the view to include the new table.

Upvotes: 0

anonxen
anonxen

Reputation: 824

I assume that your query always has a single table in the FROM clause. If that is the case, I hope the below code will be helpful:

DECLARE @schema sysname = 'dbo';
DECLARE @tbl sysname = 'tbl_xyz';
DECLARE @Sql nvarchar(Max) = ''
DECLARE @Collist nvarchar(max) = ''


SELECT @Collist = @Collist + c.name + char(10) + ','
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
    JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.name = @tbl AND s.name = @schema

IF LEN(@Collist) = 0 PRINT 'Table not found'
ELSE
    BEGIN
        SET @Sql = 'SELECT ' + char(10) + @Collist + '''' + @schema + '.' + @tbl + '''' + ' as TableName' +char(10)
            + 'FROM ' + @schema + '.' + @tbl


        PRINT @Sql
    END

EXECUTE sp_ExecuteSql @Sql

Upvotes: 0

Tanner
Tanner

Reputation: 22733

The dynamic sql approach, where you could pass in a parameter with the table name (assuming it always has the same structure, length) would be something like this:

Create Script:

create table LOAD_UNISTATS_2013_ACCREDITATION (value1 int, value2 int)

insert into LOAD_UNISTATS_2013_ACCREDITATION (value1, value2)
values (1,2)

Dynamic SQL:

declare @sql nvarchar(200)
declare @tblname nvarchar(50)

set @tblname = 'LOAD_UNISTATS_2013_ACCREDITATION'
set @sql = 'SELECT value1,value2,' + substring(@tblname, 15, 4) 
           + ' as TableName FROM ' + @tblname
execute(@sql)

Demo Sql Fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

From the comments, it seems that you have multiple tables all with the same structure and similar names This is almost always a sign of poor database design. You should have a single table LOAD_UNISTATS_ACCREDITATION with a column for the year.

If you cannot change the database structure, then perhaps you can create a view:

create view v_LOAD_UNISTATS_ACCREDITATION as
    select lua.*, 2013 as year
    from LOAD_UNISTATS_2013_ACCREDITATION lua
    union all
    select lua.*, 2012 as year
    from LOAD_UNISTATS_2012_ACCREDITATION lua
    . . .;

But the answer to your question is "no". There is no automated way to specify a table name in query. And, for a simple reason. The columns in a query are defined in the SELECT but tables are defined in the FROM. A query can have multiple tables. One could imagine a function such as OBJECT_ID for this purpose, but columns are not first class objects in the database.

Upvotes: 2

Related Questions