How to create multiple views at a time ?

Here is one Scenerio. Number of tables to be converted into views.

I have 800 tables. For each table, I have to create one view.  For example there is a table called EMP. It contains the following columns.

-          EmpId

-          EmpName

-          Dept

We can obtain the view when we execute the following script

CREATE VIEW [dev].[vwEmp]
AS
SELECT  [EmpID]
        ,[EmpName]
        ,[vwEmp]
FROM [Dev].[Emp]

Like that i have to create views for 800 tables.

Here I provide a simple script which can convert all the tables into views. You can modify this script with respect to your requirements and needs. Take care about Server, DB and Schema while u code.

USE [Your_Database]

GO

DECLARE col_csr CURSOR FOR

     Select c.table_name

            , c.table_schema

            , c.column_name

     From information_schema.tables t

     inner join information_schema.columns c on c.table_name = t.table_name

     where t.table_type = 'BASE TABLE'

     order by c.table_name, c.ordinal_position

    

declare @tname nvarchar(128), @sname nvarchar(128), @cname nvarchar(128)

declare @prev_tname nvarchar(128), @prev_sname nvarchar(128)

OPEN col_csr

FETCH NEXT FROM col_csr into @tname, @sname, @cname

WHILE @@FETCH_STATUS = 0

BEGIN

    if @prev_tname <> @tname begin

        print ' FROM [' + db_name() + '].[' + @prev_sname + '].[' + @prev_tname + ']'

        print 'GO'

        print ' '

       

        set @prev_tname = null

    end

   

    if @prev_tname is null begin

        set @prev_sname = @sname

        set @prev_tname = @tname

   

        print 'CREATE VIEW [' + @sname + '].[vw' + @tname + ']'

        print 'AS'

        print 'SELECT [' + @cname + ']'

    end else begin

        print '      ,[' + @cname + ']'

    end   

   

    FETCH NEXT FROM col_csr into @tname, @sname, @cname

END

print ' FROM [' + db_name() + '].[' + @prev_sname + '].[' + @prev_tname + ']'

print 'GO'

CLOSE col_csr

DEALLOCATE col_csr

GO