Dynamically creating a view 2

[mSolver]

I need assistance with writing SQL for a view in SQL Server 2005/2008. For this task, I have two tables “table1” and “table2.” I am trying to come up with a procedure to dynamically create a view. There are two challenges. 1. I don’t want to include the key and 2. There is a User Interface which allows the administrator to add columns to one of the tables.

Example:
CREATE TABLE [dbo].[Dim](
[DimCode] [varchar](50) Not Null,
[DimDescription] [varchar](100) NULL)


CREATE TABLE [dbo].[DimAttribute](
[DimCode] [varchar](50) Not Null,
[Att1] [varchar](100) NULL,
[Att2] [varchar](100) NULL,
[Att3] [varchar](100) NULL)

The view would be based on something like:
Select D1.DimCode, D1.DimDescription, D2.Att1, D2.Att2, D2.Att3
From Dim D1
Left Outer Join DimAttribute D2 on D1.DimCode = D2.DimCode

Note: The actual table has custom names, not Att1..Att3, etc.

I want to avoid showing the DimCode two times, so I can’t use Select D2.*. However, since new columns can be added in the DimAttribute table, I need a way to update the view to ensure all columns show when new fields are added to the dimAttribute table.

Any ideas on how this is achievable or thoughts on a better design approach are greatly appreciated.

Regards,
Mike

 

[gmmastros]

With SQL 2005 and up, there are DDL triggers that you may be able to use to accomplish this task.

Most triggers are DML triggers that fire when data changes in a table (insert, update, delete). SQL 2005 added new DDL triggers that fire when the structure of the database changes. In fact, there are server level DDL triggers and database level DDL triggers.

So.... here's what I suggest.

1. Create a stored procedure the build dynamic SQL for the tables involved. This Dynamic SQL should build the "Create View" statement.

2. Write a DDL trigger for "Alter Table". This trigger should call the stored procedure that builds the "Create View" statement.

For Example:

-- Create a table to test this functionality
Create 
Table  TestAlterTableTrigger(
         Id Int Identity(1,1), 
         EyeColor VarChar(20))

Create a procedure to create the view:

Create Procedure BuildView
AS
SET NOCOUNT ON

Declare @Columns VarChar(Max)
Declare @SQL VarChar(Max)

Set @Columns = ''

Select @Columns = @Columns + Column_Name + ','
From   Information_Schema.Columns
Where  Table_Name = 'TestAlterTableTrigger'
       And Column_Name <> 'Id'

If Right(@Columns, 1) = ',' 
	Set @Columns = Left(@Columns, Len(@Columns)-1)

Set @SQL = 'Create View MyView As
Select ' + @Columns + ' From TestAlterTableTrigger'

-- Make sure we remove the view if it already exists.
If Exists(Select * From Information_Schema.Tables Where Table_Name = 'MyView' And Table_Type = 'View')
	Drop View MyView

Exec (@SQL)

Run the code initially to build the view:

Exec BuildView

Create a DDL Trigger to re-build the view:

Create Trigger [AlterMyView]
On DATABASE
For ALTER_TABLE
As
SET NOCOUNT ON

Exec BuildView

Test the view before adding a new column:

Select * From MyView

Modify the table structure:

Alter 
Table  TestAlterTableTrigger 
Add    ShoeSize Decimal(3,1)

Select from the view again to make sure the column was added to it:

Select * From MyView

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I forgot to add the cleanup code:

Drop Trigger AlterMyView On Database
Drop Procedure BuildView
Drop Table TestAlterTableTrigger

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mSolver]

Pure genius George! Thank you for the detailed response, this is far and above what I was hoping for!

Regards,
Mike