PVI_COPY

11_1_4_SQLServer_Размер таблиц

Apr 12th, 2021
169
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. -- 11_1_4_SQLServer_Размер таблиц
  3.  
  4.  
  5. --Набор скриптов для знакомства с SQL Server
  6. --https://infostart.ru/1c/articles/1128594/
  7.  
  8. SELECT  'Select ''' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
  9.         + LEFT(o.name, 128) + ''' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name
  10.         + ';' AS ' Script generator to get counts for all tables'
  11. FROM    sys.objects o
  12. WHERE   o.[type] = 'U'
  13. ORDER BY o.name;
  14.  
  15.  
  16. SELECT  @@ServerName AS Server ,
  17.         DB_NAME() AS DBName ,
  18.         OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,
  19.         OBJECT_NAME(p.object_id) AS TableName ,
  20.         i.Type_Desc ,
  21.         i.Name AS IndexUsedForCounts ,
  22.         SUM(p.Rows) AS Rows
  23. FROM    sys.partitions p
  24.         JOIN sys.indexes i ON i.object_id = p.object_id
  25.                               AND i.index_id = p.index_id
  26. WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
  27.                              -- This is key (1 index per table)
  28.         AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
  29. GROUP BY p.object_id ,
  30.         i.type_desc ,
  31.         i.Name
  32. ORDER BY SchemaName ,
  33.         TableName;
  34.  
  35.  
  36. SELECT  @@ServerName AS ServerName ,
  37.         DB_NAME() AS DBName ,
  38.         OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
  39.         OBJECT_NAME(ddps.object_id) AS TableName ,
  40.         i.Type_Desc ,
  41.         i.Name AS IndexUsedForCounts ,
  42.         SUM(ddps.row_count) AS Rows
  43. FROM    sys.dm_db_partition_stats ddps
  44.         JOIN sys.indexes i ON i.object_id = ddps.object_id
  45.                               AND i.index_id = ddps.index_id
  46. WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
  47.                               -- This is key (1 index per table)
  48.         AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
  49. GROUP BY ddps.object_id ,
  50.         i.type_desc ,
  51.         i.Name
  52. ORDER BY SchemaName ,
  53.         TableName;
  54.  
  55.  
  56. ------
  57. -- размер хранимых данных в таблицах  
  58.  
  59. SELECT
  60.     a3.name AS [schemaname],
  61.     a2.name AS [tablename],
  62.     a1.rows as row_count,
  63.     (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved],
  64.     a1.data * 8 AS [data],
  65.     (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size],
  66.     (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused]
  67. FROM
  68.     (SELECT
  69.         ps.object_id,
  70.         SUM (
  71.             CASE
  72.                 WHEN (ps.index_id < 2) THEN row_count
  73.                 ELSE 0
  74.             END
  75.             ) AS [rows],
  76.         SUM (ps.reserved_page_count) AS reserved,
  77.         SUM (
  78.             CASE
  79.                 WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
  80.                 ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
  81.             END
  82.             ) AS data,
  83.         SUM (ps.used_page_count) AS used
  84.     FROM sys.dm_db_partition_stats ps
  85.     GROUP BY ps.object_id) AS a1
  86. LEFT OUTER JOIN
  87.     (SELECT
  88.         it.parent_id,
  89.         SUM(ps.reserved_page_count) AS reserved,
  90.         SUM(ps.used_page_count) AS used
  91.      FROM sys.dm_db_partition_stats ps
  92.      INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  93.      WHERE it.internal_type IN (202,204)
  94.      GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
  95. INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
  96. INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
  97. WHERE a2.type <> N'S' and a2.type <> N'IT'
  98. ORDER BY reserved DESC
RAW Paste Data