SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 script to create views in mass for 100's of tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/16/2013 :  14:14:47  Show Profile  Reply with Quote
I have a database with 100's of tables and need to create views for all of them based on a primary key column in each table which holds just 4 distinct values based on location but some tables have millions of records.
please suggest/share if any script or code to create views in mass.
thanks

djj55
Constraint Violating Yak Guru

USA
308 Posts

Posted - 05/16/2013 :  14:51:28  Show Profile  Reply with Quote
This will list all the tables
select 
    name as TableName 
from sys.tables 
where type = 'U' AND is_MS_Shipped = 0

Build dynamic code to create the views.

djj
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/16/2013 :  15:00:50  Show Profile  Reply with Quote
thanks djj; can you explain more abou tthe dynamic code to create the views with example please..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/17/2013 :  00:24:18  Show Profile  Reply with Quote
something like

SELECT 'CREATE VIEW vw_' + TABLE_NAME + ' AS ...'
FROM INFORMATION_SCHEMA.TABLES


When you run this you'll get view creation script as output. copy and paste it to new window and execute.

FOr ... part add your actual view code which might be columns of table or whatever. If you can explain what you want inside view we cant provide that part as well!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/20/2013 :  09:43:52  Show Profile  Reply with Quote
thanks Visakh; I have several 100's of tables which has a PK column of LocationID which has 4 distinct values and I need to create 4 views based on the locationID. like below please suggest.

table name Visits has PKs of SourceID and VisitID followed by several columns. I need to create 4 views vLVisits,vNVisits,vPVisits,vWVisits

create view vLVisits
as
select *
from Visits where SourceID = 'LNP'

but i need to do this in mass for 100's of tables.
any suggestions is appreciated.

thanks,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/21/2013 :  04:56:58  Show Profile  Reply with Quote
you mean create a view each for each value of SourceID etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/21/2013 :  12:08:51  Show Profile  Reply with Quote
yes Visakh and this needs to be done all the tables ( 100's of them)
thanks for any suggestions.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/21/2013 :  12:22:12  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Why?

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

sreenu9f
Yak Posting Veteran

72 Posts

Posted - 05/21/2013 :  12:31:56  Show Profile  Reply with Quote
would like to restrict the report writers only to their location instead of having access to all others.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/21/2013 :  16:20:52  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Ah I see -- so to only let people look at certain subsets of the data in the tables.



Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 05/22/2013 :  00:43:57  Show Profile  Reply with Quote
quote:
Originally posted by sreenu9f

would like to restrict the report writers only to their location instead of having access to all others.


you mean at database level or at report level?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000