Sql-Server
如何通過重新編譯找到儲存過程?
在我的數據庫中,我有一些重新編譯的儲存過程
我找到它們的方法是:
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;