automatically create views for a bunch of tables

Question

I'm looking for a way to automatically create a 1:1 view per table for a bunch of tables. Since there are plenty of tables for which I need a view, it would be time-consuming to create them manually.

Perfect scenario would be a query creating the create view queries for each table given at once.

Answer 1

This should do the trick for the current database. It's still dynamic SQL, but... Note that it does not check to see if the views already exist. You can add a WHERE clause to the cursor query to limit the tables - WHERE t.name = '...' or WHERE t.name IN ('...','....').

DECLARE @SchemaName sysname
DECLARE @SchemaQualifiedTableName sysname
DECLARE @TableName sysname
DECLARE @ColumnCount INT
DECLARE @ColumnID INT
DECLARE @SelectColumn NVARCHAR(500)
DECLARE @sql NVARCHAR(max) = ''

DECLARE QUERYINFO CURSOR FOR
    SELECT
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS SchemaQualifiedTableName,
        t.name AS TableName,
        ccount.ColumnCount,
        c.column_id AS ColumnID,
        CASE WHEN c.column_id <> ccount.ColumnCount
                THEN c.name + ', '
             ELSE c.name
             END AS SelectColumn
    FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id=c.object_id
    INNER JOIN (
        SELECT object_id,COUNT(*) AS ColumnCount
        FROM sys.columns
        GROUP BY object_id
    ) ccount ON t.object_id = ccount.object_id
    ORDER BY t.Name,c.column_id

OPEN QUERYINFO
FETCH NEXT FROM QUERYINFO INTO @SchemaName,@SchemaQualifiedTableName,@TableName,@ColumnCount,@ColumnID,@SelectColumn
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ColumnID = 1
    BEGIN
        SET @sql = 'CREATE VIEW [' + @SchemaName + '].v_' + @TableName + ' AS SELECT ' + @SelectColumn
    END
    ELSE
    BEGIN
        SET @sql = @sql + @SelectColumn
    END  
  
    IF @ColumnID = @ColumnCount
    BEGIN
        SET @sql = @sql + ' FROM ' + @SchemaQualifiedTableName
        EXEC sys.sp_executesql @sql
        SET @sql = ''
    END 
    
    FETCH NEXT FROM QUERYINFO INTO @SchemaName,@SchemaQualifiedTableName,@TableName,@ColumnCount,@ColumnID,@SelectColumn
END

CLOSE QUERYINFO
DEALLOCATE QUERYINFO

Answer 2

I know this is a bit dated but you could use a temporary table with a recursive CTE to string together the columns from sys.columns and sys.tables and then build the view without using a procedure or a cursor. Here is an example. This one only selects one object_id, but you could run it for all of the tables in your db. The only issue might be if you have a table larger than 100 columns. I think the default depth of a CTE is 100 recursive joins.

SELECT t.name AS TableName
        , ccount.ROW_COUNT
        , c.column_id AS ROW_RANK
        , c.name as COL

INTO #VT_TEMP

FROM sys.tables t  INNER JOIN sys.columns c 
ON t.object_id=c.object_id

INNER JOIN (  SELECT  object_id
                    , COUNT(*) AS ROW_COUNT
               FROM sys.columns
               GROUP BY object_id
    ) ccount 
ON t.object_id = ccount.object_id
WHERE t.OBJECT_ID = 245575913
ORDER BY t.Name, c.COLUMN_ID
;



WITH MYVIEW_CTE  ( T_NAME, R_COUNT, R_RANK, TXT )

AS 
  (
    SELECT TABLENAME
         , ROW_COUNT
         , ROW_RANK
         , CAST(COL AS VARCHAR(MAX))

    FROM #VT_TEMP 
    WHERE ROW_RANK = 1

    UNION ALL

    SELECT  V.TABLENAME
          , V.ROW_COUNT 
          , V.ROW_RANK
          , CAST(TXT  + ', ' +  V.COL AS VARCHAR(MAX)) 


    FROM #VT_TEMP V INNER JOIN MYVIEW_CTE C
    ON  V.TABLENAME = T_NAME
    AND V.ROW_RANK = R_RANK + 1
 )  

 SELECT CC.T_NAME
        ,CC.TXT
        , 'CREATE VIEW V_' + CC.T_NAME + ' AS SELECT ' + CC.TXT + ' FROM dbo.' + CC.T_NAME + ' ;' as DDL_View
 FROM MYVIEW_CTE CC INNER JOIN (

 SELECT T_NAME, MAX(R_RANK) AS MX_CNT
 FROM MYVIEW_CTE C
 GROUP BY T_NAME
 ) SC
 ON CC.T_NAME = SC.T_NAME
 AND CC.R_RANK = SC.MX_CNT