Is their a way to get metadata info about views

  • 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

  • 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.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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

  • 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