473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

CREATE VIEW - script to automate column names?

Hi,

I'm trying to create views on all my existing tables and for that I'd
like to create a script or so.
I don't want to specify the '*' for the columns in the create view
statement. I prefer to specify the column names.
I have the column names int sys.columns table but Do not know how to
handle them to have a statement like that:

CREATE VIEW myVIEW
WITH SCHEMABINDING
AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
....?????.....
Anyone can help?

thx,

Chris

Mar 12 '07 #1
1 4503
On 12 Mar 2007 04:44:56 -0700, "clir" <ch******************@gmail.com>
wrote:

Use a cursor to loop over the column names, all the while
concatenating a string variable. In the end, execute that string
(sp_executesql) and your view will be created.

-Tom.
>Hi,

I'm trying to create views on all my existing tables and for that I'd
like to create a script or so.
I don't want to specify the '*' for the columns in the create view
statement. I prefer to specify the column names.
I have the column names int sys.columns table but Do not know how to
handle them to have a statement like that:

CREATE VIEW myVIEW
WITH SCHEMABINDING
AS
SELECT col1name, col2name, col3name, etc...
from sys.columns
....?????.....
Anyone can help?

thx,

Chris
Mar 14 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: js | last post by:
I am trying to create a primary key constraint on a view in the following statement. However, I got an error ORA-00907: missing right parenthesis. If the CONSTRAINT clause is removed, then the...
2
by: Gary DeRousse | last post by:
Hello, Using SQL Server 2000, I'm trying to put together a query that will tell me the following information about a view: The View Name The names of the View's columns The names of the source...
1
by: Not Me | last post by:
Hi, I'm sure this is a common problem.. to create a single field from a whole column, where each row would be separated by a comma. I can do this for a specified table, and column.. and I've...
4
by: Silas | last post by:
Hi, I use view to join difference table together for some function. However, when the "real" table fields changed (e.g. add/delete/change field). The view table still use the "old fields". ...
24
by: flkeyman | last post by:
Work in legal office. Trying to create solid designed database structure. This is list of tables w/fields and primary keys. Any comments/advice greatly appreciated. tbl-Defendants CaseNumber...
10
by: Zack Sessions | last post by:
Has anyone tried to create a SQL7 view using the CREATE VIEW command and ADO.NET? If so, is there a trick in trapping a SQL error when trying to create the view? I have a VB.NET app that, amoung...
2
by: Curtiosity | last post by:
I have done a create or replace view called creditcard1. If I do a "select * from creditcard1" it retrieves the data just fine. If I try to do a statement where I am listing the column names it...
2
by: Shirley | last post by:
We are running DB2 on iSeries V5R2. Using AQUA DATA STUDIO with a connection to our iSeries, I created a view using SQL and I am trying to create an index on this view using the code below. ...
4
by: cognosqueen | last post by:
I need to create a view of a sql table, but change the data types. I know the syntax below is not correct, and can't figure out if it is wrong or if you just can't do this. I have only created...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.