如何修改SQL Server Master数据库dbo中的存储过程?
问题:SSMS中无法找到Master数据库内的存储过程
我使用SQL Server SSMS 18.12.1执行查询脚本,定位到了Master数据库中的存储过程。我清楚普通数据库的存储过程可以在Programmability/Stored Procedures路径下查找,但在SQL Server对象资源管理器里却找不到Master库的对应内容。
编辑:感谢@siggemannen,我发现Databases文件夹下的System Databases处于折叠状态,Master.dbo因此被隐藏了。
以下是我用来按指定搜索模式返回对象列表的脚本:
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
内容的提问来源于stack exchange,提问作者Tony Servies
相关产品推荐
相关产品推荐

