Quick, Easy, Database Views For The Lazy Typist

I am not great at typing. Yes, I had the requisite course on typing in High School (that dates me, I know). Back then it was simply "Typing," now my kids take "Keyboarding," and they take it much earlier than High School. I didn't do that well in my Typing class and still have known bad habits in typing. All that to say, I don't like typing out letter-by-laborious-letter the views that lay atop my Data Warehouse tables. By the way, creating Views is smart even if your business users and analysts are accustomed to accessing the underlying tables.  Views provide a level of abstraction and a way to semantically beautify column names from the underlying tables.

To get around typing, I created some quick SQL to interrogate the INFORMATION_SCHEMA tables and build the views for me.  It includes some tweaks to limit the scope of which tables to create views for and which audit columns I want hidden from the users. Since I am working in a Data Warehouse environment, the tables I want to create are simple SELECT views for my Dimensions, Facts and Bridge tables. As long as your Data Warehouse follows a standard naming practice you should be able to adjust the following SQL in a way that suits your environment.

/******************************************************************
Create Views from Base Dimensional Model tables
******************************************************************/
DECLARE @Schema VARCHAR(120),
  @TableName    VARCHAR(120),
  @ViewName     VARCHAR(120),
  @colName      VARCHAR(120),
  @sql          VARCHAR(max)
  -- Use tables matching my criteriaDECLARE table_cursor
CURSOR static --Use STATIC to make a temp copy of the data for the cursor.
  FOR
    SELECT table_schema,
         table_name,
         ViewName = Replace(table_name,'tbl','')
    FROM   information_schema.tables
    WHERE  table_schema IN ('Core','Sales')
--Loop through the tables
OPEN table_cursor
FETCH next
FROM  table_cursor
INTO  @Schema,
      @TableName,
      @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN 
  --Table Loop
  SET @sql = 'IF OBJECT_ID''' + @Schema + '.' + @ViewName + ''') IS NOT NULL DROP VIEW ' + @Schema + '.' + @ViewName + Char(13)
  PRINT @sql
  EXEC(@sql)
  SET @sql = 'CREATE VIEW ' + @Schema + '.' + @ViewName + ' AS ' + Char(13) + '     SELECT '

  DECLARE col_cursor
  CURSOR static 
    FOR
        SELECT   column_name
        FROM     information_schema.columns
        WHERE    table_schema = @Schema
            AND      table_name = @TableName
            AND      column_name NOT LIKE 'Edw%'
            AND      column_name NOT LIKE '%RowVersionStamp%'
        ORDER BY ordinal_position

-        -Loop through the columns of my table
        OPEN col_cursor
        FETCH next
        FROM  col_cursor
        INTO  @colName
        WHILE @@FETCH_STATUS = 0
        BEGIN
          SET @sql = @sql + Char(13) + '          ' + @colName
          FETCH next
          FROM  col_cursor
          INTO  @colName
          IF @@FETCH_STATUS = 0
          SET @sql = @sql + ',';
        END
    --Column Loop End
    CLOSE col_cursor
    DEALLOCATE col_cursor

  SET @sql = @sql + Char(13) + '     FROM ' + @schema + '.' + @TableName
  PRINT @sql
  PRINT ''
  EXEC(@sql)
  FETCH next
  FROM  table_cursor
  INTO  @Schema,
        @TableName,
        @ViewName
END
--Table Loop END

CLOSE table_cursor
DEALLOCATE table_cursor;