Sql Server-实用技巧-找一个字符串所在的位置

遍历整个数据库,查找包含某个字符串存储的表和字段有哪些，就不多说，直接上代码了

     IF (EXISTS(SELECT * FROM sysobjects WHERE name='SP_FindValueInDB' AND type='P')) --变量2:存储过程名(一般不用改)
     DROP PROCEDURE SP_FindValueInDB                                                  --变量2:存储过程名(一般不用改)
     go
     -----------------
     CREATE PROCEDURE [dbo].[SP_FindValueInDB]                                   --变量2:存储过程名(一般不用改)
        (
          @value VARCHAR(1024)
        ) 
     AS
     BEGIN
          SET NOCOUNT ON;
          DECLARE @sql VARCHAR(1024) 
          DECLARE @table VARCHAR(200) 
          DECLARE @column VARCHAR(200) 
          CREATE TABLE #t ( 
               tablename VARCHAR(200), 
               columnname VARCHAR(200) 
           ) 
          DECLARE TABLES CURSOR FOR 
          SELECT o.name, c.name FROM syscolumns c 
          INNER JOIN sysobjects o ON c.id = o.id 
          WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) 
          ORDER BY o.name, c.name 
          OPEN TABLES 
                FETCH NEXT FROM TABLES 
                INTO @table, @column 
                WHILE @@FETCH_STATUS = 0 
                BEGIN 
                     SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] ' 
                     ---------------------------------这里采用包含查询,可以根据实际情况,设置为等于或其他
                     SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') ' 
                     SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', ''' 
                     SET @sql = @sql + @column + ''')' 
                     EXEC(@sql) 
                     FETCH NEXT FROM TABLES 
                     INTO @table, @column 
                END 
           CLOSE TABLES 
           DEALLOCATE TABLES 
           SELECT * FROM #t 
           DROP TABLE #t 
     End
     go
     -----------------
     EXEC  SP_FindValueInDB '张三'                                                      --变量3:填写要查询的字符串