如何以编程方式确定存储过程是否从另一个数据库中进行选择？

发布于 2024-08-21 12:38:09 字数 182 浏览 8 评论 0原文

在 MS SQL Server 2000 安装中，我有许多存储过程，它们从存储数据的数据库以外的数据库中提取数据。所有选择都发生在同一数据库服务器上。例如：

select * from [OtherDatabase]..table

如何在不逐一查看的情况下找到哪些程序执行此类操作？

原文

On an MS SQL Server 2000 installation I have numerous stored procedures that pull data from databases other than the one it's stored in. All selects occur on the same database server. For example:

select * from [OtherDatabase]..table

How can I find which procedures do that sort of thing without eyeballing each one?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

荒人说梦 2024-08-28 12:38:09

以下是一个存储过程，它将搜索任何存储过程中包含的文本。您可以使用它来搜索可能被调用的各种数据库。

CREATE PROCEDURE FindTextInSP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

The following is a stored proc that will search for text contained in any stored proc. You could use this to search for the various databases that might get called.

CREATE PROCEDURE FindTextInSP
@StringToSearch varchar(100) 
AS 
   SET @StringToSearch = '%' +@StringToSearch + '%'
   SELECT Distinct SO.Name
   FROM sysobjects SO (NOLOCK)
   INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
   AND SO.Type = 'P'
   AND SC.Text LIKE @stringtosearch
   ORDER BY SO.Name
GO

回复收藏 0 原文

雅心素梦 2024-08-28 12:38:09

这段代码可以吗...您需要临时连接到其他服务器上的数据库吗？您需要成为“系统管理员”组的成员才能正常工作...补充我的想法...这可能是错误的，请随时指出这一点...

USE MASTER
GO

sp_addserver @server='AnotherSQLServer'
USE [AnotherSQLServer].Master
GO

IF EXISTS (SELECT name FROM [AnotherSQLServer].sysobjects WHERE name = 'some_proc' AND type = 'P') 
THEN
    ' DO WHAT YOU HAVE TO DO
    'DROP PROCEDURE some_proc
END
GO

USE MASTER
GO

sp_dropserver @server='AnotherSQLServer'

Would this code do...You would need to temporarily connect to the database on the other server? You would need to be a member of the 'sysadmin' group for this to work...top off my head....it may be wrong, feel free to point this out...

USE MASTER
GO

sp_addserver @server='AnotherSQLServer'
USE [AnotherSQLServer].Master
GO

IF EXISTS (SELECT name FROM [AnotherSQLServer].sysobjects WHERE name = 'some_proc' AND type = 'P') 
THEN
    ' DO WHAT YOU HAVE TO DO
    'DROP PROCEDURE some_proc
END
GO

USE MASTER
GO

sp_dropserver @server='AnotherSQLServer'

回复收藏 0 原文

~没有更多了~