Post reply

Returning Object_Name or name of tbl

  • July 25, 2011 at 12:51 pm

    #244803

    I'm looking to return the table name for an object_id from the sys_dm_db_index_physical_stats.

    How would I go about that?

  • July 25, 2011 at 1:06 pm

    #1358080

    the object_name() function has a second parameter to pass the database id; i think it got added ina Sp of 2005, and 2008 and above:

    select

    db_name(database_id) As DBName,

    object_name(object_id,database_id) As ObjectName,

    MyAlias.* from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) MyAlias

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 25, 2011 at 1:09 pm

    #1358083

    Try this

    SELECT DB_NAME(PS.database_id) AS DBName,OBJECT_NAME(PS.object_id) AS TableName,PS.*

    FROM sys.dm_db_index_physical_stats

    (DB_ID(), null, NULL, NULL , 'DETAILED') PS

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 25, 2011 at 1:09 pm

    #1358084

    Lowell (7/25/2011)

    the object_name() function has a second parameter to pass the database id; i think it got added ina Sp of 2005, and 2008 and above:

    select

    db_name(database_id) As DBName,

    object_name(object_id,database_id) As ObjectName,

    MyAlias.* from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL) MyAlias

    Curses - you beat me

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • July 25, 2011 at 1:24 pm

    #1358098

    Thanks guys ...

  • July 25, 2011 at 1:25 pm

    #1358102

    NP

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply