none
sp_spaceused to apply to more tables of a database - SQL Server 2008 R2

    Question

  • Hi,

    I'd like to use a T-SQL statement with a similar behaviour of the sp_spaceused applied to a table, in order to extract sizing data from more tables of a database, preferably having the spaced used expressed in Mb and not in Kb.

    Thanks

    Thursday, October 31, 2013 7:44 AM

Answers


  •     SELECT TOP 20 u.name, SUM(convert(bigint, i.reserved)*8192/1000000)
        FROM   sysusers u
        JOIN   sysobjects o ON u.uid = o.uid
        JOIN   sysindexes i ON o.id = i.id
        WHERE  i.indid IN (0,1)
        ORDER  BY 2  
     
    --How many pages and how big in MB is a table

    SELECT object_name(a.[object_id]) as TableName,a.index_id,
     isnull(b.name,'HEAP') as IndexName, 
    sum(a.page_count) as pages,sum(a.page_count)*1.0/128 as Mb
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
    group by a.[object_id],a.index_id, b.name
    order by pages desc;

    select OBJECT_NAME(p.object_id ), i.name,p.* 
    from sys.dm_db_partition_stats p
    join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 31, 2013 8:35 AM

All replies

  • Hello ,

       The below script should be able to help you.

    http://gallery.technet.microsoft.com/Determine-size-of-database-6c5373cd

      The only customization you may need to do is , Add one another column with data column divided by 1024 to get the data size in MBs


    Best Regards Sorna

    Thursday, October 31, 2013 8:12 AM

  •     SELECT TOP 20 u.name, SUM(convert(bigint, i.reserved)*8192/1000000)
        FROM   sysusers u
        JOIN   sysobjects o ON u.uid = o.uid
        JOIN   sysindexes i ON o.id = i.id
        WHERE  i.indid IN (0,1)
        ORDER  BY 2  
     
    --How many pages and how big in MB is a table

    SELECT object_name(a.[object_id]) as TableName,a.index_id,
     isnull(b.name,'HEAP') as IndexName, 
    sum(a.page_count) as pages,sum(a.page_count)*1.0/128 as Mb
    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a
        JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
    group by a.[object_id],a.index_id, b.name
    order by pages desc;

    select OBJECT_NAME(p.object_id ), i.name,p.* 
    from sys.dm_db_partition_stats p
    join sys.indexes i on i.object_id = p.object_id and i.index_id = p.index_id 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 31, 2013 8:35 AM
  • Thursday, October 31, 2013 12:34 PM
  • I have my own for this: http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, October 31, 2013 1:08 PM
  • Have a try,

    Create  proc Table_space_used AS
    
    BEGIN
    CREATE TABLE #Temp (Name VARCHAR(1000),Rows Int,Reserved VARCHAR(100), Data VARCHAR(100), Index_size VARCHAR(100), Unused VARCHAR(100))
    DECLARE @Temp1 TABLE (RowID INT IDENTITY(1,1), Table_Name VARCHAR(1000))
    DECLARE @Cnt INT
    DECLARE @Max INT
    SET NOCOUNT ON
    SET @Cnt = 1 
    DECLARE @Table_name VARCHAR(1000)
    INSERT INTO @Temp1
    SELECT Name FROM sysobjects WHERE Xtype = 'U'
    SELECT @Max = MAX(RowID) FROM @Temp1
    WHILE @Cnt <= @Max
    BEGIN
    
                SELECT @Table_name= Table_name FROM @Temp1 WHERE RowID = @Cnt
                INSERT INTO #Temp
    
                EXEC SP_SPACEUSED @Table_name
                SET @Cnt = @Cnt + 1
    
    END
    
    SELECT Name,Rows, cast((REPLACE(data, 'KB','')/1024) as BIGINT)+cast((REPLACE(Index_size, 'KB','')/1024) as BIGINT) AS 'Table Data Size (MB)',
    Reserved,Data,Index_size,Unused FROM #Temp ORDER BY Rows DESC
    --select * from #Temp
    END
    go
    exec Table_space_used


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 31, 2013 1:15 PM