Script to generate views based on your tables

Hello everyone.

Today I were in a customer and had to do a very manual and repetitive task: create many views with different names from the source tables, but with all the fields defined in the table schema.
What is the reason for that? We´re creating a temporary environment where I´m putting a lot of data and we´ll expose an "interface" using views, that the (power) business user will use to create queries and reports. So this abstraction layer, that in the present moment will reflect almost all tables, will be used to avoid some future rework and clashes between both sides (in case the table structure changes), and to make easier to deal with security.

Now that you´re aware of the context let´s see what I had come up with... I could simply write 50 views with all the fields, but it would take a long time, so I created a quick script that will generate the code I need.

To create that I used a temporary table with N records containing schema and existing tables names, besides a column with the name of the view I´ll create. Using this table in a CTE, I leveraged the CROSS APPLY operator to generate a string based on sys.schemas, sys.columns and sys.objects, creating a comma delimited enumeration of columns based on a XML trick.

Here is the T-SQL code using AdventureWorks2008 for you to play, and maybe, it will be useful in the future.

USE AdventureWorks2008
go

WITH TableView AS
(SELECT UsrSchema, UsrTable, UsrView
FROM ( VALUES
('Sales', 'SalesOrderHeader', 'SalesTemp'),
('Sales', 'SalesOrderDetail', 'SalesDetailsTemp'),
('Production', 'Product', 'ProductTemp'))
AS T(UsrSchema, UsrTable, UsrView))
SELECT
ViewsCode.Instruction
FROM TableView
CROSS APPLY
(SELECT
'
IF OBJECT_ID(''vw_'+ TableView.UsrView +''') IS NOT NULL
DROP VIEW dbo.[vw_'+ TableView.UsrView +']
go

CREATE VIEW dbo.vw_' + TableView.UsrView + '
WITH SCHEMABINDING
AS
SELECT ' +
STUFF(
(SELECT N', ' + QUOTENAME(SC.name) AS [text()]
FROM SYS.columns AS SC
INNER JOIN sys.objects AS SO
ON SO.object_id = SC.object_id
INNER JOIN sys.schemas AS SS
ON SO.schema_id = SS.schema_id
WHERE SO.type = 'U'
AND SO.name = TableView.UsrTable
AND SS.name = TableView.UsrSchema
FOR XML PATH('')), 1, 2, N'') + '
FROM ' + TableView.UsrSchema + '.' + TableView.UsrTable + '
go'
AS Instruction) AS ViewsCode
go

Here is the code generated by running the query...

IF OBJECT_ID('vw_SalesTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesTemp]
go

CREATE VIEW dbo.vw_SalesTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [RevisionNumber], [OrderDate], [DueDate], [ShipDate], [Status], [OnlineOrderFlag], [SalesOrderNumber], [PurchaseOrderNumber], [AccountNumber], [CustomerID], [SalesPersonID], [TerritoryID], [BillToAddressID], [ShipToAddressID], [ShipMethodID], [CreditCardID], [CreditCardApprovalCode], [CurrencyRateID], [SubTotal], [TaxAmt], [Freight], [TotalDue], [Comment], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderHeader
go

IF OBJECT_ID('vw_SalesDetailsTemp') IS NOT NULL
DROP VIEW dbo.[vw_SalesDetailsTemp]
go

CREATE VIEW dbo.vw_SalesDetailsTemp
WITH SCHEMABINDING
AS
SELECT [SalesOrderID], [SalesOrderDetailID], [CarrierTrackingNumber], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice], [UnitPriceDiscount], [LineTotal], [rowguid], [ModifiedDate]
FROM Sales.SalesOrderDetail
go

IF OBJECT_ID('vw_ProductTemp') IS NOT NULL
DROP VIEW dbo.[vw_ProductTemp]
go

CREATE VIEW dbo.vw_ProductTemp
WITH SCHEMABINDING
AS
SELECT [ProductID], [Name], [ProductNumber], [MakeFlag], [FinishedGoodsFlag], [Color], [SafetyStockLevel], [ReorderPoint], [StandardCost], [ListPrice], [Size], [SizeUnitMeasureCode], [WeightUnitMeasureCode], [Weight], [DaysToManufacture], [ProductLine], [Class], [Style], [ProductSubcategoryID], [ProductModelID], [SellStartDate], [SellEndDate], [DiscontinuedDate], [rowguid], [ModifiedDate]
FROM Production.Product
go

Note that this T-SQL is easy and simple to be changed, so If I need to filter out uniqueidentifier columns or those that names start with 'Id%', adding where clauses would do the trick.

This is a quick post, but I hope it became useful for someone, or at least the idea behind it...
You can download the source code here. (Please note that the file contains content in Portuguese and English).

See ya.
Luti