Search code examples
sql-serverstored-proceduresssms

How do I modify a SQL Server Stored Procedure in the Master dbo?

I ran a query to find all stored procedures in SQL Server SSMS version 18.12.1 (see below). It returned a stored procedure from the Master database. I know how to find those that are in the typical database by looking in Programmability/Stored Procures underneath the database. I tried looking through the SQL Server object explorer, but cannot find it.

EDIT: Thanks to @siggemannen, I was able to find out that the System Databases "folder" was collapsed underneath the Databases folder and hiding the Master.dbo.

Here is the procedure that I used to return the list of objects with a given search pattern:

DECLARE @SearchString varchar(120) = 'xxxx'


DECLARE @strSQL varchar(MAX) = '
USE [?];

BEGIN
DECLARE 
    @SearchStr varchar(200) 
    SET @SearchStr = ''%' + @SearchString + '%''; 
SELECT DISTINCT
    ''?'' as db_name, o.name
    , ( CASE upper(o.xtype) 
            WHEN ''C'' THEN ''CHECK constraint''        
            WHEN ''D'' THEN ''Default or DEFAULT constraint''                       
            WHEN ''F'' THEN ''FOREIGN KEY constraint''  
            WHEN ''L'' THEN ''Log''                                                                 
            WHEN ''FN'' THEN ''Scalar function''        
            WHEN ''IF'' THEN ''Inline table-function''
            WHEN ''PK'' THEN ''PRIMARY KEY or UNIQUE constraint''
            WHEN ''P'' THEN ''Stored procedure''                                            
            WHEN ''R'' THEN ''Rule''                    
            WHEN ''RF'' THEN ''Replication filter stored procedure''            
            WHEN ''S'' THEN ''System table''            
            WHEN ''TF'' THEN ''Table function''
            WHEN ''TR'' THEN ''Trigger''                
            WHEN ''U'' THEN ''User table''      
            WHEN ''V'' THEN ''View''                    
            WHEN ''UQ'' THEN ''UNIQUE constraint (type is K)''                  
            WHEN ''X'' THEN ''Extended stored procedure''                           
        ELSE upper(o.xtype) END ) Type
    , ( CASE upper(o.xtype)
            WHEN ''PK'' THEN ( select object_name(parent_object_id) FROM sys.key_constraints (nolock) WHERE o.name=name )
            WHEN ''F'' THEN ( select object_name(parent_object_id) FROM sys.foreign_keys (nolock) WHERE o.name=name )
            WHEN ''TR'' THEN ( select object_name(parent_id) FROM sys.triggers (nolock) WHERE o.name=name )     
        ELSE '''' END ) as Parent_Object
FROM sysobjects o (nolock) 
INNER JOIN syscomments sc (nolock) ON o.id = sc.id
WHERE UPPER( text ) LIKE UPPER( @SearchStr ) AND substring(o.name,1,3)<> ''dt_'' 
GROUP BY o.name, o.xtype
END'


exec sp_msforeachdb @strSQL
GO
Solution

  • You have to click on "System databases" to find the master-db, if you're in SSMS (SQL Server Management Studio) that is. If not, then i'm not sure why you can't find master, it should be always accessible.

    Looks like master db