主页 / dba / 问题 / 24977
Accepted
datagod
Asked: 2012-09-27 10:20:01 +0800 CST

有没有办法检查 SQL 作业以获取对链接服务器的引用？

  • 772

在准备将服务器场迁移到云中时，我试图列举多年来定义的所有链接服务器。我还需要找到（并更正）引用这些链接服务器的代码。

这是我一起扔的东西，以帮助识别引用链接服务器的对象的名称，但这仅适用于存储的过程、视图、函数和触发器：

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 包......我只是想尽快完成尽可能多的工作。

sql-server sql-server-2000

2 个回答

  1. Best Answer

    要查询工作，您只需要查看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
   );
    • 2

  2. 如果您知道自己在搜索什么，Red Gate 有一个非常好的免费搜索工具，称为 SQL Search，它是 SSMS 的插件。与我在查看 syscomments 时所做的任何尝试相比，它更容易且更不容易出错。试一试。在这里下载。

    在工作中搜索引用时，请确保在搜索中包含 msdb。

    如果要直接查询 SQL 代理作业，则可以使用：

    USE msdb
SELECT
    j.name AS JobName,
    js.step_id AS StepID,
    js.step_name AS StepName
FROM sysjobsteps js
INNER JOIN sysjobs j ON js.job_id=j.job_id
WHERE js.command LIKE '%TheLinkedServer%'
ORDER BY j.name, js.step_id
    • 1

相关问题