October 1, 2014 at 7:42 am
I've been breaking my poor head over the following problem,I want to show certain meta data of all objects
I got the following code
SELECT
SOS.type_desc
, SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Object Name]
, (SPS.Rows) AS [Row Count]
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END
,sos.object_id,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.objects AS SOS
INNER JOIN sys.partitions AS SPS ON SOS.object_id = SPS.object_id
INNER JOIN sys.allocation_units a ON SPS.partition_id = a.container_id
WHERE 1 = 1
group by SOS.type_desc,SOS.SCHEMA_ID,SOS.NAME,SPS.INDEX_ID,sos.object_id,SPS.Rows
But that doesn't show any information about views and indexes in a database,I was wondering if you could retrieve the same data about views as the code does for tables
October 1, 2014 at 8:34 am
My query along those lines goes via the sys.indexes table:
FROM sys.objects o
INNER JOIN sys.indexes i ON i.object_id = o.object_id
INNER JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
Note you'll get a row for every index in each table.
You won't see views in there unless they're indexed.
October 1, 2014 at 1:27 pm
No, because non-materialized views don't occupy data space in the db. Only the view definition is stored; the data is generated from the underlying tables when the view is used in a query.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 1, 2014 at 1:48 pm
Quick thought, on 2012 this will work
😎
SELECT * FROM sys.all_sql_modules WHERE object_id = OBJECT_ID('dbo.MYVIEW')
SELECT * from sys.dm_exec_describe_first_result_set(N'select * from dbo.MYVIEW',NULL,NULL) as x
October 2, 2014 at 8:32 am
Thank you for your answer,I guess it's not pratical to show that in one select so It might be better to split it up
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply