T-SQL : Script to create view with columns from a select statement

Question

I have some bulk views to create for an entire database.

To create a view the general syntax is as follows:

CREATE VIEW [TABLE_NAME] 
AS 
    SELECT [COLUMN1], [COLUMN2], [COLUMN3], [COLUMN4]
    FROM [TABLE_NAME]
    WITH CHECK OPTION;

I would like to set the column names in the script above by querying the column names ([COLULMN1], [COLUMN2], etc) from INFORMATION_SCHEMA.COLUMNS.

Is there a way to achieve this by table name?

Answer 1

COALESCE is your friend good programmer. What you want to do is get a csv list of COLUMNS. Then using dynamic sql you can auto generate the rest of the code.

 declare @columns AS VARCHAR(MAX)

SELECT @COLUMNS = NULL


select  @COLUMNS = coalesce(@columns+',','')+c.name from syscolumns as c
inner join sysobjects as o on c.id = o.id
WHERE O.NAME = 'change me to your table name'

SELECT @COLUMNS
SELECT ' CREATE VIEW ' + 'COOL VIEW NAME' + ' AS ' +
' SELECT ' + @COLUMNS +
 ' FROM '+ ' change me to your table name '+
 ' WITH CHECK OPTION;' 

EDIT

I purposely didn't declare the view anywhere. If you want to declare the view just execute the scripts like so. BUT YOU SHOULD NEVER just execute code on your servers without reading it all I purposely excluded the execution part as I think it is bad judgement just to cut and paste code and execute it without understanding/testing.

DECLARE @sql varchar(max)


    SELECT @sql = ' CREATE VIEW ' + 'COOL VIEW NAME' + ' AS ' +
    ' SELECT ' + @COLUMNS +
     ' FROM '+ ' change me to your table name '+
     ' WITH CHECK OPTION;' 

EXEC(@sql);

Answer 2

Here's one option... replace "MyTableName" with the table name you want, or wrap it in a cursor that reads TABLE_NAME from INFORMATION_SCHEMA.VIEWS into @tableName:

DECLARE @tableName sysname;
DECLARE @sql nvarchar(max);
DECLARE @columnList nvarchar(max);
SELECT @tableName = 'MyTableName';
SELECT @columnList = '';
SELECT @columnList += CASE WHEN LEN(@columnList) = 0 THEN '' ELSE ', ' END + COLUMN_NAME 
  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName ORDER BY ORDINAL_POSITION;
SELECT @sql = 'CREATE VIEW [TABLE_NAME] AS 
 SELECT ' + @columnList + '
FROM [' + @tableName + ']
 WITH CHECK OPTION;'

PRINT @sql
EXEC(@sql);