We are trying to exclude stored procedures which have name like '%backup%' or %WI%'..
Here is the total query i used..
;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' and ( object_name(object_id) NOT LIKE '%BACKUP%'
or object_name(object_id) not like '%backup%'
OR object_name(object_id) NOT LIKE '%Wi%')
)
SELECT * FROM CTE
sample Output of above query:
report_spBIHRRecruitmentReport_Phase2_backup
But strangely above query is returning results which still have names
like
1.backup
2.Wi
Then i tried to use collate to restrict results like results..
;WITH CTE
AS
(select object_name(object_id) as procname
from sys.sql_modules
where definition LIKE '%CTLEMPLOYEE%' collate SQL_Latin1_General_CP1_CI_AS
and ( object_name(object_id) NOT LIKE '%BACKUP%'
or object_name(object_id) not like '%backup%'
OR object_name(object_id) NOT LIKE '%Wi%' collate SQL_Latin1_General_CP1_CI_AS
))
SELECT * FROM CTE
Output still contains names like below:
report_spBIHRRecruitmentReport_Phase2_backup
Below is my database info:
Version:SQL Server 2012
Collation:SQL_Latin1_General_CP1_CI_AS
comptabilty level :90
ASK:
Can you please help me understand why i am still getting names like '%backup% or '%wi%'
Scripts to Reproduce:
create proc usp_test1_backup
as
begin
end
create proc usp_test2_wi
as
begin
end
drop proc usp_test1_backup
drop proc usp_test2_wi
Execution plan of my query if it helps you:
Please let me know if you need any further info
Best Answer
The answer is quite simple. You're using a load of
OR
s, which should beAND
s:In your case,
object_name(object_id) not like '%backup%'
will still matchOR object_name(object_id) NOT LIKE '%Wi%'
and so will return the row etc etc.Change all of the above conditions to
AND
and you'll be OK.