Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    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'

    Please be gentle, I'm a procedure newb.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SQLS2000
    Code:
    SELECT *
    FROM   sysobjects
    WHERE   type='U'
    
    SELECT o.name As [tableName]
          , c.name As [column]
          , c.*
    FROM   sysobjects o
     INNER
      JOIN syscolumns c
        ON o.id = c.parent_id
    WHERE   o.type='U'
    --datatype is in syscolumns somwhere; forget the name
    Changes manually.

    For SS2K5 look in the sys schema
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    Thanks George ... that should get me started ... looking that the original post, I forgot to state this is for SQL Server 2000.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    George - didn't you ask this question a bit ago and I pointed you towards one of blindman's posts?

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    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?
    Last edited by FastCougar; 01-22-08 at 17:07.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You should only edit column names and table names using ALTER TABLE statements.

    To Pootle: you really are the archiver; I did ask this ages ago!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Posts
    42
    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.

    Code:
    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
    WHERE   so.type='U'
    AND so.name='KJV'
    AND sc.xtype IN (
        SELECT st.xtype
        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.

    Make sense?
    Last edited by FastCougar; 01-22-08 at 21:21.

  8. #8
    Join Date
    Jul 2007
    Posts
    96
    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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not give us the real life reason; not "mike changed his name"?
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Posts
    42
    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:

    Find: <authorID>3457</authorID>
    Replace with: <authorID>12543</authorID>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •