none
how to check already present object by some query RRS feed

  • Question

  • Msg 2714, Level 16, State 3, Procedure RePayRegINS, Line 119 There is already an object named 'RePayRegINS' in the database.
    for above error, I am checking object is already exsist or not 

    how to check it in DB(I am not DBA but just doing deployment),

    how to check already present object by some query?


    --Himanshu Kulkarni

    • Changed type Naomi NModerator Tuesday, August 28, 2012 3:22 AM Question rather than discussion
    Monday, August 27, 2012 6:55 AM

Answers

  • Msg 2714, Level 16, State 3, Procedure RePayRegINS, Line 119 There is already an object named 'RePayRegINS' in the database.
    for above error, I am checking object is already exsist or not 

    how to check it in DB(I am not DBA but just doing deployment),

    how to check already present object by some query?


    --Himanshu Kulkarni

    You can run this to list out all the tables

    -----------------------------------------
    -- List only the tables in this
    database
    -----------------------------------------
    SELECT    
    o.name
    AS [Table Name],
    o.type,
    c.name AS [Col Name],
    s.name AS [Col
    Type],
    c.prec,
    c.scale,
    c.isnullable
    FROM       

    dbo.sysobjects AS o
    INNER JOIN
    dbo.syscolumns AS c
      ON

       c.id = o.id
    INNER JOIN
    dbo.systypes AS s
      ON
       c.xtype
    = s.xtype
    WHERE  (
       o.type = 'U'
    )

    Read this also

    http://devpinoy.org/blogs/keithrull/archive/2006/06/17/HowTo_3A00_-List-Database-Objects-in-SQL-Server-using-sysobjects_2C00_-syscolumns-and-systypes.aspx


    Many Thanks & Best Regards, Hua Min

    Monday, August 27, 2012 7:58 AM

All replies

  • Would you please check-out below thread

    http://stackoverflow.com/questions/1140203/how-to-check-existence-of-a-sql-server-object-and-drop-it


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    My Blog
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.
    View Ahmed Ibrahim's profile on LinkedIn

    Monday, August 27, 2012 6:58 AM
  • If Exists(Select 1 From <DBNAme>.Sys.Objects Where Name = 'ObjectName>)

    Select 'Exists'

    Else

    Select Not 'Exists'


    Please have look on the comment

    Monday, August 27, 2012 7:06 AM
  • you can use this query

    IF EXISTS(SELECT 1 FROM SYS.Objects where name='RePayRegINS')
    BEGIN
    	--- do your alters needed here
    END
    ELSE
    BEGIN
    	--Create Scripts here
    END

    Regards
    Satheesh

    Monday, August 27, 2012 7:13 AM
  • try this :

    IF OBJECT_ID('TestTable2') IS NULL BEGIN
    	select 0
    END ELSE BEGIN
    	select 1
    END


    Please vote if you find this posting was helpful or Mark it as answered.

    Monday, August 27, 2012 7:14 AM
  • Msg 2714, Level 16, State 3, Procedure RePayRegINS, Line 119 There is already an object named 'RePayRegINS' in the database.
    for above error, I am checking object is already exsist or not 

    how to check it in DB(I am not DBA but just doing deployment),

    how to check already present object by some query?


    --Himanshu Kulkarni

    You can run this to list out all the tables

    -----------------------------------------
    -- List only the tables in this
    database
    -----------------------------------------
    SELECT    
    o.name
    AS [Table Name],
    o.type,
    c.name AS [Col Name],
    s.name AS [Col
    Type],
    c.prec,
    c.scale,
    c.isnullable
    FROM       

    dbo.sysobjects AS o
    INNER JOIN
    dbo.syscolumns AS c
      ON

       c.id = o.id
    INNER JOIN
    dbo.systypes AS s
      ON
       c.xtype
    = s.xtype
    WHERE  (
       o.type = 'U'
    )

    Read this also

    http://devpinoy.org/blogs/keithrull/archive/2006/06/17/HowTo_3A00_-List-Database-Objects-in-SQL-Server-using-sysobjects_2C00_-syscolumns-and-systypes.aspx


    Many Thanks & Best Regards, Hua Min

    Monday, August 27, 2012 7:58 AM