Wednesday, August 31, 2011

Table Size in SQL Server | Find Rows and Disk space Usage

There are two ways to find out the table size;

first option will be to run the system stored procedure “sp_spaceUsed”

  1: EXEC sp_SpaceUsed 'table_name'


Option 2 uses the dmv sys.dm_db_partition_stats



  1: SELECT  t.schema_name + '.' + t.table_name AS table_name
  2:        ,t.index_name
  3:        ,SUM(t.used) AS used_in_kb
  4:        ,SUM(t.reserved) AS reserved_in_kb
  5:        ,SUM(t.tbl_rows) AS rows
  6: FROM    ( SELECT    s.Name schema_name
  7:                    ,o.Name table_name
  8:                    ,COALESCE(i.Name, 'HEAP') index_name
  9:                    ,p.used_page_count * 8 used
 10:                    ,p.reserved_page_count * 8 reserved
 11:                    ,p.row_count ind_rows
 12:                    ,CASE WHEN i.index_id IN ( 0, 1 ) THEN p.row_count
 13:                          ELSE 0
 14:                     END tbl_rows
 15:           FROM      sys.dm_db_partition_stats p
 16:           INNER JOIN sys.objects AS o ON o.object_id = p.object_id
 17:           INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
 18:           LEFT OUTER JOIN sys.indexes AS i ON i.object_id = p.object_id
 19:                                               AND i.index_id = p.index_id
 20:           WHERE     o.type_desc = 'USER_TABLE'
 21:                     AND o.is_ms_shipped = 0 ) AS t
 22: GROUP BY t.schema_name
 23:        ,t.table_name
 24:        ,t.index_name
 25: ORDER BY 5 DESC
 26: 
 27: 

Friday, August 5, 2011

Generating Calendar in SQL Server 2008

Here is the simple query that generates a calendar.

  1: DECLARE @startDay DATE = '20110101'
  2:        ,@endDay DATE = '20130101'
  3: 
  4: ;WITH calendar ( [Date], [Year], [Quarter], [Month], [Day], [DayOfWeek], [MonthName], [DayName], [WeekNumber], isWeekDay )
  5: AS ( SELECT @startDay
  6:            ,YEAR(@startDay)
  7:            ,DATEPART(qq, @startDay)
  8:            ,DATEPART(mm, @startDay)
  9:            ,DATEPART(dd, @startDay)
 10:            ,DATEPART(dw, @startDay)
 11:            ,DATENAME(month, @startDay)
 12:            ,DATENAME(dw, @startDay)
 13:            ,DATEPART(wk, @startDay)
 14:            ,CASE WHEN DATEPART(dw, @startDay) IN ( 1, 7 ) THEN 0 ELSE 1 END
 15:    UNION ALL
 16:    SELECT  DATEADD(dd, 1,[date])
 17:            ,YEAR(DATEADD(dd, 1,[date]))
 18:            ,DATEPART(qq, DATEADD(dd, 1,[date]))
 19:            ,DATEPART(mm, DATEADD(dd, 1,[date]))
 20:            ,DATEPART(dd, DATEADD(dd, 1,[date]))
 21:            ,DATEPART(dw, DATEADD(dd, 1,[date]))
 22:            ,DATENAME(month, DATEADD(dd, 1,[date]))
 23:            ,DATENAME(dw, DATEADD(dd, 1,[date]))
 24:            ,DATEPART(wk, DATEADD(dd, 1,[date]))
 25:            ,CASE WHEN DATEPART(dw, DATEADD(dd, 1,[date])) IN ( 1, 7 ) THEN 0 ELSE 1 END
 26:    FROM     calendar
 27:    WHERE    DATEADD(dd, 1,[date]) < @endDay )
 28: SELECT *
 29: FROM   calendar
 30: OPTION  ( MAXRECURSION 1000 )
 31: 
 32: