Creating view in SQL Server 2008 using SSMS

Question

I have create view using SSMS but I don't find option to change schema. How do I change schema in this case?

@Matten Can't we choose that while creating it and I don't find it in design too — kinkajou
– kinkajou, Commented Jan 21, 2012 at 11:54
@Kitex... maybe just not enough coffee, but I don't understand your question :-) In SSMS, you can create a view graphically by modelling the required tables, columns and linking them to each other (instead of writing the necessary sql code). So what is you problem? — Matten
– Matten, Commented Jan 21, 2012 at 11:57
@Kitex - Simplest soulution is not to bother with the view designer. It doesn't support constructs like row_number, and mangles your code formatting. With intellisense in the script environment now can think of no good reason for wanting to use it. — Martin Smith
– Martin Smith, Commented Jan 21, 2012 at 12:51
Totally agree with @Martin. There are several other bugs with the view designer. "Easy" is not the same as "better" - especially when it changes the intended logic of your query. Even worse if you don't catch it. — Aaron Bertrand
– Aaron Bertrand, Commented Jan 21, 2012 at 17:01

Martin Smith · Accepted Answer · 2012-01-30 19:38:06Z

5

You can change the view to a new schema via TSQL ALTER SCHEMA ... TRANSFER .... Full example below

CREATE VIEW dbo.Foo
AS
SELECT 1 AS X

GO

CREATE SCHEMA bar

GO
ALTER SCHEMA bar TRANSFER dbo.Foo;

SELECT *
FROM bar.Foo

In general just create it in the correct schema with CREATE VIEW bar.xyz.

As far as the SSMS View designer goes the dialogue that comes up on Ctrl + S just accepts a name and has nowhere to input a schema but before saving the view you can bring up the properties window (with F4) and define the schema there.

enter image description here

edited Jan 30, 2012 at 19:38

answered Jan 21, 2012 at 12:39

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating view in SQL Server 2008 using SSMS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related