?
Solved

How to create multiple views at a time ?

Posted on 2012-04-05
6
Medium Priority
?
319 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.
Suggested Courses

764 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