Unanswered: Replace Text in Text, Char & Varchar fields all at once?
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?
1) Loop over entire database and get all user tables
2) Loop over all user tables and get all fields
3) Loop over all fields and determine the field type
4) switch between field types and change a string of text from 'a' to 'b'
SELECT o.name As [tableName]
, c.name As [column]
FROM sysobjects o
JOIN syscolumns c
ON o.id = c.parent_id
--datatype is in syscolumns somwhere; forget the name
So, this would be possible with one large procedure then correct? I need to learn more about TransactSQL procedure formatting, but I assume I could then loop over this result set by assigning variables to the results and then start my inner loop and so forth. Is that correct?
Maybe I didn't make my needs clear ... I need this information to know all the table & column names of text, varchar, char, ntext, nvarchar & nchar so that I can then loop over all these tables & column pairs and change a text value within them. So, for example, I need to change the text 'Mike said' to 'Tom said' ... basically, change every occurance of 'Mike' to 'Tom' within all the tables in a database. I need to know the column name & type so that I can change my text replacement method accordingly since text field types are handled differently than varchar field types.
So far, I have this much written, which tells me the table name, column name and column types within a database.
SELECT so.name As [tableName]
,sc.name As [columnName]
,st.name As [columnType]
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id INNER JOIN systypes st ON st.xtype = sc.xtype
AND sc.xtype IN (
FROM systypes st
WHERE st.name IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext')
I now need to incorporate the results from the above query to loop over and make text changes within the table according to it's column type.
I'm not 100% sure if it's possble. But if your db gets big you are going to face serious performance problems with that query. Maybe you could consider using a Full-Text Catalog. I'm pretty sure it returns the table names etc
OK then, we have a CMS system that stores stub file information in WDDX packets in text fields within the database. Unfortunately, user ID's and other bits of information are hard coded into the WDDX packets. This normally wouldn't be a problem, but our CMS is broken out across 6 "authoring" servers, all of which have a different "users" table. We are consolidating the users tables into one single users table. So, the problem is that with multiple user tables, you have the same 'author' with multiple id's, so consolidation requires mapping them together in order to change their old hard coded information with the new id.
Now, the WDDX information is stored for many different elements of ALL the authors pages. The system also has versioning, so the multitude of place where data needs to change is HUGE and mostly unknown ... thus the need to query the table metadata. So, rather than looking for "Mike" and changing to "Tom", it will be more like: