TSQL to Generate a date table

DateTable.sql

DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 100;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals

SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- this is just a holding table for intermediate calculations:

CREATE TABLE #dim
(
  [date]       DATE PRIMARY KEY --, 
  --[day]        AS DATEPART(DAY,      [date]),
  --[month]      AS DATEPART(MONTH,    [date]),
  --FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
  --[MonthName]  AS DATENAME(MONTH,    [date]),
  --[week]       AS DATEPART(WEEK,     [date]),
  --[ISOweek]    AS DATEPART(ISO_WEEK, [date]),
  --[DayOfWeek]  AS DATEPART(WEEKDAY,  [date]),
  --[quarter]    AS DATEPART(QUARTER,  [date]),
  --[year]       AS DATEPART(YEAR,     [date]),
  --FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
  --Style112     AS CONVERT(CHAR(8),   [date], 112),
  --Style101     AS CONVERT(CHAR(10),  [date], 101)
);

-- use the catalog views to generate as many rows as we need

INSERT #dim([date]) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) 
			rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM	sys.all_objects AS s1
			CROSS JOIN sys.all_objects AS s2
			-- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;


SELECT * FROM #dim

DROP TABLE #dim