How to create multiple views at a time ?

Hi  Gicks,

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

EmpId, EmpName,Dept

The view is

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

Like that i have to create views for 63 tables. Is there any alternative ways to achieve this in a single step instead of creating one by one.

Rave
Ravee123Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
wdosanjosConnect With a Mentor Commented:
Try the following script on SSMS and it should generate the create views for you:
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

Open in new window

0
 
lcohanConnect With a Mentor Database AnalystCommented:
"Is there any alternative ways to achieve this in a single step instead of creating one by one."

There is no way (in any database) as far as I'm aware to create multiple views (or any other code objects) with the same piece of code if thats what you mean.

"in a single step" - you can script ALL your objects in a single file including permissions, defaults, etc. and execute that whole script (or file) "in a single step" but again - you must script each object and separate the scripts by GO commands.
0
 
Ravee123Author Commented:
 There is no way (in any database) as far as I'm aware to create multiple views (or any other code objects) with the same piece of code if thats what you mean.

"in a single step" - you can script ALL your objects in a single file including permissions, defaults, etc. and execute that whole script (or file) "in a single step" but again - you must script each object and separate the scripts by GO commands.
 

Open in new window


We can't write a Stored Proc ?

Logic:  In a cursor, we can store these 63 tables name. Then we can pull one table name and set string. Then CREATE VIEW AS SELECT * FROM <First tablename which we pull it from cursor>. This we can put into loop till it reaches 63rd tablename.

Let me know how to achieve this. Any one can write a script for this pls.

Rave
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
lcohanConnect With a Mentor Database AnalystCommented:
Are all the views just a stright select?

If yes, then you could get the column definitions from sys.syscolumns for each of the tables in sys.sysobjects where type = 'U' and table name in your list then indeed prepare and exec a dynamic SQL to create the views on the top of each table.
0
 
Ravee123Author Commented:
Are all the views just a stright select?

If yes, then you could get the column definitions from sys.syscolumns for each of the tables in sys.sysobjects where type = 'U' and table name in your list then indeed prepare and exec a dynamic SQL to create the views on the top of each table.

Open in new window


YES.

Just I need to create views for all these 63 tables. It is like copy of each table. Thats all. I dont have exposure in T-SQL Scripts. Any one can help me.
0
 
Ravee123Author Commented:
Thanks a lot lcohan; Awesome solution. Thanks a lot.
0
All Courses

From novice to tech pro — start learning today.