Come ricavare lo spazio usato da ogni tabella

SQLCopia

DECLARE @KB bigint
DECLARE @MB bigint
DECLARE @GB bigint
DECLARE @TB bigint
SET @KB = 1024
SET @MB = @KB * 1024
SET @GB = @MB * 1024
SET @TB = @GB * 1024
 
--SELECT @KB AS [kB], @MB AS [MB], @GB AS [GB], @TB AS [TB]
 
DECLARE @Tables TABLE( 
        [name] sysname not null
       ,[schema] sysname not null
) 
 
DECLARE  @TableSize TABLE ( 
        [name] sysname 
       ,row_count INT
       ,reserved VARCHAR(50) 
       ,data VARCHAR(50)
       ,index_size VARCHAR(50) 
       ,unused VARCHAR(50)
       ,reservedInt bigint
       ,dataInt bigint
       ,index_sizeInt bigint
       ,unusedInt bigint
) 
 
INSERT INTO @Tables  
SELECT Table_Name, Table_Schema  
FROM information_schema.tables  
WHERE TABLE_TYPE = 'BASE TABLE'
 
DECLARE @name sysname  
DECLARE @schema sysname  
DECLARE @objName nvarchar(280)
 
DECLARE cur CURSOR FOR
SELECT [name], [schema]
FROM @Tables  
 
OPEN cur 
FETCH NEXT FROM cur INTO @name, @schema
WHILE @@FETCH_STATUS = 0  
BEGIN  
        SET @name = REPLACE(@name, ']',']]');  
        SET @schema = REPLACE(@schema, ']',']]');  
 
    set @objName = '[' + @schema + '].[' + @name + ']'
 
        IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@objName)) 
        BEGIN
               INSERT INTO @TableSize ([name],row_count,reserved,data,index_size,unused)
               EXEC sp_spaceused @objName, false
        END
        FETCH NEXT FROM cur INTO @name, @schema
END
CLOSE cur 
DEALLOCATE cur 
 
-- update int
UPDATE @TableSize SET
  reservedInt = charindex(' ', reserved)
  ,dataInt = charindex(' ', data)
  ,index_sizeInt = charindex(' ', index_size)
  ,unusedInt = charindex(' ', unused)
 
UPDATE @TableSize SET
  reservedInt = CASE
    WHEN reserved LIKE '% TB' THEN convert(bigint, substring(reserved,0, reservedInt) * @TB) 
    WHEN reserved LIKE '% GB' THEN convert(bigint, substring(reserved,0, reservedInt) * @GB) 
    WHEN reserved LIKE '% MB' THEN convert(bigint, substring(reserved,0, reservedInt) * @MB) 
    WHEN reserved LIKE '% KB' THEN convert(bigint, substring(reserved,0, reservedInt) * @kb) 
    ELSE convert(int, reserved) END
  ,dataInt = CASE
    WHEN data LIKE '% TB' THEN convert(bigint, substring(data,0, dataInt) * @TB) 
    WHEN data LIKE '% GB' THEN convert(bigint, substring(data,0, dataInt) * @GB) 
    WHEN data LIKE '% MB' THEN convert(bigint, substring(data,0, dataInt) * @MB) 
    WHEN data LIKE '% KB' THEN convert(bigint, substring(data,0, dataInt) * @kb) 
    ELSE convert(int, data) END
  ,index_sizeInt = CASE
    WHEN index_size LIKE '% TB' THEN convert(bigint, substring(index_size,0, index_sizeInt) * @TB) 
    WHEN index_size LIKE '% GB' THEN convert(bigint, substring(index_size,0, index_sizeInt) * @GB) 
    WHEN index_size LIKE '% MB' THEN convert(bigint, substring(index_size,0, index_sizeInt) * @MB) 
    WHEN index_size LIKE '% KB' THEN convert(bigint, substring(index_size,0, index_sizeInt) * @kb) 
    ELSE convert(int, index_size) END
  ,unusedInt = CASE
    WHEN unused LIKE '% TB' THEN convert(bigint, substring(unused,0, unusedInt) * @TB) 
    WHEN unused LIKE '% GB' THEN convert(bigint, substring(unused,0, unusedInt) * @GB) 
    WHEN unused LIKE '% MB' THEN convert(bigint, substring(unused,0, unusedInt) * @MB) 
    WHEN unused LIKE '% KB' THEN convert(bigint, substring(unused,0, unusedInt) * @kb) 
    ELSE convert(int, unused) END
--results
SELECT  A.[name], A.[row_count], A.[reserved], A.[data]
  , A.[index_size], A.[unused], B.[schema]
FROM @TableSize A  
  INNER JOIN @Tables B ON (A.[name] = B.[name] ) 
ORDER BY A.[reservedInt] desc, B.[schema], A.[name]