I have a specific value let's say string 'comments'. I need to find all instances of this in the database as I need to do an update on the format to change it to (*) Comments. How can I do this? Please help! The database is in SQL Server 2000 format.
A:
Check this link: http://vyaskn.tripod.com/search%5Fall%5Fcolumns%5Fin%5Fall%5Ftables.htm
They do have one for replacing across the database as well, but I don't have the link to hand.
A S
2009-11-25 12:17:43
i already looked at that and could not get it to work...
jeff
2009-11-25 12:58:49
I do not understand what that procedure is doing... where do I put in 'comments' i.e. what i'm searching for, i've tried entering itSET @SearchStr2 = '%comments%' but get a whole load of syntax errors
jeff
2009-11-25 13:21:59
Jeff,The script on that page creates a stored procedure. Run it completely as is in your database. Then execute the procedure as follows:EXEC SearchAllTables 'Computer'The proc will then run and give you the results. If you still can't get it to work let me know.
A S
2009-11-26 08:50:20
it's alright i ended up just going through the db and updated them manually... thanks for your help.
jeff
2009-11-26 15:42:56
A:
You could query the sys.tables database view to get out the names of the tables, and then use this query to build yourself another query to do the update on the back of that. For instance:
select 'select * from '+name from sys.tables
will give you a script that will run a select * against all the tables in the system catalog, you could alter the string in the select clause to do your update, as long as you know the column name is the same on all the tables you wish to update, so your script would look something like:
select 'update '+name+' set comments = ''(*)''+comments where comments like ''%comment to be updated%'' ' from sys.tables
You could also then predicate on the tables query to only include tables that have a name in a certain format, or are in a subset you want to create the update script for.
James B
2009-11-25 12:18:17
+2
A:
This is what you are looking for Search all fields in SQL Server Database
gg
2009-11-25 12:18:31
A:
I've just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: Search all fields in SQL Server Database
Tim
2010-02-19 12:29:40