List all tables and their respective information.

Question

Post reply

List all tables and their respective information.

fercasas

Old Hand

Points: 307
More actions
February 26, 2013 at 5:23 pm

#271006

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

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

I prefer mine below

SELECTOBJECT_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

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

"Ya can't make an omelette without breaking just a few eggs" 😉

fercasas Old Hand Points: 307 More actions · Answer 2

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: 234013 More actions · Answer 3

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

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

"Ya can't make an omelette without breaking just a few eggs" 😉

m.g.faina Grasshopper Points: 21 More actions · Answer 4

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 More actions · Answer 5

Got Perry's query to work, nice!

Still not able to get the initial one working.

fercasas Old Hand Points: 307 More actions · Answer 6

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 More actions · Answer 7

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: 11053 More actions · Answer 8

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 More actions · Answer 9

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.