Get size of all tables in database

 Posted on April 12, 2016

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. [Read More]
SQL 

Get years, months, days between 2 date

 Posted on March 13, 2016

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