2

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

However I am using SQL Server 2000 and need to do the same thing: search all tables in a database for a specific string.

Community
  • 1
  • 1
Mtok
  • 1,600
  • 11
  • 36
  • 62
  • Is your problem with the SQL in those other answers, or that you can't find a program to use to run it? – Jon Egerton Feb 20 '13 at 15:10
  • Yes it is about the SQL in other answers. I mean I couldnt make them work for SQL Server 2000. – Mtok Feb 20 '13 at 15:13
  • 1
    try this link : http://www.richnetapps.com/searching_in_all_tables_and_columns_of_a/ – Pandian Feb 20 '13 at 15:13
  • So, given the sql you've decided to try (the accepted answer from that other question?) what problem are you having with it (specifically)? - it doesn't look that far off usable in SQL 2000. You should edit your question to include the code you decided to use and state what problem you have converting it. At the moment we've no idea what you're actually trying to run. – Jon Egerton Feb 20 '13 at 15:17
  • 1
    The only obvious issue I see with the code you linked to is that `sys.objects` (for example) is `sysobjects` in SQL 2000. But those differences are [documented](http://msdn.microsoft.com/en-us/library/ms187997(v=sql.90).aspx) so it should be easy to modify the code. – Pondlife Feb 20 '13 at 15:20
  • The other answer also uses nested cursors, an approach which isn't necessary. – Aaron Bertrand Feb 20 '13 at 15:44

1 Answers1

2
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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490