[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

newbie: table versus view?  where is the table

Posted on 2006-06-06
15
Medium Priority
?
2,029 Views
Last Modified: 2007-11-27
I do not understand "views", but I do know that the DB I am working on has a couple hundred tables.  I can see them, query them, update them, restore them, etc..  Every table I have seen in a query is actually a table except one.  The "Employees" table is nowhere to be found, except under "Views" (Enterprise Manager).  But it seems to reference the Employees table, no?  Here's my question: where is the table?  Is there one?

Here is what shows up under Views:

create view  [view Employees] as select Employees.employee_number [Employee Number],Employees.first_name [First Name],Employees.last_name [Last Name],Employees.ss_number [SS Number] from Employees

Thanks for any help.
0
Comment
Question by:ottenm
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16843058
A view is the same as a query.

Also, many applications will use views in the same way that you can use a table, where the connection is made to the view and not the table.

> the DB I am working on
Which database?  SQL Server?

0
 
LVL 12

Expert Comment

by:geotiger
ID: 16843114

create view  [view Employees] as
select Employees.employee_number [Employee Number],
         Employees.first_name [First Name],
         Employees.last_name [Last Name],
         Employees.ss_number [SS Number]
from Employees

Based on the view creation statement, you should have a Employees table. Otherwise, the view will not be able to be queried. Your view name is actually 'view Employees'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16843117
>Here's my question: where is the table?  Is there one?
yes, the table is what is mentionned in the FROM clause, ie Employees.

this view returns some columns of all the rows for that table, giving some new names to the columns (for the output, not changing the actual table column names)

views can be used
* to present data in a different way
* to give more granular permissions on data (hiding rows/columns)


see also stored functions which allows parameters to be passed to the query.



0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:ottenm
ID: 16843180
OK, I am not a complete idiot (as my wife would suggest), all the replies make sense.  

Here's what threw me: there is no "Employees" table under tables in Enterprise Manager!  There is employees_, but no employees (or Employees).  (all the other tables are lower case, and they are all duplicated with xxxxxname, and xxxxxname_

Is there a way to hide a table?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16843290
>Here's what threw me: there is no "Employees" table under tables in Enterprise Manager!
well, 2 things:
* running the view, does it return rows?
* possibly you have another VIEW called Employees ...
0
 

Author Comment

by:ottenm
ID: 16843390
>Which database?  SQL Server?
Yes.

>* running the view, does it return rows?
Yes.

>* possibly you have another VIEW called Employees ...
No luck, but the same patter is duplicated under views (for every pair of tables (xyz and xyz_) there is a pair of views named "view Xyz" and "view Xyz_).  For tables, there is only employees_.  For views, there is "view Employees" and "view Employees_".

Thanks for trying.  This has gnawed at me for months.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16843832
What kind of rights do you have on the database?
0
 
LVL 12

Assisted Solution

by:geotiger
geotiger earned 400 total points
ID: 16843866
You may have a hidden employee table or a compatibility view as described in this link:
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1089808,00.html
0
 

Author Comment

by:ottenm
ID: 16843944
>What kind of rights do you have on the database?

Pretty much unlimited as far as I know.  How to tell?

>You may have a hidden employee table or a compatibility view as described in this link:
>http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1089808,00.html

It's an older version of SQL (not 2005).  Enterprise Manager says 8.0.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16844036
If you have sysadmin or SA (same thing) then you can not have anything hidden from you.

In Query Analyzer, Can you run this query - SELECT * FROM employees

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16844145
what does this query return:

select * from sysobjects where [name] = 'Employees'
0
 

Author Comment

by:ottenm
ID: 16844826
>in Query Analyzer, Can you run this query - SELECT * FROM employees

Yes, and it differs slightly from opening the "view Employees".  There is a row_id column that is returned in this query, that does not show up in the Employees view results.

>what does this query return:
>select * from sysobjects where [name] = 'Employees'

bunch of stuff.  Basically a one row result with name/id/xtype/etc (employees, 1597509020, V, etc).

FWIW: In Query analyzer the situation I've described is replicated under the "User Tables" browser.  All the tables have "dbo." prefixed, but there are still two of most tables except employees, only "dbo.employees_".  And no, "employees" does not show up under the "System Tables" folder.

0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 400 total points
ID: 16845308
>>Basically a one row result with name/id/xtype/etc (employees, 1597509020, V, etc).

The xtype says that employees is an View (V).
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 16846994
as ptjcb noted, the "V" in the sysobjects "clearly" indicates that you have a view with that name.

check out that view, and I guess that it does a select ... from employees_  or the like
0
 

Author Comment

by:ottenm
ID: 16847171
And on the 7th day, the decrepit man finally found the *other* view!

There are *three* views: "view Employees", "view Employees_", and now I see the one I have been missing, a view named "employees".  It's a long list, 98% of which starts with "view ....", so it was way up on top.  But in it, I see the queries to real tables.  Rubber, road, reality.

Thanks for the surf.  This had been gnawing at me.  Sorry I was missing it.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

873 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