locked
Cannot create view RRS feed

  • Question

  • Hi,

    I am trying to create following view:

    create view dbo.abc

    as

    select * from otherschema.abc

    There is error:

    Msg 2714, Level 16, State 3, Procedure abc, Line 3

    There is already an object named 'abc' in the database

    While for some other scrits same syntax works. Any hlep.

    Thanks

    Monday, February 11, 2013 10:20 PM

Answers

  • Hello,

    As the error message says, there already exists an object with the same name as you want to use for your new view. You can check it with the following query:

    SELECT *
    FROM sys.objects
    WHERE name = N'abc'
          AND SCHEMA_NAME(schema_id) =N'otherschema' 


    Olaf Helper

    Blog Xing

    • Proposed as answer by Saurabh Sinha DBA Tuesday, February 12, 2013 6:17 AM
    • Marked as answer by DBA_LEARNER Tuesday, February 12, 2013 7:11 PM
    Tuesday, February 12, 2013 3:17 AM
  • Definitely  YES, you can  and below is example for same 

    Create Database test
    Create schema company
    create table company.emp (id int , name varchar(20))
    create view dbo.emp as select * from company.emp
    select * from company.emp
    select * from dbo.emp

    Thanks

    Saurabh Sinha

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 12, 2013 3:22 PM

All replies

  • Are you sure is not already there? did you refresh your View folder?
    Tuesday, February 12, 2013 1:18 AM
  • Run the below query in the context of the database to see if there is already a view exists in this database with the name abc. If yes, then you either need to drop it or create the view with a different name instead of abc.

    select * from sys.views where name like 'abc' 

    Tuesday, February 12, 2013 3:15 AM
  • Hello,

    As the error message says, there already exists an object with the same name as you want to use for your new view. You can check it with the following query:

    SELECT *
    FROM sys.objects
    WHERE name = N'abc'
          AND SCHEMA_NAME(schema_id) =N'otherschema' 


    Olaf Helper

    Blog Xing

    • Proposed as answer by Saurabh Sinha DBA Tuesday, February 12, 2013 6:17 AM
    • Marked as answer by DBA_LEARNER Tuesday, February 12, 2013 7:11 PM
    Tuesday, February 12, 2013 3:17 AM
  • Hi there,

    Run the below code you will succeed.

    IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[abc]'))
    DROP VIEW [dbo].[abc]
    GO
    create view dbo.abc
    as
    select * from otherschema.abc
    GO

    Tuesday, February 12, 2013 6:15 AM
  • IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[abc]')) DROP VIEW [dbo].[abc]

    Kumar,

    Just a note: Object names are unique over all, so the object with the name "otherschema.abc" could also be a table, stored procedure, function etc. If you query via "sys.views" you may miss the object.


    Olaf Helper

    Blog Xing

    Tuesday, February 12, 2013 6:39 AM
  • IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[abc]')) DROP VIEW [dbo].[abc]

    If you query via "sys.views" you may miss the object.


    Exactly - that's the reason why I always use the following code for the check for existing objects:

    IF OBJECT_ID('dbo.myview') IS NOT NULL
        DROP dbo.myview
        GO
    
    CREATE view dbo.myview
    AS
    ...


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Tuesday, February 12, 2013 6:53 AM
  • Yes, otherschema.abc is table name. So if abc is table in otherschema, can i create view with same name abc in dbo schema? objects names are same but one is table and other is view.
    Tuesday, February 12, 2013 2:46 PM
  • Definitely  YES, you can  and below is example for same 

    Create Database test
    Create schema company
    create table company.emp (id int , name varchar(20))
    create view dbo.emp as select * from company.emp
    select * from company.emp
    select * from dbo.emp

    Thanks

    Saurabh Sinha

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, February 12, 2013 3:22 PM
  • Thanks for all.

    There was a synonym created with same name that was creating this issue.

    Tuesday, February 12, 2013 4:22 PM
  • So if abc is table in otherschema, can i create view with same name abc in dbo schema?

    Hello,

    As Saurabh already wrote, yes. Object names must be unique, but the full qualified object name include also the schema (name). So you can create object with the same name, as long as they are in different schemas.


    Olaf Helper

    Blog Xing

    Tuesday, February 12, 2013 6:43 PM