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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase 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
lcohanDatabase 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
wdosanjosCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ravee123Author Commented:
Thanks a lot lcohan; Awesome solution. Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.