Drop Stored Procedures

Drop Stored Procedures

小皮球看著自己的作業清單嘆氣許久,就開始找諸位大師求教關於Stored Procedure的事情。

事情的起因可以追朔到好幾個月以前的某一次 簡訊系統 的討論會議,當時因為有一資料庫中存在多到讓人眼花撩亂的 Stored Procedures,Boss 大人知道後就丟給小皮球一項作業:「想辦法砍掉所有的 Stored Procedure!除非系統的Stored Procedure,否則全部砍掉!」

小皮球愣了一秒,呆呆地發出一個音節:「喔──」

作者伸出手拍在小皮球的頭上,「小皮球太呆了喔~ 你知道剛剛發生什麼事情嗎?」

小皮球:「……蛤?」

作者:「恭喜你多了一項作業,我會幫你在你的債務清單──喔,不對,是作業清單上補上這一筆的。」

小皮球:「……咦?!」

於是,就有了今天的「還債之旅」──

 

怎麼砍掉一個 Stored Procedure 呢?在知道名字的情況下是很簡單的一行指令:「drop Procedure Stored_Procedure_Name」。

那要怎麼看資料庫中有哪些 Stored Procedure 呢?不知道的人,可以先用小皮球到處求教整理出來的指令下去看:「select * from sys.all_objects where type = ‘P’ and is_ms_shipped = 0」。

下指令後的成果

 

先來解釋一下為什麼小皮球要用這樣的指令吧。

「select * from sys.all_objects」是用來檢視目前該資料庫中所有的物件資料,包含系統預設的、後天人工加入的,不管是Table、View、Stored Procedure、Primary Key、Foreign Key……等等通通都包含在內。

可是要如何篩選出非系統預設的 Stored Procedure?要限定為 Stored Procedure 只要下 where 條件讓 type 等於 P ,就表示限定撈出 Stored Procedure,但最大的問題在於「如何限定非系統預設」這一件事情。

當時小皮球有在網路上找到一種說法:「如果要找系統預設的 Stored Procedure,那直接篩選名稱開頭為 sp_ 就可以了」,但後來小皮球下了 Sql 指令試試看,發現有4個 Stored Procedure 名稱是 xp_ 開頭、但它也是系統預設的 Stored Procedure。

 

下指令後的成果

 

於是,小皮球開始去看各個欄位包含了哪些意義,整理如下表:

Column意義
Name該物件名稱。

例如:若該物件資料為 Stored Procedure,則該欄位就是 Stored Procedure Name;若該物件資料為 Table,則該欄位就是 Table Name。

object_id該物件的識別號碼,在該資料庫中為唯一值。
principal_id如果該物件的擁有者不是 Schema 擁有者,則此為擁有者的識別號碼。

※一般而言,預設 Schema 包含的物件就是 Schema 所擁有;除非利用 Alter Authorization 指令來變更擁有權、指定另一個擁有者,否則該欄位均會是 null。

schema_id該物件所屬的 Schema 架構編號,可利用 Sql Server 的function 「SCHEMA_NAME(schema_id)」進一步查詢該 Schema 架構的名稱。
parent_object_id該物件之所屬物件的識別編號。

例如:有一 Table 為 Users,其 object_id 為52195236;則該 Users 內的 Primary Key 之 parent_object_id 即為 52195236。

Type該物件的型別。

例如:Stored Procedure的型別即為「P」。

type_desc該物件的型別描述。

例如:Stored Procedure 的型別描述為「SQL_STORED_PROCEDURE」。

create_date該物件的建立日期。
modify_date該物件最後一次的修改日期。
is_ms_shipped該物件是否為 Sql Server 組件所創建。
is_published該物件是否為已發行的物件。
is_schema_published僅該物件的結構為已發行。

因此,各個欄位看來看去,小皮球覺得用 is_ms_shipped = 0 來確認非系統預設的這一件事情是最安全的。

 

可以找到所有非系統預設的 Stored Procedure 後,就可以來準備砍 Stored Procedure 了。

這裡小皮球利用曾經看過的一種寫法──「Cursor」,針對每一個 Stored Procedure 名稱進行組合 Sql 指令:「drop Procedure Stored_Procedure_Name」後,再執行該 Sql 指令進行刪除 Stored Procedure。

整體 Sql 指令如下圖所示:

整體 Sql 指令

 

執行過後,就可以看到該資料庫中除了系統預設的 Stored Procedure 以外,其他的 Stored Procedure 都消失了。

就可以看到該資料庫中除了系統預設的 Stored Procedure 以外,其他的 Stored Procedure 都消失了

小皮球把作業整理好後,還非常慎重的寫了一張小紙條。

作者將作業與紙條收走、準備幫忙轉交前,看了一下紙條:「作業求放過QAQ」。

作者將紙條隨手一丟、丟到了垃圾桶裡,笑笑地說:「文件裡面怎麼可以包含表情符號呢!呵呵~」

小皮球:「咦咦咦咦咦?!────」

《完》

 

 

相關參考:

  1. http://www.databaseskill.com
  2. http://msdn.microsoft.com/zh-tw/library/ms178618.aspx
  3. http://msdn.microsoft.com/zh-tw/library/ms187961.aspx
  4. http://www.dotblogs.com.tw/hunterpo/archive/2009/08/25/10241.aspx
  5. http://paladinprogram.blogspot.tw/2010/08/sql-server-store-procedure.html
  6. http://dog0416.blogspot.tw/2014/03/dbsql-server-stored-procedure.html

 


發表迴響

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料