SQL Server – WHERE NAME LIKE ( A SET OF NAMES )

selectsql serversql server 2014sql-server-2008-r2sql-server-2012

I would like to drop all the jobs related to a set of specific databases.

So I have added all the names into a table variable and I was trying to generate the scripts to drop the jobs.

how can I achieve this?

use msdb
go


DECLARE @R TABLE ( NAME VARCHAR(108) NOT NULL PRIMARY KEY CLUSTERED)

INSERT INTO @R(NAME)
SELECT 
'UK15SUMMProduct' UNION ALL SELECT 
'US15SUMMProduct' UNION ALL SELECT 
'DE15SUMMProduct' UNION ALL SELECT 
'AT15SUMMProduct' UNION ALL SELECT 
'FR15SUMMProduct' UNION ALL SELECT 
'EU15SUMMProduct' UNION ALL SELECT 
'AU15SUMMProduct' UNION ALL SELECT 
'UK15SUMSProduct' UNION ALL SELECT 
'US15SUMSProduct' UNION ALL SELECT 
'DE15SUMSProduct' UNION ALL SELECT 
'AT15SUMSProduct' UNION ALL SELECT 
'FR15SUMSProduct' UNION ALL SELECT 
'EU15SUMSProduct' UNION ALL SELECT 
'AU15SUMSProduct'


SELECT S.* 
FROM SYSJOBS S
WHERE NAME LIKE ( SELECT NAME FROM  @R)

something like the select above, I want to see all the jobs that have the database names in them and delete them, for example, like in the script below:

exec sp_delete_job @job_name = 'WebFeed UK15SUMMProduct', @delete_unused_schedule = 0

Best Answer

Here is the whole query:

Declare @job sysname
Declare jobs_cursor Cursor For
    Select S.name 
    From msdb.dbo.SYSJOBS S
    Inner Join (values
        ('UK15SUMMProduct')
        , ('US15SUMMProduct') 
        , ('DE15SUMMProduct') 
        , ('AT15SUMMProduct') 
        , ('FR15SUMMProduct') 
        , ('EU15SUMMProduct') 
        , ('AU15SUMMProduct') 
        , ('UK15SUMSProduct') 
        , ('US15SUMSProduct') 
        , ('DE15SUMSProduct') 
        , ('AT15SUMSProduct') 
        , ('FR15SUMSProduct') 
        , ('EU15SUMSProduct') 
        , ('AU15SUMSProduct')
    ) as n(name) on S.name like '%'+n.name + '%'

Open jobs_cursor
Fetch Next From jobs_cursor into @job

While @@Fetch_Status = 0
Begin
    Print 'Remove: '+@job
    exec sp_delete_job @job_name = @job, @delete_unused_schedule = 0
    Fetch Next From jobs_cursor into @job
End

Close jobs_cursor
Deallocate jobs_cursor

Part of it is similar to previous answers.

You can replace values(...) by a table variable or a real table.

Related Solutions

Sql-server – Generate script to automate renaming of default constraints

Ok, couple of things.

always use EXEC when executing stored procedures; the shorthand without EXEC only works when it is the first statement in the batch (and that will not be the case here).
always use semi-colon terminators - in this case they are useful in lieu of pretty carriage returns and indentation, but they are always wise to have.
always use QUOTENAME() instead of manually applying square brackets yourself. In this case you're probably safe, but there are cases where the manual approach will break.

you can test the PRINT output but it won't necessarily be complete if your total command is > 8k (see this tip for some alternative approaches).

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'EXEC sys.sp_rename N''' 
    + QUOTENAME(s.name) + '.' + QUOTENAME(d.name) 
    + ''', N''DF_' + t.name + '_' + c.name + ''', ''OBJECT'';'
  FROM sys.tables AS t
  INNER JOIN sys.default_constraints AS d
     ON d.parent_object_id = t.object_id
  INNER JOIN sys.columns AS c
     ON c.object_id = t.object_id
    AND c.column_id = d.parent_column_id
  INNER JOIN sys.schemas AS s
     ON t.schema_id = s.schema_id
  WHERE d.NAME LIKE N'DF[_][_]%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Sql-server – Generate script to automate renaming of default constraints

Sql-server – Combine – Select Into, with Insert Into Select

Related Question