asked on

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.

ralmada

This will do it

create view yourview
as
select * from yourschema.yourtable

Open in new window

ralmada

correction

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

Open in new window

david_ackerman

ASKER

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.

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

If you've already created your view, just drop it
drop view yourview
and recreate it as I mentioned in my previous comment.

david_ackerman

ASKER

Used your suggestion in a query and worked like a charm! Thanks!