我有一个将会改变的链接服务器。有些过程像这样调用链接服务器:[10.10.100.50].dbo.SPROCEDURE_EXAMPLE
。我们也有触发器来做这类工作。我们需要找到所有使用[10.10.100.50]
来更改它的地方。
在SQL Server Management Studio Express中,我没有在Visual Studio中找到像“在整个数据库中查找”这样的功能。一个特殊的sys-select可以帮助我找到我需要的东西吗?
发布于 2009-03-23 19:24:53
这是我在我的系统上用来查找文本的过程的一部分。
DECLARE @Search varchar(255)
SET @Search='[10.10.100.50]'
SELECT DISTINCT
o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id=o.object_id
WHERE m.definition Like '%'+@Search+'%'
ORDER BY 2,1
发布于 2010-11-19 13:31:51
你可以像这样找到它
SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'
它将列出不同的存储过程名称,这些名称在存储过程中包含像“User”这样的文本。More info
发布于 2012-08-27 17:25:28
-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = 'employee';
-- Get the schema name, table name, and table type for:
-- Table names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,TABLE_NAME AS 'Object Name'
,TABLE_TYPE AS 'Object Type'
,'Table Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
--Column names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,COLUMN_NAME AS 'Object Name'
,'COLUMN' AS 'Object Type'
,'Column Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
SPECIFIC_SCHEMA AS 'Object Schema'
,ROUTINE_NAME AS 'Object Name'
,ROUTINE_TYPE AS 'Object Type'
,ROUTINE_DEFINITION AS 'TEXT Location'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');
https://stackoverflow.com/questions/674623
复制相似问题