4

Is it possible to get table name in select statement?

Example:

SELECT Id, "Users" as TableName
FROM Users

I don't want to write "Users" manually but to determine it based on FROM statement.

I'm doing some migrations from one database to another (with different structure), and I have mapping table that stores old table names so that's why I need this (it's less error prone).

Dale K
  • 25,246
  • 15
  • 42
  • 71
zhuber
  • 5,364
  • 3
  • 30
  • 63
  • 6
    Short answer no. Long answer, yes, if you used Dynamic SQL and passed the table name as a **safely** injected value as then you display the value of that parameter in the `SELECT` (effectively doing what you don't want to do). – Thom A Oct 31 '19 at 19:31
  • No, you cannot. – The Impaler Oct 31 '19 at 19:45
  • It's best to think of your database as split into two domains. That of the "database object" and the "database data". "database objects" are tables, views, procedures, users, schemas, etc. "database data" is the information you store in tables. This is why we have "DML" and "DDL" type statements. Crossing between those in the same SQL statement is generally not possible. The desire to do so is often times indicative of bad database design, but not always. In your case it makes sense. – JNevill Oct 31 '19 at 19:50

2 Answers2

3

You can use following sub-query to get this information (works on SQL Server 2012+):

SELECT  *,
(select object_name(ind.object_id)
from sys.fn_PhysLocCracker(%%physloc%%) plc
INNER JOIN SYS.DM_DB_DATABASE_PAGE_ALLOCATIONS(DB_ID(),null,null,null,null) ind
ON ind.allocated_page_file_id = plc.file_id
AND ind.allocated_page_page_id = plc.page_id) as table_name
FROM [your table]

It is painfully slow, caching on the side page allocation will speed things up.

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17
  • As mentioned this is slow like molasses on the tundra of Siberia but it does work...with the caveat that you are only selecting from a single table. If you have a join this will fail. And of course this is an undocumented function which is usually a bad idea. Still +1 since it does actually provide the information being requested. – Sean Lange Oct 31 '19 at 20:52
0

Alternatively, if you want to do it for all tables, you can use following stored procedure:

USE [DB_NAME]
EXEC sp_msforeachtable 'SELECT Id, ''?'' AS [TableName] FROM ?'

Maybe it will be helpful for somebody :)

Gucu112
  • 877
  • 10
  • 12