Get size of all tables in database

Sometime you need to get size of all tables in database. This SQL code will help you.

DECLARE @tmpTable TABLE
    (
      [RowCount] INT ,
      [TableName] NVARCHAR(MAX)
    )
DECLARE @ResultTable TABLE
    (
      [Name] NVARCHAR(MAX) ,
      [Rows] INT ,
      [Reserverd] NVARCHAR(MAX) ,
      [Data] NVARCHAR(MAX) ,
      [IndexSize] NVARCHAR(MAX) ,
      [Unused] NVARCHAR(MAX)
    )
INSERT  INTO @tmpTable
        ( [RowCount] ,
          [TableName]
        )
        SELECT  [RowCount] = MAX(si.rows) ,
                [TableName] = so.name
        FROM    sysobjects so ,
                sysindexes si
        WHERE   so.xtype = 'U'
                AND si.id = OBJECT_ID(so.name)
        GROUP BY so.name
        ORDER BY 2 DESC
DECLARE @cursor CURSOR ,
    @tablename VARCHAR(MAX)
SET
@cursor = cursor for
select [TableName] from @tmpTable
OPEN @cursor
WHILE 1 = 1
    BEGIN
        FETCH FROM @cursor INTO @tablename
        IF @@fetch_status <> 0
            BREAK
        INSERT  INTO @ResultTable
                ( [Name] ,
                  [Rows] ,
                  [Reserverd] ,
                  [Data] ,
                  [IndexSize] ,
                  [Unused]
                )
                EXEC sp_spaceused @tablename
    END
SELECT  [Name] ,
        ROUND(( CAST(REPLACE(data, ' KB', '') AS FLOAT) / 1024 ), 2) AS DataInMb ,
        [Rows] ,
        [Reserverd] ,
        [Data] ,
        [IndexSize] ,
        [Unused]
FROM    @ResultTable
ORDER BY ( CAST(REPLACE(data, ' KB', '') AS INT) ) DESC

And here the result after you run this SQL code.

Get size of all tables result

SQL 

Get years, months, days between 2 date

Sometime you want get how many years, months, days from 2 date.

The UDF here help you

CREATE FUNCTION [dbo].[fnGetYMD]
(
    @d1 DATETIME,
    @d2 DATETIME
)
RETURNS NVARCHAR(MAX)
AS
BEGIN  
    IF (@d1 >= @d2) RETURN '0 day'  
    DECLARE @y INT
    DECLARE @m INT
    DECLARE @d INT
    SET @y = DATEDIFF(yy, @d1, @d2) - CASE WHEN (MONTH(@d1) > MONTH(@d2)) OR (MONTH(@d1) = MONTH(@d2) AND DAY(@d1) > DAY(@d2)) THEN 1 ELSE 0 END
    SET @d1 = DATEADD(yy, @y, @d1)
    SET @m = DATEDIFF(m, @d1, @d2) - CASE WHEN DAY(@d1) > DAY(@d2) THEN 1 ELSE 0 END
    SET @d1 = DATEADD(m, @m, @d1)
    SET @d = DATEDIFF(d, @d1, @d2)
    DECLARE @ret NVARCHAR(MAX) = ''
    IF @y <> 0
    BEGIN
        IF @y = 1
            SET @ret = '1 year '
        ELSE
            SET @ret = CONVERT(NVARCHAR(MAX), @y) + ' years '
    END
    IF @m <> 0
    BEGIN
        IF @m = 1
            SET @ret = @ret + '1 month '
        ELSE
            SET @ret = @ret + CONVERT(NVARCHAR(MAX), @m) + ' months '
    END
    IF @d <> 0
    BEGIN
        IF @d = 1
            SET @ret = @ret + '1 day'
        ELSE
            SET @ret = @ret + CONVERT(NVARCHAR(MAX), @d) + ' days'
    END
    RETURN @ret
END

And the result from my birthday until to 11-08-2013

Get years, months, days between

SQL