Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to create multiple views at a time ?

Posted on 2012-04-05
6
Medium Priority
?
325 Views
Last Modified: 2012-04-05
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
0
Comment
Question by:Ravee123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1332 total points
ID: 37813866
"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
 

Author Comment

by:Ravee123
ID: 37813900
 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
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 1332 total points
ID: 37813990
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ravee123
ID: 37814036
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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 668 total points
ID: 37814047
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
 

Author Comment

by:Ravee123
ID: 37814183
Thanks a lot lcohan; Awesome solution. Thanks a lot.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question