Stored PRocedure List / Compare

[steveot]

Is there any way with osql to compare the stored procedure for a give database againts a fixed list.

In other word I know that a database is supposed to have 100 stored procedures and if one is deleted I would like to query the stored procedures and be able to tell which procedure got deleted.

I know is can use sp_stored_procedure to list the procedure but would either like to dumpt this list to a file so i can compare it or it its possible to write something to compared the dump list against the a fix list and tell me what is missing.

 

[SQLDenis]

select specific_name
from information_schema.routines

will give you a list of all the procs in your DB

You can use this to create your file

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

You can capture the results of sp_stored_procedures in to a temp table, then do anything you'd like with it.

Create 
Table  #Temp(
       Procedure_Qualifier VarChar(50), 
       Procedure_Owner VarChar(50), 
       Procedure_Name VarChar(100), 
       Num_Input_Params Integer, 
       Num_Output_Params Integer, 
       Num_Result_Sets Integer, 
       Remarks VarChar(1000), 
       Procedure_Type Integer)

Insert Into #Temp
Exec sp_stored_procedures 

Select Procedure_Name From #temp

Drop Table #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom