SQL Server Performance

Generate Scripts That Check for Object Existence

Discussion in 'SQL Server 2005 General DBA Questions' started by hominamad, Apr 28, 2008.

  1. hominamad Member

    In SQL Server 2000, in the generate scripts wizard, you used to be able to generate scripts that did a DROP and CREATE after checking for existence. e.g.
    IF EXISTS "My Proc"
    DROP "My Proc"
    CREATE "My Proc"
    I can't seem to find a way to do this in SQL 2005. The option that does the DROP stmt doesn't check for existence first. Does anyone know of a way to accomplish this?
  2. FrankKalis Moderator

    If you right-click on any database node in the Object Explorer in SSMS -> Tasks -> Generate Script, you will see one page in that wizard where you can set your scripting options. One option is "Include IF NOT EXISTS". Or simply goto the Tools -> Options -> Scripting to set this.
  3. hominamad Member

    Thanks. I saw that but it doesn't seem to be the right option. When that is checked, and the object exists, it just skips it entirely. I want it to do a drop and create always but check for existence for the drop. I'm pretty sure this is how 2000 behaved. Another annoying thing about the IF EXISTS object is that it puts all the text of thecode in a string and uses sp_executesql to deploy it. I'm scripting all of the objects out so that I can add them to a Visual Studio database project and then put into source control. thats why its important the syntax be correct. Developers will be editing the procs/scripts through visual stuido rather than managament studio going forward.
  4. jagblue New Member

    Try this Procedure
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[proc_genscript]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[proc_genscript]
    /****** Object: Stored Procedure dbo.proc_genscript Script Date: 5/8/2003 11:06:53 AM ******/
    CREATE PROCEDURE proc_genscript
    @ServerName varchar(30),
    @DBName varchar(30),
    @ObjectName varchar(50),
    @ObjectType varchar(10),
    @TableName varchar(50),
    @ScriptFile varchar(255)
    DECLARE @CmdStr varchar(255)
    DECLARE @object int
    DECLARE @hr int
    SET @CmdStr = 'Connect('+@ServerName+')'
    EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
    --Comment out for standard login
    EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
    /* Uncomment for Standard Login
    EXEC @hr = sp_OASetProperty @object, 'Login', 'sa'
    EXEC @hr = sp_OASetProperty @object, 'password', 'sapassword'
    EXEC @hr = sp_OAMethod @object,@CmdStr
    SET @CmdStr =
    CASE @ObjectType
    WHEN 'Database' THEN 'Databases("'
    WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'
    WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'
    WHEN 'Table' THEN 'Databases("' + @DBName + '").Tables("'
    WHEN 'Index' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Indexes("'
    WHEN 'Trigger' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Triggers("'
    WHEN 'Key' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Keys("'
    WHEN 'Check' THEN 'Databases("' + @DBName + '").Tables("' + @TableName + '").Checks("'
    WHEN 'Job' THEN 'Jobserver.Jobs("'
    SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
    EXEC @hr = sp_OAMethod @object, @CmdStr
    EXEC @hr = sp_OADestroy @object
    uasge exec proc_genscript
    @ServerName = 'Server',
    @DBName = 'database',
    @ObjectName = 'ObjName',
    @ObjectType = 'Procedure',
    @TableName = ' ',-- Only For Index, Check,Triggers And Key
    @ScriptFile = 'c: est.sql'
    This will create object script the way you want
    before running this script you need to turn on 'Ole Automation' feature in surface Area Configuration
  5. MohammedU New Member

    By default OLE Automation option is disabled in SQL 2005 you have to enable it before running through SAC.
  6. hominamad Member

    Does this mean that there's no inherent way in SQL Server to do this?
  7. satya Moderator

  8. hominamad Member

    Am I mistaken or was there a way in SQL Server 2000 to do this via Generate Scripts wizard? Does anyone know if it is back in SQL 2008?
  9. satya Moderator

    I;m not sure whether you can achieve this via Generate script wizard, and it is possible with DMO which is changed to SMO from SQl 2005 onwards.
  10. jaiprakashv New Member

    How we can check Trigger exist or not in sql server 2005
  11. FrankKalis Moderator

    Basically just like you would check for most of the other database objects.
    IF OBJECT_ID ('triggername goes here') IS NOT NULL
    ...do stuff
  12. Adriaan New Member

    And if you want to know if a given table has any triggers, you can use this:

    CASE WHEN OBJECTPROPERTY(OBJECT_ID('mytable'), 'HasAfterTrigger') = 1 OR
    OBJECTPROPERTY(OBJECT_ID('mytable'), 'HasInsteadOfTrigger') = 1
    THEN 'Table has a trigger!' ELSE 'No trigger on table!' END
  13. Madhivanan Moderator

Share This Page