# SQL Replace欄位部分內容以及全資料庫關鍵字搜尋
###### tags: `資料庫`

因為資料庫被injection了一些字串，導致必須要用replace的方式把字串拔掉
紀錄一下replace的語法，

    UPDATE 資料表名稱 SET 欄位名稱 = REPLACE ( 欄位名稱 , 被取代值, 要取代值 ) WHERE 欄位名稱 LIKE '%被取代值%'

這個則是針對TEXT欄位，不能直接replace，要改用CAST轉型

    UPDATE TableName SET DBTextField = REPLACE(CAST(DBTextField AS varchar(MAX))
                                                   ,'SearchText', 'ReplaceText')
    FROM TableName
    WHERE CHARINDEX('SearchText',CAST(DBTextField as varchar(MAX)))>0

下面則是針對特定關鍵字，一次尋找整個資料庫，第一段是逐行內容都會顯示，第二段的是只顯示總計

    DECLARE @SearchStr nvarchar(100)
    --定義變數
    SET @SearchStr = '你要找的詞'
     
    --建立暫存表格, 2個欄位
    IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
     
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
     
    --停止在部份結果集中傳回顯示Transact-SQL 陳述式或預存程序所影響之資料列數的訊息
    SET NOCOUNT ON
     
    --宣告變數: 資料庫名稱、表格名稱、欄位名稱、搜尋字串
    --QUOTENAME: 用於傳回Unicode 字串，且附加了分隔符號，以便使輸入字串成為有效的SQL Server 分隔識別碼。
    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 = 
                    (
                     --從INFORMATION_SCHEMA.TABLES中取出TYPE為BASE TABLE的資料表SCHEMA與NAME
                     --每次取一個名稱, 所以要用MIN搭配 > @TableName, 即可由小而大去取
                     --OBJECTPROPERTY: 傳回目前資料庫中以結構描述為範圍之物件的相關資訊。
                     --OBJECT_ID: 傳回結構描述範圍的物件之資料庫物件識別碼。
                      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
                            --找出欄位名稱
                            --PARSENAME: 傳回物件名稱的指定部分。
                            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')
                                      AND QUOTENAME(COLUMN_NAME) > @ColumnName
                            )
     
                            --如果有找到欄位名稱, 就把資料塞進去剛才建立的暫存資料表 #Result中
                            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

只顯示總計

    CREATE TABLE #result(
      id      INT IDENTITY, -- just for register seek order
      tblName VARCHAR(255),
      colName VARCHAR(255),
      qtRows  INT
    )
    go
     
    DECLARE @toLookFor VARCHAR(255)
    SET @toLookFor = '[input your search criteria here]'
     
    DECLARE cCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT
      '[' + usr.name + '].[' + tbl.name + ']' AS tblName,
      '[' + col.name + ']' AS colName,
      LOWER(typ.name) AS typName
    FROM
      sysobjects tbl
        INNER JOIN(
          syscolumns col
            INNER JOIN systypes typ
            ON typ.xtype = col.xtype
        )
        ON col.id = tbl.id
        --
        LEFT OUTER JOIN sysusers usr
        ON usr.uid = tbl.uid
     
    WHERE tbl.xtype = 'U'
      AND LOWER(typ.name) IN(
            'char', 'nchar',
            'varchar', 'nvarchar',
            'text', 'ntext'
          )
    ORDER BY tbl.name, col.colorder
    --
    DECLARE @tblName VARCHAR(255)
    DECLARE @colName VARCHAR(255)
    DECLARE @typName VARCHAR(255)
    --
    DECLARE @sql  NVARCHAR(4000)
    DECLARE @crlf CHAR(2)
     
    SET @crlf = CHAR(13) + CHAR(10)
     
    OPEN cCursor
    FETCH cCursor
    INTO @tblName, @colName, @typName
     
    WHILE @@fetch_status = 0
    BEGIN
      IF @typName IN('text', 'ntext')
      BEGIN
        SET @sql = ''
        SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
        SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
        SET @sql = @sql + 'FROM ' + @tblName + @crlf
        SET @sql = @sql + 'WHERE PATINDEX(''%'' + @toLookFor + ''%'', ' + @colName + ') > 0' + @crlf
      END
      ELSE
      BEGIN
        SET @sql = ''
        SET @sql = @sql + 'INSERT INTO #result(tblName, colName, qtRows)' + @crlf
        SET @sql = @sql + 'SELECT @tblName, @colName, COUNT(*)' + @crlf
        SET @sql = @sql + 'FROM ' + @tblName + @crlf
        SET @sql = @sql + 'WHERE ' + @colName + ' LIKE ''%'' + @toLookFor + ''%''' + @crlf
      END
     
      EXECUTE sp_executesql
                @sql,
                N'@tblName varchar(255), @colName varchar(255), @toLookFor varchar(255)',
                @tblName, @colName, @toLookFor
     
      FETCH cCursor
      INTO @tblName, @colName, @typName
    END
     
    SELECT *
    FROM #result
    WHERE qtRows > 0
    ORDER BY id
    GO
     
    DROP TABLE #result
    go

## 參考網址
[http://blog.yam.com/juell/article/22958926](http://blog.yam.com/juell/article/22958926)
[http://stackoverflow.com/questions/2641068/sql-server-find-and-replace-in-text-field](http://stackoverflow.com/questions/2641068/sql-server-find-and-replace-in-text-field).
[https://gist.github.com/Vitall/5353279](https://gist.github.com/Vitall/5353279)