过滤掉从sys.procedures中更改的当天存储过程

Question

我试图获得一个存储过程列表，这些存储过程是为当前日期创建的，或者是为当前日期修改的。

我有以下工作，并得到了如下的结果：

SELECT  
    SCHEMA_NAME(schema_id),
    name  as StoredProcedureName, 
    create_date, 
    modify_date 
FROM 
    sys.procedures
WHERE  
    create_date >= '2018-09-12 00:00:00'  OR 
    modify_date >= '2018-09-12 00:00:00'  
ORDER BY 
    modify_date DESC

​

​

问题是我试图忽略"SqlQueryNotification“条目。所以我想让它得到结果，忽略上面提到的名字。

我尝试了以下方法

SELECT  
    SCHEMA_NAME(schema_id),
    name  as StoredProcedureName, 
    create_date, 
    modify_date 
FROM 
    sys.procedures
WHERE  
    create_date >= '2018-09-12 00:00:00' OR 
    modify_date >= '2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%'
ORDER BY 
    modify_date DESC

但它还是给了我和照片一样的记录。这不是忽视那些记录，我做错了什么？

Answer 1

您需要将WHERE条件更改为以下内容：

--Want to keep only items that were created on or after 2018-09-12 and do not have a name 
--like 'sqlquerynotification' OR items that were modified on or after 2018-09-12 and do 
--not have a name like 'sqlquerynotification
WHERE  
   (create_date >='2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%') OR
   (modify_date>='2018-09-12 00:00:00' AND 
    name NOT LIKE '%SqlQueryNotificationStoredProcedures%')  

Answer 2

1. 您需要在OR标准周围加上括号
2. 您不需要SqlQueryNotificationStoredProcedure前面的占位符
3. 您不能在SqlQueryNotificationStoredProcedure之后添加复数"s“。

因此，查询可以如下所示：

SELECT  s.[name] AS SchemaName, p.[name] AS StoredProcedureName, p.create_date, p.modify_date 
FROM sys.procedures p
  INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
WHERE  (p.create_date >= '2018-09-12 00:00:00' OR p.modify_date >= '2018-09-12 00:00:00')
  AND p.[name] NOT LIKE 'SqlQueryNotificationStoredProcedure%'
ORDER BY p.modify_date DESC