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.
david_ackermanAsked:
Who is Participating?
 
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:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.