List all tables and their respective information.

  • fercasas

    SSC Enthusiast

    Points: 182

    Comments posted to this topic are about the item List all tables and their respective information.

    Fernando Casas Osorio
  • Perry Whittle

    SSC Guru

    Points: 233784

    I prefer mine below

    SELECT OBJECT_NAME(i.object_id) AS TableName

    , ISNULL(i.name, 'HEAP') AS IndexName

    , i.index_id as IndexID

    , i.type_desc AS IndexType

    , p.partition_number AS PartitionNo

    , p.rows AS NumRows

    , au.type_desc AS InType

    , au.total_pages AS NumPages

    , au.total_pages * 8 AS TotKBs

    , au.used_pages * 8 AS UsedKBs

    , au.data_pages * 8 AS DataKBs

    , o.create_date AS CreateDate

    , o.modify_date AS ModifyDate

    FROM sys.objects o INNER JOIN sys.indexes i

    ON o.object_id = i.object_id

    INNER JOIN sys.partitions p

    ON i.object_id = p.object_id AND i.index_id = p.index_id

    INNER JOIN sys.allocation_units au ON

    CASE

    WHEN au.type IN (1,3) THEN p.hobt_id

    WHEN au.type = 2 THEN p.partition_id

    END = au.container_id

    WHERE OBJECT_NAME(i.object_id) <> 'dtproperties'

    AND o.is_ms_shipped <> 1

    ORDER BY TableName, i.index_id

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • fercasas

    SSC Enthusiast

    Points: 182

    Excellent, your script is much more efficient because it has fewer lines and gives the same results.

    Thanks for your input, we are here to help each other. 🙂

    Fernando Casas Osorio
  • Perry Whittle

    SSC Guru

    Points: 233784

    No problem, mine doesn't give object schema but that would be easy to obtain by joining to the sys.schemas catalog.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • m.g.faina

    Grasshopper

    Points: 21

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'i'.

    That's what I get when trying to parse it, 2008 R2.

    The initial one parses OK but give in execution

    Msg 4104, Level 16, State 1, Line 12

    The multi-part identifier "SYS.partitions.INDEX_ID" could not be bound.

    Msg 4104, Level 16, State 1, Line 16

    The multi-part identifier "SYS.partitions.INDEX_ID" could not be bound.

  • m.g.faina

    Grasshopper

    Points: 21

    Got Perry's query to work, nice!

    Still not able to get the initial one working.

  • fercasas

    SSC Enthusiast

    Points: 182

    Hello,

    The script has been executed in SQL Server 2008 R2 and it works fine, please try this again eliminated the "ORDER BY DESC COUNT_ROWS".

    Regards. 🙂

    Fernando Casas Osorio
  • m.g.faina

    Grasshopper

    Points: 21

    same message -

    Msg 4104, Level 16, State 1, Line 12

    The multi-part identifier "SYS.partitions.INDEX_ID" could not be bound.

    Msg 4104, Level 16, State 1, Line 16

    The multi-part identifier "SYS.partitions.INDEX_ID" could not be bound.

    It is line 16

    WHEN SYS.partitions.INDEX_ID < 2 THEN DATA_PAGES ELSE 0 END, 0)) * (SELECT low / 1024 AS VALUE

    Not so sure why it could not be bound...

  • Robert Sterbal

    SSChampion

    Points: 10954

    The link to you blog:

    http://recursossqlserver.blogspot.com/

    is broken at the moment.

    Is there a typo?

    412-977-3526 call/text

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Perry Whittle (3/14/2013)


    No problem, mine doesn't give object schema but that would be easy to obtain by joining to the sys.schemas catalog.

    Thanks for the alternate version.

Viewing 10 posts - 1 through 10 (of 10 total)

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