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 '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')

              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 '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')

                    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'

查询结果：