February 26, 2013 at 5:23 pm
Comments posted to this topic are about the item List all tables and their respective information.
March 14, 2013 at 7:34 am
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" 😉
March 14, 2013 at 12:44 pm
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. 🙂
March 14, 2013 at 12:50 pm
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" 😉
March 19, 2013 at 6:51 am
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.
March 19, 2013 at 8:44 am
Got Perry's query to work, nice!
Still not able to get the initial one working.
March 19, 2013 at 10:54 am
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. 🙂
March 19, 2013 at 12:42 pm
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...
October 13, 2015 at 6:11 am
The link to you blog:
http://recursossqlserver.blogspot.com/
is broken at the moment.
Is there a typo?
412-977-3526 call/text
October 21, 2015 at 7:32 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply