Sql-Server

如何通過重新編譯找到儲存過程?

  • June 9, 2018

在我的數據庫中,我有一些重新編譯的儲存過程

我找到它們的方法是:

SELECT OBJECT_NAME(ID)AS SP_NAME,* 
FROM SYSCOMMENTS
WHERE TEXT LIKE '%WITH RECOMPILE%'

當我看:

select * from sys.procedures

我沒有發現重新編譯的跡象。

有沒有更好或更優雅的方法來重新編譯儲存過程?

對於每個數據庫,您可以執行以下操作:

SELECT          DISTINCT
               DB_NAME() AS DBName, 
               SO.name AS SPName, 
               SM.is_recompiled, 
               ISR.SPECIFIC_SCHEMA
FROM            sys.sql_modules AS SM
LEFT OUTER JOIN master.sys.databases AS sDB
   ON SM.object_id = DB_ID()
LEFT OUTER JOIN sys.sysobjects AS SO
   ON  SM.object_id = SO.id
   AND SO.type = 'P'
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR
   ON  ISR.ROUTINE_NAME = SO.name
   AND ISR.SPECIFIC_CATALOG = DB_NAME()
WHERE           SM.is_recompiled = 1;

引用自:https://dba.stackexchange.com/questions/209127