Plesk Question posted in
The official documentation for Plesk Obsidian can be found here.

How do you search MS SQL database for a particular string? – Plesk

The Situation

I come from PHP + MySQL background and I’m migrating an ASP website.

I’ve migrated them a few times before, but in this case I’m encountering a couple of issues that disorient me and are preventing me from importing a database.

The Obstacles

  1. I downloaded, through Plesk (current version as of writing of this post), a backup of the MS SQL database. But upon opening it I find strings of numbers, not SQL. Thus I cannot do a find and replace of the website URL.

Ex.

5441 5045 0000 0300 8c00 0e01 0000 0000
0000 0000 0000 0000 0000 0000 0000 0000
0000 0000 0000 0000 0000 0000 0000 0000

  1. When I connect to MS SQL via myLittleAdmin and navigate to Tools > New Query and attempt to use a FIND or SEARCH command, as I would in MySQL, it simply doesn’t work.

I search the MS SQL documention for similar commands but couldn’t locate any. And all of the Questions here on Stack Overflow that come up related to my question have huge queries that have a lot of syntax / code that is irrelevant to the simplicity of my question.

The Question

Can someone help me understand what I’m missing here?

Ultimately I am just seeking the MS SQL code I write to search the entire database for an instance of a particular string of text.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I hired someone to write the script for me.

    Here is the find script (update line 2, "TEXT_TO_SEARCH"):

    USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'TEXT_TO_SEARCH'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

    And here is the find and replace script (update line 13): 

    SET NOCOUNT ON 

DECLARE @stringToFind VARCHAR(100) 
DECLARE @stringToReplace VARCHAR(100) 
DECLARE @schema sysname 
DECLARE @table sysname 
DECLARE @count INT 
DECLARE @sqlCommand VARCHAR(8000) 
DECLARE @where VARCHAR(8000) 
DECLARE @columnName sysname 
DECLARE @object_id INT 

SET @stringToFind = 'TEXT_TO _FIND'
SET @stringToReplace = 'TEXT_TO_REPLACE' 

DECLARE TAB_CURSOR CURSOR  FOR 
SELECT   B.NAME      AS SCHEMANAME, 
         A.NAME      AS TABLENAME, 
         A.OBJECT_ID 
FROM     sys.objects A 
         INNER JOIN sys.schemas B 
           ON A.SCHEMA_ID = B.SCHEMA_ID 
WHERE    TYPE = 'U' 
ORDER BY 1 

OPEN TAB_CURSOR 

FETCH NEXT FROM TAB_CURSOR 
INTO @schema, 
     @table, 
     @object_id 

WHILE @@FETCH_STATUS = 0 
  BEGIN 
    DECLARE COL_CURSOR CURSOR FOR 
    SELECT A.NAME 
    FROM   sys.columns A 
           INNER JOIN sys.types B 
             ON A.SYSTEM_TYPE_ID = B.SYSTEM_TYPE_ID 
    WHERE  OBJECT_ID = @object_id 
           AND IS_COMPUTED = 0 
           AND B.NAME IN ('char','nchar','nvarchar','varchar','text','ntext') 

    OPEN COL_CURSOR 

    FETCH NEXT FROM COL_CURSOR 
    INTO @columnName 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
        SET @sqlCommand = 'UPDATE ' + @schema + '.' + @table + ' SET [' + @columnName 
                           + '] = REPLACE(convert(nvarchar(max),[' + @columnName + ']),''' 
                           + @stringToFind + ''',''' + @stringToReplace + ''')' 

        SET @where = ' WHERE [' + @columnName + '] LIKE ''%' + @stringToFind + '%''' 

        EXEC( @sqlCommand + @where) 

        SET @count = @@ROWCOUNT 

        IF @count > 0 
          BEGIN 
            PRINT @sqlCommand + @where 
            PRINT 'Updated: ' + CONVERT(VARCHAR(10),@count) 
            PRINT '----------------------------------------------------' 
          END 

        FETCH NEXT FROM COL_CURSOR 
        INTO @columnName 
      END 

    CLOSE COL_CURSOR 
    DEALLOCATE COL_CURSOR 

    FETCH NEXT FROM TAB_CURSOR 
    INTO @schema, 
         @table, 
         @object_id 
  END 

CLOSE TAB_CURSOR 
DEALLOCATE TAB_CURSOR

  2. 0

    Please see the following SO post on obtaining a dump: How can I get a SQL dump of a SQL Server 2008 database?

    From there, locate the text in question then update the actual DB accordingly once you’ve identified the requisite tables and columns.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search