fn_TableSizes

USE [DBZOrders_PROD]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_TableSizes]    Script Date: 12/27/2010 20:33:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_TableSizes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_TableSizes]
GO

USE [DBZOrders_PROD]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_TableSizes]    Script Date: 12/27/2010 20:33:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Andrew
-- Create date: Monday December 27,2010 7:55 PM
-- Description:
-- =============================================
CREATE FUNCTION [dbo].[fn_TableSizes]
(
)
RETURNS
@table_sizes TABLE
(   -- Add the column definitions for the TABLE variable here
    [table_name] [nvarchar](50),
    [size_mb] [decimal](18, 2),
    [row_count] [int]
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    DECLARE tables_cursor CURSOR
       FOR
        select TOP 100 percent
            t.object_id,t.TableName,t.TableSize
        from
            (select
                  sys.objects.object_id,
                  sys.objects.name as TableName,
                  sum(reserved_page_count) * 8.0 / 1024 as TableSize,
                  sys.objects.type
            from
                  sys.dm_db_partition_stats, sys.objects
            where
                  sys.dm_db_partition_stats.object_id = sys.objects.object_id
            group by sys.objects.object_id,sys.objects.name,sys.objects.type) t

        WHERE t.type = 'u'
        ORDER BY t.TableSize desc

    OPEN tables_cursor;

    DECLARE @sys_id int;
    DECLARE @tablename sysname;
    DECLARE @tablesize decimal(18, 2);
    DECLARE @tablesize_desc varchar(20);

    FETCH NEXT FROM tables_cursor INTO @sys_id,@tablename,@tablesize;
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN;

        SELECT @tablesize_desc = CAST(@tablesize AS varchar(20)) + ' MB';
        DECLARE @Result int;

        --EXECUTE @Result = [proc_GetTableRowCount] @tablename

        INSERT INTO @table_sizes
           ([table_name]
           ,[size_mb]
           ,[row_count])
        VALUES
            (@tablename,
            @tablesize,
            @Result)


       FETCH NEXT FROM tables_cursor INTO @sys_id,@tablename,@tablesize;
    END;
    --PRINT 'The indexes on all tables have been rebuilt.';
    CLOSE tables_cursor;
    DEALLOCATE tables_cursor;


    RETURN
END

GO