Home 程式語言 Stored Procedure MS SQL server...

MS SQL server Stored Procedure(SP)預存程序，如何將包含關鍵字內容的所有資料表及所有欄位，全部搜尋出來

3050

對於一個在維護系統的人而言，我們常常發生一個情況，程式寫太多資料庫太龐大或者程式邏輯太複雜，甚至還有一種情況，就是維護別人寫好的舊系統，而這個舊系統呢！並沒有太多的說明文件而且呢程式經歷了許多次的修改，讓你在維護上產生了非常大的困難或者真的要解出Bug，要花費的時間和精力呢，花的時間是寫一支新程式很多倍的情況之下；如何節省維護的時間呢？如何快速的解決問題呢？如何快速解出Bug？這個功能相信會有一定的幫助。

首先的第一種情況，系統出現了一個問題，要把這個問題解決，Review程式後，了解到了是因為資料庫某一個欄位的關係，接著如果我們想要了解這個欄位裡面的值到底還有用在哪些欄位，最快速的方法，也許是查詢的系統文件，但如果這支程式如果已經歷史久遠，連你自己也想不起來，這時候呢最快速的方法，也最可靠的方法，有可能就是直接去搜尋資料庫裡面的所有的表跟欄位到底哪裡還存在著一模一樣的值。

底下是已經寫好的預儲程式，可以直接複製使用。

不知道怎麼用嗎？看看底下的教學影片吧！

影音教學

程式碼(預存程序Stored Procedure)

USE [資料庫名稱]
GO
/****** Object:  StoredProcedure [dbo].[SearchAllTables]    Script Date: 2020/12/13 上午 10:21:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SearchAllTables]
(
    @Fuzzy bit,
    @SearchStr nvarchar(100)
)
AS
 
BEGIN
    -- 目的: 搜尋資料庫中，所有資料表及所有欄位，把欄位內容包含關鍵字的全部列出來，關鍵字可以設定為模糊或精準搜尋。
    -- 作者: 郭志賢
    -- 來源: 從網路改寫
    -- 網站: https://jackteacher.cc
    -- 已測試環境: SQL Server 2012, SQL Server 2015, SQL Server 2017 
    -- Copyright © 2020 . All rights reserved.
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
    SET NOCOUNT ON
 
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
	IF @Fuzzy = 0
	SET @SearchStr2 = @SearchStr
    ELSE
	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
    DROP TABLE #Results
END

USE [資料庫名稱]

/****** Object: StoredProcedure [dbo].[SearchAllTables] Script Date: 2020/12/13 上午 10:21:30 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROC [dbo].[SearchAllTables]

(

@Fuzzy bit,

@SearchStr nvarchar(100)

)

BEGIN

-- 目的: 搜尋資料庫中，所有資料表及所有欄位，把欄位內容包含關鍵字的全部列出來，關鍵字可以設定為模糊或精準搜尋。

-- 作者: 郭志賢

-- 來源: 從網路改寫

-- 網站: https://jackteacher.cc

-- 已測試環境: SQL Server 2012, SQL Server 2015, SQL Server 2017

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

IF @Fuzzy = 0

SET @SearchStr2 = @SearchStr

ELSE

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

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

END

來源：https://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated/

影音教學

程式碼(預存程序Stored Procedure)

回覆留言 取消回覆

回覆留言取消回覆