SQL Query - show table name into coloumn/field

Posted on 2012-09-05
Last Modified: 2012-10-02
Hi There,

Hows it going Experts!

I have a rather simple question is it possible to insert table name into a query?

For example I have a table called: TableA, I want this name (TableA) to appear in my query as a field is this possible?

Please let me know if you want me to clarify


Question by:J3D1-KN1G1-1t
    LVL 29

    Accepted Solution

    I'm not sure I understand.
    Wouldn't your query just be something like:
    SELECT "TABLEA" AS NameOfTable, field1, field2, FROM TABLEA
    LVL 10

    Expert Comment

    select 'TabA' as Table1, ...
    from TabA
    where ...;
    LVL 9

    Expert Comment

    this is sample for  spt_monitor table in master u can try for other also...

    select OBJECT_NAME ( object_id('spt_monitor')) ,* from spt_monitor

    for e.g.
    select OBJECT_NAME ( object_id('TableName')) ,* from TableName
    LVL 9

    Expert Comment

    yah you can store it as a field....

    but you can eaily get it from below query so no need to store it ..

    select name from sys.objects where type='U'

    if you want to store t in some table.....

    select name into my_table from sys.objects where type='U'

    than you can use Pivot functionality to show it as field
    LVL 9

    Expert Comment

    DEclare @tablelist as varchar(max)
    DEclare @tablsql as nvarchar(max)

    select @tablelist =coalesce(@tablelist +',','')+ name from sys.objects where type='U' group by name

    set @tablsql='select '+@tablelist+' from (select * from sys.objects where type=''U'') t pivot (avg(object_id) for name in ('+@tablelist+') ) as mypivot'
    EXEC Sp_ExecuteSql @tablsql,N'@tablsql as nvarchar(max) output',@tablsql output
    LVL 7

    Expert Comment

    Try this query,

    select 'TableA' as [Table] ,* from TableA

    Open in new window


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now