Find and Replace

[uncgis]

Does anyone know how to use a find and replace in sql server 2000? I need to search all tables and columns..

Thanks for any suggestions!

 

[mrdenny]

You can use the replace function but you will need to search each column in each table one at a time.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[dgillz]

Cut and paste the code below into Query Analyzer, and replace the red below with the field you are looking for.

select A.name as table_name, B.name as column_name
from sysobjects A 
inner join syscolumns B on A.id=B.id
where A.xtype='U' and B.name = '[red]item_no[/red]'
order by A.name, B.name

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[uncgis]

Thanks for responding!

I tried the code above but it does not return any value.

what is the A.xtype='U'? Is that the user tables?

Thanks again

 

[dgillz]

When you first get into Query Analyzer, it defaults to the master database. Be sure to change to the database you are trying to do the find in.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[dgillz]

U stands for User Table I believe. This may be a proprietary value. If you browse the raw data in your sysobjects table, you will see the values there and you may need to change the where clause accordingly.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[uncgis]

Thanks again for the help...

this code works out great and I will definitely use it again…but, it only searches the column name and not what is in the columns.

Does anyone know how to search through all the text, in all the tables?

thanks