script to create views in mass for 100's of tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

script to create views in mass for 100's of tables

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-16 : 14:14:47

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

352 Posts

Posted - 2013-05-16 : 14:51:28

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

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-16 : 15:00:50

thanks djj; can you explain more abou tthe dynamic code to create the views with example please..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 00:24:18

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

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-20 : 09:43:52

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,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 04:56:58

you mean create a view each for each value of SourceID etc?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-21 : 12:08:51

yes Visakh and this needs to be done all the tables ( 100's of them)
thanks for any suggestions.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-21 : 12:22:12

Why?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2013-05-21 : 12:31:56

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-21 : 16:20:52

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 00:43:57

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources