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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

how do I specify the schema in a view?

We are running sql server 2005 and I need to create some views to be used in a third party application.  I am not by any means an expert on this but have enough knowledge to create a view to perform the task needed.  The only problem is that when I create a view, it saves with the schema "dbo" ( the default, I assume).  I need the view to relate to a different schema, but I have not found a straight forward way of defining it at the time of creation.  Can anyone help walk me through this?  This is very important for my report writing in the third party application.
0
david_ackerman
Asked:
david_ackerman
  • 4
  • 2
1 Solution
 
ralmadaCommented:
This will do it
 

create view yourview
as
select * from yourschema.yourtable

Open in new window

0
 
ralmadaCommented:
correction
 

create view yourschema.yourview
as
select * from yourschema.yourtable

Open in new window

0
 
david_ackermanAuthor Commented:
I'm sorry, but I honestly don't even know where I would input this.  I have opened up the view I created that has attached the dbo schema and see the following:

SELECT     ET_Reason, ET_ID, ET_tstamp, ET_lastuser, ET_ST_ID, ET_EV_ID, ET_MT_ID, ET_RF_ID, ET_Date, ET_Duration, ET_DurationCode
FROM         SPIPublic.Extension
WHERE     (ET_Reason = '30 Day Deadline Date')

SPIPublic is the schema I want associated with the view.

I apologize for my ignorance.
0
Independent Software Vendors: 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!

 
ralmadaCommented:
so when you create your view you just do like this:
create view SPIPublic.thenameofyourview
as
SELECT     ET_Reason, ET_ID, ET_tstamp, ET_lastuser, ET_ST_ID, ET_EV_ID, ET_MT_ID, ET_RF_ID, ET_Date, ET_Duration, ET_DurationCode
FROM         SPIPublic.Extension
WHERE     (ET_Reason = '30 Day Deadline Date')

Open in new window

0
 
ralmadaCommented:
If you've already created your view, just drop it
drop view yourview
and recreate it as I mentioned in my previous comment.
0
 
david_ackermanAuthor Commented:
Used your suggestion in a query and worked like a charm!  Thanks!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now