A list of read to file / write to file keywords for SQL Server

Question

We're migrating a SQL Server and as part of preparing for this we need to know about any file access it will be doing to paths that may not exist any more.

There is a large amount of T-SQL we would need to go through to search for any reads / writes to file paths. I'm thinking I could search all of our T-SQL for keywords that do those operations to find potential problems.

Two example keywords I can think of are

OPENROWSET

QUERYOUT

Is there any others?

Answer 1

You want a list of keywords that might be in code somewhere that hard-codes a defunct path? Well, the list is very long... bulk insert, xp_cmdshell, sys.dm_os_file_exists, sp_OACreate, xp_fileexist, create database, alter database, restore database, backup database, ... enumerating this and somehow being sure you got them all will be difficult. It will certainly be easier to find all of the hard-coded paths (assuming you know the list of paths that have been removed) than to find all the keywords that might be involved in referencing them.

If you have removed a mapped drive called X:\, for example, it is much easier to search all procedures / job steps / etc. for %X:\% than it is to find all the places where you use BULK INSERT and then check each one of those to see if they reference X:\.

In either case, I put together this handy search procedure that searches code for a specific string. You can certainly use it to find all instances of BULK INSERT, and all instances of xp_cmdshell, etc. But again it will be a lot easier to search for the explicit paths / drive letters / network shares, and even use that as an opportunity to change the way your code references paths that can change (e.g. put them in config or in a table, instead of hard-coding them anywhere).

CREATE PROCEDURE dbo.FindStringInModules
   @search_string               nvarchar(4000),
   @database_list               nvarchar(max) = NULL,
   @case_sensitive              bit = 0,
   @search_jobs                 bit = 0,
   @search_job_and_step_names   bit = 0,
   @search_schema_names         bit = 0,
   @search_object_names         bit = 0,
   @search_column_names         bit = 0,
   @search_parameter_names      bit = 0,
   @search_system_objects       bit = 0,
   @search_system_databases     bit = 0,
   @search_everything           bit = 0,    
   @debug                       bit = 0
AS
BEGIN
  SET NOCOUNT ON;
 
  IF @search_everything = 1
  BEGIN
    SELECT
      @search_jobs               = 1,
      @search_job_and_step_names = 1,
      @search_object_names       = 1,
      @search_schema_names       = 1,
      @search_column_names       = 1,
      @search_parameter_names    = 1,
      @search_system_objects     = 1,
      @search_system_databases   = 1;
  END
 
  DECLARE @sql        nvarchar(max),
          @template   nvarchar(max),
          @exec       nvarchar(1024),
          @all_text   nvarchar(128),
          @coll_text  nvarchar(128);
 
  SELECT @sql       = N'',
         @template  = N'',
         @all_text  = CASE @search_system_objects 
                      WHEN 1 THEN N'all_' ELSE N'' END,
         @coll_text = CASE @case_sensitive
                      WHEN 1 THEN N'Latin1_General_100_CS_AS_SC'
                      WHEN 0 THEN N'Latin1_General_100_CI_AS_SC' 
                      END;
 
  CREATE TABLE #o
  (
    [database]   nvarchar(130), 
    [schema]     nvarchar(130), 
    [object]     nvarchar(130), 
    [type]       nvarchar(130), 
    create_date  datetime, 
    modify_date  datetime, 
    column_name  nvarchar(130), 
    param_name   nvarchar(130), 
    definition   xml
  );
 
  SET @search_string = N'%' + @search_string + N'%';
 
  SET @template = N'
SELECT [database]     = DB_NAME(),
       [schema]       = s.name,
       [object]       = o.name, 
       [type]         = o.type_desc, 
       o.create_date, 
       o.modify_date,
       [column_name]  = $col$,
       [param_name]   = $param$,
       definition     = CONVERT(xml, ''<?query -- '' + QUOTENAME(DB_NAME())
                        + CHAR(13) + CHAR(10) + OBJECT_DEFINITION(o.object_id) 
                        + CHAR(13) + CHAR(10) + ''--'' + CHAR(63) + ''>'')
  FROM sys.$all$objects AS o
  INNER JOIN sys.schemas AS s 
    ON o.[schema_id] = s.[schema_id]';
 
 
  SET @sql = @sql + REPLACE(REPLACE(@template, N'$col$', N'NULL'), N'$param$', N'NULL')
      + N'
    ' + N' WHERE OBJECT_DEFINITION(o.[object_id]) COLLATE $coll$ 
    ' + N'                                LIKE @s COLLATE $coll$';
 
  SET @sql = @sql + CASE @search_schema_names WHEN 1 THEN N'
      OR s.name COLLATE $coll$ 
        LIKE @s COLLATE $coll$' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_object_names WHEN 1 THEN N'
      OR o.name COLLATE $coll$ 
        LIKE @s COLLATE $coll$' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_column_names WHEN 1 THEN N';
'     + REPLACE(REPLACE(@template, N'$col$', N'c.name'),N'$param$',N'NULL')
      +  N'
         INNER JOIN sys.$all$columns AS c ON o.[object_id] = c.[object_id]
         AND c.name COLLATE $coll$ 
            LIKE @s COLLATE $coll$;' ELSE N'' END;
 
  SET @sql = @sql + CASE @search_parameter_names WHEN 1 THEN N';
'     + REPLACE(REPLACE(@template, N'$col$', N'NULL'),N'$param$',N'p.name')
      +  N'
         INNER JOIN sys.$all$parameters AS p ON o.[object_id] = p.[object_id]
         AND p.name COLLATE $coll$ 
            LIKE @s COLLATE $coll$;' ELSE N'' END;
 
  SET @sql = REPLACE(REPLACE(@sql, N'$coll$', @coll_text), N'$all$', @all_text);
 
  DECLARE @db sysname, @c cursor;
 
  SET @c = cursor FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT QUOTENAME(name) FROM sys.databases AS d
      LEFT OUTER JOIN dbo.fn_split(@database_list, N',') AS s ON 1 = 1
      WHERE
      (
        LOWER(d.name) = LOWER(LTRIM(RTRIM(s.value)))
        OR NULLIF(RTRIM(@database_list), N'') IS NULL
      )
      AND d.database_id >= CASE @search_system_databases
          WHEN 1 THEN 1 ELSE 5 END
      AND d.database_id < 32767
      AND d.state = 0;
 
  OPEN @c;
 
  FETCH NEXT FROM @c INTO @db;
 
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @exec = @db + N'.sys.sp_executesql';
 
    IF @debug = 1
    BEGIN
      RAISERROR(N'Running dynamic SQL on %s:', 1, 0, @db);
      PRINT @sql;
    END
    ELSE
    BEGIN
      INSERT #o
      (
        [database], 
        [schema], 
        [object], 
        [type], 
        create_date, 
        modify_date, 
        column_name, 
        param_name, 
        definition
      )
      EXEC @exec @sql, N'@s nvarchar(4000)', @s = @search_string;
    END
 
    FETCH NEXT FROM @c INTO @db;
  END
 
  IF @debug = 0
  BEGIN
        SELECT [database], 
             [schema], 
             [object], 
             [type], 
             create_date, 
             modify_date, 
             column_name, 
             param_name, 
             definition
      FROM #o
      ORDER BY [database], [schema], [object], [column_name], [param_name];
  END
 
  /* jobs */
 
  IF @search_jobs = 1
  BEGIN
    SET @template = N'SELECT
                job_name = j.name,
                s.step_id,
                s.step_name,
                j.date_created,
                j.date_modified,
                [command_with_use] = CONVERT(xml, N''<?query -- '' 
                                     + QUOTENAME(s.database_name)
                                     + CHAR(13) + CHAR(10) + s.[command]
                                     + CHAR(13) + CHAR(10) + ''--'' + CHAR(63) + ''>'')
            FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS s
            ON j.job_id = s.job_id
            WHERE s.command COLLATE $coll$
                    LIKE @s COLLATE $coll$'
    + CASE @search_job_and_step_names WHEN 1 THEN
      N' OR j.name      COLLATE $coll$ 
                LIKE @s COLLATE $coll$
         OR s.step_name COLLATE $coll$ 
                LIKE @s COLLATE $coll$'
      ELSE N'' END
    + N' ORDER BY j.name, s.step_id;';
 
    SET @sql = REPLACE(@template, N'$coll$', @coll_text);
 
    IF @debug = 1
    BEGIN
      PRINT N'Running this for jobs:';
      PRINT @sql;
    END
    ELSE
    BEGIN
      EXEC sys.sp_executesql @sql, N'@s nvarchar(4000)', @s = @search_string;
    END
  END
END
GO