All stored procedures in the master database disappear? Help!

This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a lot
system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
I would greatly appreciate any help.
Bing
I doubt they are really gone since. SEM uses many of those procedures to do
it's work and I doubt SEM would even work if all the procs were gone. Of
course, I've never tried... <g>
Hopefully it's something this simple. I don't suppose you have closed and
reopened SEM?
BTW... why are you modifying procedures in master in the first place. You
should not be touching system procedures and for the most part you shouldn't
be placing 'user' procedures there.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"bing" <bing@.discussions.microsoft.com> wrote in message
news:212BA431-BCAE-425A-BF02-277B235DE3DA@.microsoft.com...
> This morning when I went to modify a stored procedure in the master
database, I noticed all the stored procedures were gone! When I clicked
'Stored Procedures', nothing returned on the right window in SQL Server
Enterprise Manager. There should be a lot system and user defined stored
procedures there. Extended Stored Procedures show up fine though. Is this
a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||From query analyzer run the following query
select * from master..sysobjects where xtype = 'S'
It will list your system sp's.
You can then be sure if they are deleted or not.
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||Sorry S for system tables P for SP's you're problem with sp's then type
xtype = 'P'
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing
|||It's getting even weirder. I closed and reopened SEM one more time, now I can see all the stored procedures in the master database, but all the views become invisible this time. DBCC checkdb on master shows no error. Would restarting
SQL server and SQL agent help clean up some weirdness?
Bing
"bing" wrote:

> This morning when I went to modify a stored procedure in the master database, I noticed all the stored procedures were gone! When I clicked 'Stored Procedures', nothing returned on the right window in SQL Server Enterprise Manager. There should be a l
ot system and user defined stored procedures there. Extended Stored Procedures show up fine though. Is this a disaster? What might have happened?
> I would greatly appreciate any help.
> Bing