Solved

How to create multiple views at a time ?

Posted on 2012-04-05
6
294 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
  • 3
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 333 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 39

Assisted Solution

by:lcohan
lcohan earned 333 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 167 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now