Home > Database > Mysql Tutorial > 如何快速重新编译所有的存储过程

如何快速重新编译所有的存储过程

WBOY
Release: 2016-06-07 16:06:42
Original
1317 people have browsed it

自己的一个写法,大概思路是从sys.Objects里取得所有的存储过程,然后拼动态字符串来使用sp_recompile重新编译所有的SP。 SELECT ROW_NUMBER() OVER(ORDER BY name) AS RID,exec sp_recompile + name + AS TextINTO #TEMPFROM sys.objects WHERE TYPE = PDEC

自己的一个写法,大概思路是从sys.Objects里取得所有的存储过程,然后拼动态字符串来使用sp_recompile重新编译所有的SP。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SELECT ROW_NUMBER() OVER(ORDER BY name) AS RID,'exec sp_recompile ''' + name +'''' AS Text

INTO #TEMP

FROM sys.objects WHERE TYPE = 'P'

 

DECLARE @MaxID INT

DECLARE @SQL VARCHAR(MAX)

SELECT @MaxID = MAX(RID) FROM #TEMP

 

WHILE (@MaxID IS NOT NULL)

BEGIN

    SELECT @SQL = Text

    FROM #TEMP

    WHERE RID = @MaxID

     

    --PRINT @SQL

    EXEC (@SQL)

     

    SELECT @MaxID = MAX(RID) FROM #TEMP WHERE RID < @MaxID

END

Copy after login
不知道还有没有什么更好的方法。
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template