Wednesday, September 01, 2004

Generate SQL Server Views (MyGeneration)

Added a new MyGeneration template archive that contains a template that generates views from your tables (SQL Server) - Generate SQL Server Views.

Returns NULL's if blank string and column allows NULL's. Also breaks down dates into component parts (i.e. Month, Day, Year) and to other formats:

  • Short Date (mon dd yyyy)
  • Short Time (hh:mm)
  • US Date (mm/dd/yyyy)
  • ANSII Date (yyyy.mm.dd)
  • UK Date (dd/mm/yyyy)
  • German Date (dd.mm.yyyy)
  • Italian Date (dd-mm-yyyy)

Example Output:

USE [news]
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
         WHERE TABLE_NAME = 'vw_News')
   DROP VIEW vw_News
GO

CREATE VIEW [vw_News]
AS
SELECT
		[ID] AS [NewsID],
		NULLIF ([Title], '') AS [News Title],
		NULLIF ([Url], '') AS [News Url],
		[Added] AS [Date Added],
		CONVERT ( char(11) , [Added] , 113) AS [Date Added_ShortDate],
		CONVERT ( char(5) , [Added] , 108) AS [Date Added_ShortTime],
		CONVERT ( char(10) , [Added] , 101) AS [Date Added_USDate],
		CONVERT ( char(10) , [Added] , 102) AS [Date Added_ANSIIDate],
		CONVERT ( char(10) , [Added] , 103) AS [Date Added_UKDate],
		CONVERT ( char(10) , [Added] , 104) AS [Date Added_GermanDate],
		CONVERT ( char(10) , [Added] , 105) AS [Date Added_ItalianDate],
		{ fn DAYNAME ([Added])  } AS [Date Added_DayName],
		DAY ( [Added] ) AS [Date Added_Day],
		{ fn MONTHNAME ([Added])  } AS [Date Added_MonthName],
		MONTH ( [Added] ) AS [Date Added_Month],
		YEAR ( [Added] ) AS [Date Added_Year]
FROM [news]
GO
PRINT 'View Creation: vw_News Succeeded'
GO

No comments: