给大家共享好东西喽! 查找数据库中指定字符串，支持2005以上-优快云博客

本文介绍了一个用于在SQL Server数据库中搜索指定字符串的存储过程。该过程可以根据输入的字符串、表名和类型参数来查找匹配的数据，并能返回详细的定位信息。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
--给大家共享好东西喽! 查找数据库中指定字符串
USE master 
GO
IF object_id('[dbo].[sp_get_char]','P') IS NOT NULL
DROP PROC [dbo].[sp_get_char]
go 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE       PROCEDURE [dbo].[sp_get_char]
    (
      @char VARCHAR(1024) ,
      @table_name NVARCHAR(255) = '' ,
      @type INT = 1     
    )
AS /*    作者：陈恩辉-弘恩
调用示例说明，参数为默认为1，返回时生成具体列定位；参数为2时，可快速返回定位到表
 
全库查找，返回最详细信息；
exec [dbo].[sp_get_char] '要寻找的字符' 
 
指定对象范围后，进行查找 
exec [dbo].[sp_get_char] '282','%USER%'
 
指定对象范围后，进行查找 ，进行最快返回
exec [dbo].[sp_get_char] '刘亚莎15','%USER_a%404',1 
*/    
     
    IF ( @char IS NULL
         OR RTRIM(@char) = ''
       ) 
        BEGIN    
            SELECT  '请正确输入您要寻找的字符' AS message_return     
            RETURN    
        END
    IF ( @type NOT IN ( 1, 2 ) ) 
        BEGIN    
            SELECT  '输入参数错误，参数只能是1或2' AS message_return     
            RETURN    
        END      
    IF @table_name <> ''
    BEGIN      
        IF NOT EXISTS ( SELECT  1
                        FROM    sys.objects o
                        WHERE   type = 'U'
                                AND name LIKE ISNULL(@table_name, '') + '%' ) 
            BEGIN
                SELECT  '您输入的对象名不存在' AS message_return 
                RETURN
            END
    END
    DECLARE --@char NVARCHAR(255) ,    
        @table_count INT ,
        @i INT ,
        @schema NVARCHAR(255) ,
        @table NVARCHAR(255) ,
        @col NVARCHAR(255) ,
        @sql_exec_table NVARCHAR(MAX) ,
        @sql_all_col NVARCHAR(MAX) ,
        @sql_exec1 NVARCHAR(MAX)      
    SET @i = 1 ;    
    SELECT  @table_count = COUNT(*)
    FROM    sys.objects
    WHERE   type = 'U'
            AND name LIKE @table_name + '%' ;    
     
     
    IF OBJECT_ID('tempdb..#getchar_tmp1') IS NOT NULL
        DROP TABLE #getchar_tmp1  
    CREATE TABLE #getchar_tmp1
        (schemaname NVARCHAR(255) ,
          tablename NVARCHAR(255) )    
     
   --select * from sys.schemas 
    DECLARE c_table CURSOR FAST_FORWARD 
    FOR
        SELECT  SCHEMA_NAME(s.schema_id) AS schemaname ,
                o.name AS tablename
        FROM    sys.objects AS o
        INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
        WHERE    o.type = 'U'
                  AND o.name LIKE @table_name + '%'
        ORDER BY schemaname ,
                tablename      
             
    OPEN c_table    
    FETCH NEXT FROM c_table INTO @schema, @table    

    WHILE @@FETCH_STATUS = 0 
        BEGIN 
            IF 'a' = 'A' ---- 不区分大小写时
                BEGIN  
                    SET @sql_exec_table = 'IF EXISTS ( SELECT * FROM '
                        + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
                        + ' WITH (NOLOCK) WHERE 1 = 2 '   
                    SET @sql_all_col = (SELECT   ' OR ' + QUOTENAME(c.name) + ' LIKE ''' + @char + '%'''
										FROM    sys.columns c,sys.objects o 
										WHERE o.object_id = c.object_id 
												AND o.name =  @table AND o.type = 'U'
												AND ( (TYPE_NAME(c.system_type_id) LIKE '%char%' AND ( LEN(RTRIM(@char)) <= c.max_length OR c.max_length = -1))
													  OR TYPE_NAME(c.system_type_id) LIKE '%text%' )
										FOR XML PATH ('') )               
                END        
            IF 'a' <> 'A' ---- 区分大小写时
                BEGIN  
                    SET @sql_exec_table = 'IF EXISTS ( SELECT * FROM '
                        + QUOTENAME(@schema) + '.' + QUOTENAME(@table)
                        + ' WITH (NOLOCK) WHERE 1 = 2 '   
                    SET @sql_all_col = (SELECT   ' OR ' + QUOTENAME(c.name) + ' LIKE ''' + @char + '%'''
										FROM    sys.columns c,sys.objects o 
										WHERE o.object_id = c.object_id 
												AND o.name =  @table AND o.type = 'U'
												AND ( (TYPE_NAME(c.system_type_id) LIKE '%char%' AND ( LEN(RTRIM(@char)) <= c.max_length OR c.max_length = -1))
													  OR TYPE_NAME(c.system_type_id) LIKE '%text%' )
										FOR XML PATH ('') )               
                END                           
            SET @sql_all_col = @sql_all_col + ' )' + CHAR(13) + CHAR(10)
                + ' BEGIN ' + CHAR(13) + CHAR(10)
                + ' INSERT INTO #getchar_tmp1 ( schemaname , tablename )'
                + CHAR(13) + CHAR(10) + ' SELECT ''' + @schema + ''','''
                + @table + '''' + CHAR(13) + CHAR(10) + ' END '   
            SET @sql_exec1 = @sql_exec_table + @sql_all_col    
            PRINT CAST (@i AS NVARCHAR) + ' / '
                + CAST (@table_count AS NVARCHAR) + SPACE(10)
                + QUOTENAME(@schema) + '.' + QUOTENAME(@table)      
            SET @i = @i + 1
    
      
--如果有必要的话，可以将正在执行的内容打印出来    
--PRINT CAST (@i AS NVARCHAR) + ' / '+ CAST (@table_count AS NVARCHAR) + SPACE(10)+ QUOTENAME(@schema) + '.' + QUOTENAME(@table)      
--SET @i = @i + 1    
--PRINT ( @sql_exec1 )    
            BEGIN TRY 
                PRINT @sql_exec1
                EXEC sp_executesql @sql_exec1     
            END TRY 
            BEGIN CATCH 
                IF @@error <> 0 
                    PRINT ERROR_MESSAGE() + CHAR(13) + CHAR(10) + CHAR(13)
                        + @sql_exec1
            END CATCH 
            FETCH NEXT FROM c_table INTO @schema, @table    
        END    
    CLOSE c_table     
    DEALLOCATE c_table   
      SELECT  * FROM    #getchar_tmp1 
              
     
    IF NOT EXISTS ( SELECT  *
                    FROM    #getchar_tmp1 ) 
        BEGIN    
            SELECT  '没有发现你想要寻找的字符 ... ' AS msg     
            RETURN    
        END    
        
    IF EXISTS ( SELECT  *
                FROM    #getchar_tmp1 ) 
        BEGIN    
                    /* @type = 2 时，直接返回表*/  
            IF @type = 2 
                BEGIN    
                    SELECT  * ,
                            'SELECT * FROM ' + QUOTENAME(schemaname) + '.'
                            + QUOTENAME(tablename) AS sql_select
                    FROM    #getchar_tmp1     
                    DROP TABLE #getchar_tmp1    
                    RETURN    
                END
                    /* @type = 1 时，可以定位到具体是表中哪个字段，并且拼成*/        
            IF @type = 1 
                BEGIN 
                      /* 创建临时表 #getchar_tmp2 */   
                    CREATE TABLE #getchar_tmp2
                        (
                          [schema] NVARCHAR(255) ,
                          [table] NVARCHAR(255) ,
                          col NVARCHAR(255)
                        )     
     
                    DECLARE @s NVARCHAR(255) ,--架构名
                        @t NVARCHAR(255) ,--表名
                        @c NVARCHAR(255) ,--字段
                        @sql_exec_col NVARCHAR(MAX) 
                               /**声明游标列  c_col */   
                    DECLARE c_col CURSOR FAST_FORWARD
                    FOR
                        /** 搜索条件，只对字符列，或text列进行过滤 **/
                                SELECT  t.schemaname ,
                                        t.tablename ,
                                        c.name
                                FROM    #getchar_tmp1 t
                                INNER JOIN sys.objects o ON o.name = t.tablename
                                INNER JOIN sys.columns c ON ( o.object_id = c.object_id )
                                WHERE   ( ( TYPE_NAME(c.system_type_id) LIKE '%char%'
                                            AND ( DATALENGTH(RTRIM(@char)) <= c.max_length
                                                  OR c.max_length = -1))
                                          OR TYPE_NAME(c.system_type_id) LIKE '%text%')
                                        
                                         
                                                                 
     
     
                    OPEN c_col      
                    FETCH NEXT FROM c_col INTO @s, @t, @c     -- 架构  表名  列名
                    WHILE @@FETCH_STATUS = 0 
                        BEGIN 
                                /**拼接语句，如果某一列中包括这个字符，则写入临时表#getchar_tmp2 */   
                            SET @sql_exec_col = 'IF EXISTS (SELECT * FROM [' + @s + '].[' + @t + '] WITH (NOLOCK) WHERE '
                                + QUOTENAME(@c) + ' LIKE ''' + @char + '%'')' + CHAR(13) + CHAR(10)
                                + 'INSERT INTO #getchar_tmp2 ([schema],[table],[col]) '
                                + CHAR(13) + CHAR(10) + 'SELECT ''' + @s + ''',' + '''' + @t + ''',' + '''' + @c + ''''  
                                    /** 执行 @sql_exec_col */     
                                    --PRINT ( @sql_exec_col )    
                            EXEC sp_executesql  @sql_exec_col    
                          
                            FETCH NEXT FROM c_col INTO @s, @t, @c     -- 架构  表名  列名
     
                        END    
                    CLOSE c_col     
                    DEALLOCATE c_col ;     
 
                            /**生成返回数据**/
                    SELECT  [schema] ,
                            [table] AS table_name ,
                            [col] AS col_name ,
                            ' SELECT ' + QUOTENAME([col]) + ',* FROM '
                            + QUOTENAME([schema]) + '.' + QUOTENAME([table])
                            + ' WITH (NOLOCK) WHERE ' + QUOTENAME([col])
                            + ' LIKE ''' + @char + '%''' AS sql_select ,
                            '--UPDATE ' + QUOTENAME([schema]) + '.'
                            + QUOTENAME([table]) + ' SET ' + QUOTENAME([col])
                            + '= @new WHERE ' + QUOTENAME([col]) + ' LIKE '''
                            + @char + '%''' AS sql_update ,
                            '--DELETE FROM ' + QUOTENAME([schema]) + '.'
                            + QUOTENAME([table]) + ' WHERE ' + QUOTENAME([col])
                            + ' LIKE ''' + @char + '%''' AS sql_delete
                    FROM    #getchar_tmp2   
                    DROP TABLE #getchar_tmp2       
                END  
        END  
GO 
EXEC sp_MS_marksystemobject '[dbo].[sp_get_char]'