Sql-Server
有沒有辦法檢查 SQL 作業以獲取對連結伺服器的引用？
在準備將伺服器場遷移到雲中時，我試圖列舉多年來定義的所有連結伺服器。我還需要找到（並更正）引用這些連結伺服器的程式碼。
這是我一起扔的東西，以幫助辨識引用連結伺服器的對象的名稱，但這僅適用於儲存的過程、視圖、函式和触發器：
declare @sql varchar(2000) select @SQL = 'use ?; print ''?''; print replicate(''-'',len(''?'')); print '' ''; select object_name(sc.id) from syscomments sc where text like ''%TheLinkedServer%'' ' exec sp_msforeachdb @sql我知道我將不得不打開 DTS/SSIS 包……我只是想盡快完成盡可能多的工作。
要查詢工作，您只需要查看
msdb的工作表：SELECT j.name FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id WHERE s.command LIKE '%TheLinkedServer%';syscomments 也是一種向後兼容性視圖 - 它不是在對像中搜尋引用的最佳方式，特別是因為任何大於 4000 個字元的對像都將儲存在多行中，並且可能會使連結伺服器名稱跨越兩行。更安全的是：
SELECT o.name FROM sys.objects AS o INNER JOIN sys.sql_modules AS m ON o.[object_id] = m.[object_id] WHERE m.definition LIKE '%TheLinkedServer%';最後， sp_msforeachdb 完全不可靠（請參閱此處和此處以獲得更好的替代品）。
這是我去年編寫的一個方便的搜尋程序，可能有用：
CREATE PROCEDURE dbo.FindString @search_string NVARCHAR(4000), @database_list NVARCHAR(MAX) = NULL, @case_sensitive BIT = 0, @include_jobs BIT = 0, @include_columns BIT = 0, @include_parameters BIT = 0, @include_system_objects BIT = 0, @include_system_databases BIT = 0 AS BEGIN SET NOCOUNT ON; DECLARE @init_sql NVARCHAR(MAX), @run_sql NVARCHAR(MAX), @dbname NVARCHAR(128), @all_text NVARCHAR(10), @coll_text NVARCHAR(50); CREATE TABLE #t ( [database] SYSNAME, [schema] SYSNAME, [object] SYSNAME, [type] SYSNAME, [create_date] DATETIME, [modify_date] DATETIME, [definition] NVARCHAR(MAX) ); CREATE TABLE #j ( [job_name] SYSNAME, [step_id] INT, [step_name] SYSNAME, [create_date] DATETIME, [modify_date] DATETIME, [definition] NVARCHAR(MAX) ); CREATE TABLE #cp ( [database] SYSNAME, [schema] SYSNAME, [object] SYSNAME, [type] SYSNAME, [create_date] DATETIME, [modify_date] DATETIME, [param] NVARCHAR(128), [column] NVARCHAR(128) ); SELECT @all_text = CASE @include_system_objects WHEN 1 THEN N'all_' ELSE N'' END, @coll_text = CASE @case_sensitive WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END; SET @init_sql = N'SELECT [database] = ''$db$'', [schema] = QUOTENAME(s.name), [object] = QUOTENAME(o.name), [type] = o.type_desc, o.create_date, o.modify_date, m.[definition] FROM $db$.sys.$all$sql_modules AS m INNER JOIN $db$.sys.$all$objects AS o ON m.[object_id] = o.[object_id] INNER JOIN $db$.sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE m.definition $coll$ LIKE N''%'' + @search_string + ''%'' $coll$;'; SET @init_sql = REPLACE(REPLACE(@init_sql, '$all$', @all_text), '$coll$', @coll_text); SET @search_string = REPLACE(@search_string, '''', ''''''); DECLARE c CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR SELECT QUOTENAME(d.name) FROM sys.databases AS d LEFT OUTER JOIN dbo.SplitStrings_XML(@database_list, N',') AS s ON 1 = 1 WHERE ( LOWER(d.name) = LOWER(s.Item) OR NULLIF(RTRIM(@database_list), N'') IS NULL ) AND d.database_id BETWEEN CASE @include_system_databases WHEN 1 THEN 1 ELSE 5 END AND 32766 ORDER BY d.name; OPEN c; FETCH NEXT FROM c INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname); INSERT #t EXEC sp_executesql @run_sql, N'@search_string NVARCHAR(4000)', @search_string; IF @include_columns = 1 BEGIN SET @run_sql = N'SELECT [database] = ''$db$'', [schema] = QUOTENAME(s.name), [object] = QUOTENAME(o.name), [type] = o.type_desc, o.create_date, o.modify_date, NULL, c.name FROM $db$.sys.$all$columns AS c INNER JOIN $db$.sys.$all$objects AS o ON c.[object_id] = o.[object_id] INNER JOIN $db$.sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE c.name $coll$ LIKE N''%'' + @search_string + ''%'' $coll$;'; SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname); INSERT #cp EXEC sp_executesql @run_sql, N'@search_string NVARCHAR(4000)', @search_string; END IF @include_parameters = 1 BEGIN SET @run_sql = N'SELECT [database] = ''$db$'', [schema] = QUOTENAME(s.name), [object] = QUOTENAME(o.name), [type] = o.type_desc, o.create_date, o.modify_date, p.name, NULL FROM $db$.sys.$all$parameters AS p INNER JOIN $db$.sys.$all$objects AS o ON p.[object_id] = o.[object_id] INNER JOIN $db$.sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE p.name $coll$ LIKE N''%'' + @search_string + ''%'' $coll$;'; SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql, '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname); INSERT #cp EXEC sp_executesql @run_sql, N'@search_string NVARCHAR(4000)', @search_string; END FETCH NEXT FROM c INTO @dbname; END CLOSE c; DEALLOCATE c; SELECT 'Objects:'; SELECT [database], [schema], [object], [type], [definition] = CONVERT(XML, '<?query -- USE ' + [database] + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + [definition] + ' --?>'), first_line = (DATALENGTH(abbrev_def) -DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1, create_date, modify_date FROM ( SELECT *, [count] = (DATALENGTH([definition]) - DATALENGTH(REPLACE([definition], @search_string, ''))) /DATALENGTH(@search_string), abbrev_def = SUBSTRING([definition], 1, CHARINDEX(@search_string, [definition])) FROM #t ) AS x ORDER BY [database], [schema], [object]; IF @include_jobs = 1 BEGIN SELECT 'Jobs:'; SET @run_sql = N'SELECT job_name = j.name, s.step_id, s.step_name, j.date_created, j.date_modified, [definition] = s.command FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS s ON j.job_id = s.job_id WHERE s.command $coll$ LIKE ''%'' + @search_string + ''%'' $coll$ ORDER BY j.name, s.step_id;'; SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text); INSERT #j EXEC sp_executesql @run_sql, N'@search_string NVARCHAR(4000)', @search_string; SELECT job_name, step_id, step_name, [command] = CONVERT(XML, '<?query -- ' + [definition] + ' --?>'), create_date, modify_date FROM #j; END IF @include_columns = 1 OR @include_parameters = 1 BEGIN SELECT 'Columns/parameters'; SELECT [database], [schema], [object], [type], [param], [column], create_date, modify_date FROM #cp ORDER BY [database], [schema], [object], [param], [column]; END DROP TABLE #t, #j, #cp; END GO它依賴於像這樣的拆分功能（或者您自己的，如果您已經擁有一個，或者更好的是，
STRING_SPLIT()如果您使用的是 SQL Server 2016 或更高版本並且可以處理其限制）：CREATE FUNCTION dbo.SplitStrings_XML ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(3) ) RETURNS TABLE AS RETURN ( SELECT Item = CONVERT(NVARCHAR(128), Item) FROM ( SELECT Item = x.i.value('(./text())[1]', 'NVARCHAR(128)') FROM ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y WHERE Item IS NOT NULL );