MSSQL 全庫搜尋 指定字元串

平時在在MSSql中查詢資料的時候，想查找，某個字段在資料庫中是否存在，并且查詢出在哪個表中，哪個字段下面，在不知道的情況下，操作起來會很麻煩，然後就寫了一個sql語句，使用起來感覺挺友善的。當然了，也可以做成存儲過程，來使用。

　　　　

--一般查詢

DECLARE @string VARCHAR(max)

SET @string='a'--要查詢的字元串

DECLARE @tbname VARCHAR(50)
DECLARE tbroy CURSOR FOR
  SELECT name
  FROM   sysobjects
  WHERE  xtype = 'u ' --周遊所有的表

OPEN tbroy

FETCH next FROM tbroy INTO @tbname

--建立臨時表
IF Object_id('Tempdb..#temp_table') IS NOT NULL
  DROP TABLE #temp_table

CREATE TABLE #temp_table
  (
     ID      INT IDENTITY(1, 1),
     tbname  VARCHAR(max),--表名
     colname VARCHAR(max)--字段名
  )

WHILE @@fetch_status = 0
  BEGIN
      DECLARE @colname VARCHAR(50)
      DECLARE colroy CURSOR FOR
        SELECT name
        FROM   syscolumns
        WHERE  id = Object_id(@tbname)
               AND xtype IN (SELECT xtype
                             FROM   systypes
                             WHERE  name IN ( 'varchar ', 'nvarchar ', 'char ', 'nchar ' ) --資料類型為字元型的字段
                            ) --第二個遊标是第一個遊标的嵌套遊标，周遊某個表的所有字段

      OPEN colroy

      FETCH next FROM colroy INTO @colname

      WHILE @@fetch_status = 0
        BEGIN
            DECLARE @sql NVARCHAR(1000),
                    @j   INT

            SELECT @sql = 'select @i=count(1) from ' + @tbname
                          + ' where ' + @colname + ' like ' + '''%' + @string
                          + '%'''

            EXEC Sp_executesql
              @sql,
              N'@i int output',
              @i=@j output --輸出滿足條件表的記錄數
            IF @j > 0
              BEGIN
                  INSERT INTO #temp_table
                  SELECT @tbname,
                         @colname
              --exec( 'select distinct '[email protected]+' from ' [email protected] + ' where '+ @colname+ ' like '+ '''%'[email protected]+ '%''')
              END

            FETCH next FROM colroy INTO @colname
        END

      CLOSE colroy

      DEALLOCATE colroy

      FETCH next FROM tbroy INTO @tbname
  END

CLOSE tbroy

DEALLOCATE tbroy

SELECT id, tbname as '表名', colname as '字段名'FROM   #temp_table

IF Object_id('Tempdb..#temp_table') IS NOT NULL--删除臨時表
  DROP TABLE #temp_table
go

      

 查詢結果：

　　　　 

--存儲過程實作
--建立存儲過程
CREATE PROC Search (@string NVARCHAR(max))
AS
  BEGIN
      DECLARE @tbname VARCHAR(50)
      DECLARE tbroy CURSOR FOR
        SELECT name
        FROM   sysobjects
        WHERE  xtype = 'u ' --周遊所有的表
      OPEN tbroy

      FETCH next FROM tbroy INTO @tbname

      --建立臨時表
      IF Object_id('Tempdb..#temp_table') IS NOT NULL
        DROP TABLE #temp_table

      CREATE TABLE #temp_table
        (
           ID      INT IDENTITY(1, 1),
           tbname  VARCHAR(max),--表名
           colname VARCHAR(max)--字段名
        )

      WHILE @@fetch_status = 0
        BEGIN
            DECLARE @colname VARCHAR(50)
            DECLARE colroy CURSOR FOR
              SELECT name
              FROM   syscolumns
              WHERE  id = Object_id(@tbname)
                     AND xtype IN (SELECT xtype
                                   FROM   systypes
                                   WHERE  name IN ( 'varchar ', 'nvarchar ', 'char ', 'nchar ' ) --資料類型為字元型的字段
                                  ) --第二個遊标是第一個遊标的嵌套遊标，周遊某個表的所有字段
            OPEN colroy

            FETCH next FROM colroy INTO @colname

            WHILE @@fetch_status = 0
              BEGIN
                  DECLARE @sql NVARCHAR(1000),
                          @j   INT

                  SELECT @sql = 'select @i=count(1) from ' + @tbname
                                + ' where ' + @colname + ' like ' + '''%' + @string
                                + '%'''

                  EXEC Sp_executesql
                    @sql,
                    N'@i int output',
                    @i=@j output --輸出滿足條件表的記錄數

                  IF @j > 0
                    BEGIN
                        INSERT INTO #temp_table
                        SELECT @tbname,
                               @colname
                    --exec( 'select distinct '[email protected]+' from ' [email protected] + ' where '+ @colname+ ' like '+ '''%'[email protected]+ '%''')
                    END

                  FETCH next FROM colroy INTO @colname
              END

            CLOSE colroy

            DEALLOCATE colroy

            FETCH next FROM tbroy INTO @tbname
        END

      CLOSE tbroy

      DEALLOCATE tbroy

      SELECT id,
             tbname  AS '表名',
             colname AS '字段名'
      FROM   #temp_table

      IF Object_id('Tempdb..#temp_table') IS NOT NULL--删除臨時表
        DROP TABLE #temp_table
  END

go

--使用方法
EXEC Search 'a'      

 查詢結果：

　　　　

轉載于:https://www.cnblogs.com/simoncai/p/4383627.html