Searching for a string in all tables of database in SQL Server 2000

There are many answers in many places about this question, such as:

Search for a string in all tables, rows and columns of a DB

  • sql query executes in sql server, but not on program
  • SQL Server version 612 , 655?
  • SQL - Conditions on two tables with a sum(case when)
  • On Views and Indexes (SQL Server 2008 R2)
  • Get string between 2 characters that repeats several times in sql server
  • Executing SQL query from C# code
  • However I am using SQL Server 2000 and need to do the same thing: search all tables in a database for a specific string.

  • Disable Transaction Log
  • SQL Substring Issue
  • Efficient query to split a delimited column into a separate table
  • Save Html data in XML to sql using stored procedure
  • How to read system time in WPF for updating SQL Server timestamp field?
  • Using UPDATE in stored procedure with optional parameters
  • One Solution collect form web for “Searching for a string in all tables of database in SQL Server 2000”

    DECLARE @SearchString VARCHAR(32);
    SET @SearchString = 'Something';
    
    CREATE TABLE #results(o NVARCHAR(512), c SYSNAME, value NVARCHAR(4000));
    
    DECLARE @sql NVARCHAR(4000), @o NVARCHAR(512), @c SYSNAME;
    
    DECLARE c CURSOR LOCAL FAST_FORWARD FOR 
       SELECT QUOTENAME(u.name) + '.' + QUOTENAME(o.name), QUOTENAME(c.name)
    FROM sysobjects AS o
    INNER JOIN syscolumns AS c ON o.id = c.id
    INNER JOIN sysusers AS u ON o.uid = u.uid
    WHERE c.xtype IN (35, 99, 167, 175, 231, 239) AND o.xtype = 'U';
    
    OPEN c;
    
    FETCH c INTO @o, @c;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
      SET @sql = N'INSERT #Results(o,c,value) SELECT @o, @c, 
        CONVERT(NVARCHAR(4000), ' + @c + ') FROM ' + @o + ' WHERE ' 
        + @c + ' LIKE ''%' + @SearchString + '%'';';
      EXEC sp_executesql @sql, N'@o NVARCHAR(512), @c SYSNAME', @o, @c;
      FETCH c INTO @o, @c;
    END
    
    CLOSE c;
    DEALLOCATE c;
    
    SELECT o, c, value FROM #Results;
    
    DROP TABLE #Results;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.