PVI_COPY

AllTables_space and storage

Mar 25th, 2021
221
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. -- AllTables_space and storage
  4.  -- https://qastack.ru/server/13694/how-do-i-determine-the-size-of-my-sql-server-database
  5.      /*
  6.     vwTableInfo - Table Information View
  7.  
  8.  This view display space and storage information for every table in a SQL Server 2005 database.
  9. Columns are:
  10.     Schema
  11.     Name
  12.     Owner       may be different from Schema)
  13.     Columns     count of the max number of columns ever used)
  14.     HasClusIdx  1 if table has a clustered index, 0 otherwise
  15.     RowCount
  16.     IndexKB     space used by the table's indexes
  17.     DataKB      space used by the table's data
  18.  
  19.  16-March-2008, RBarryYoung@gmail.com
  20.  31-January-2009, Edited for better formatting
  21. */
  22. --CREATE VIEW vwTableInfo
  23. -- AS
  24.  
  25.     SELECT SCHEMA_NAME(tbl.schema_id) as [Schema]
  26.     , tbl.Name
  27.     , Coalesce((Select pr.name
  28.             From sys.database_principals pr
  29.             Where pr.principal_id = tbl.principal_id)
  30.         , SCHEMA_NAME(tbl.schema_id)) as [Owner]
  31.     , tbl.max_column_id_used as [Columns]
  32.     , CAST(CASE idx.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [HasClusIdx]
  33.     , Coalesce( (Select sum (spart.rows) from sys.partitions spart
  34.         Where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
  35.  
  36.     , Coalesce( (Select Cast(v.low/1024.0 as float)
  37.         * SUM(a.used_pages - CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
  38.             FROM sys.indexes as i
  39.              JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
  40.              JOIN sys.allocation_units as a ON a.container_id = p.partition_id
  41.             Where i.object_id = tbl.object_id  )
  42.         , 0.0) AS [IndexKB]
  43.  
  44.     , Coalesce( (Select Cast(v.low/1024.0 as float)
  45.         * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
  46.             FROM sys.indexes as i
  47.              JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
  48.              JOIN sys.allocation_units as a ON a.container_id = p.partition_id
  49.             Where i.object_id = tbl.object_id)
  50.         , 0.0) AS [DataKB]
  51.     , tbl.create_date, tbl.modify_date
  52.  
  53.      FROM sys.tables AS tbl
  54.       INNER JOIN sys.indexes AS idx ON (idx.object_id = tbl.object_id and idx.index_id < 2)
  55.       INNER JOIN master.dbo.spt_values v ON (v.number=1 and v.type='E')
  56. -- PV
  57. ORDER BY
  58. Coalesce( (Select sum (spart.rows) from sys.partitions spart
  59.         Where spart.object_id = tbl.object_id and spart.index_id < 2), 0)
  60.         DESC
RAW Paste Data