Solved

How do I go through every user table in the database

Posted on 2004-10-22
181 Views
Last Modified: 2008-02-01
Dear Expert:  I support a database of around 2500 user-defined tables.  I have had a request to find all records in all tables where orgid = 06.1.08.N810, and change it to 06.1.02.N810.  I  figuered this could be done by a cursor, ??? I have no idea how to get started.  This may or may not be foreign key related through the tables..
0
Question by:AshaRRichardson
    17 Comments
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    I think this will do it:



    EXEC sp_MSForEachTable '
    IF EXISTS(
    SELECT 1
    FROM syscolumns
    WHERE name = ''orgid''
    AND OBJECT_NAME(id) = PARSENAME(''?'', 1) )
    BEGIN
          PRINT ''Updating Table ?''
          EXEC(''UPDATE ? SET orgid = ''''06.1.02.N810'''' WHERE orgid = ''''06.1.08.N810'''''')
    END --IF
    '
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    sp_MSForEachTable is an undocumented, but rather well known, sp to execute a command(s) against every user table in a db.  It will create a cursor and go thru all user table names.  You can see the source code for sp_MS... in the master db.
    0
     
    LVL 67

    Expert Comment

    by:Qlemo
    A simple start w/o error checking would be:

    exec sp_msforeachdb 'alter table ? nocheck constraints all';
    exec sp_msforeachdb 'update ? set orgid = ''06.1.02.n810''' where orgid = ''06.1.08.N810''';
    exec sp_msforeachdb 'alter table ? check constraints all';


    This goes thru every table, switches all constraints off, chanigng the value, and re-implementing constraints again.

    0
     
    LVL 68

    Accepted Solution

    by:
    Yes, sorry, forgot about the constraint disable/enable.  However, you really only need to do them for tables that have that column name.


    EXEC sp_MSForEachTable '
    IF EXISTS(
    SELECT 1
    FROM syscolumns
    WHERE name = ''orgid''
    AND OBJECT_NAME(id) = PARSENAME(''?'', 1) )
    BEGIN
         PRINT ''Updating Table ?''
         ALTER TABLE ?  NOCHECK CONSTRAINT ALL
         EXEC(''UPDATE ? SET orgid = ''''06.1.02.N810'''' WHERE orgid = ''''06.1.08.N810'''''')
         ALTER TABLE ?  CHECK CONSTRAINT ALL
    END --IF
    '
    0
     

    Author Comment

    by:AshaRRichardson
    I did both of these examples, and neither of them executed without errors.  Can you please help me, I don't know enough about the commands to debug the code.

    Thanks very much!
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    What errors did you get??  It's impossible to debug without knowing the specific error(s).
    0
     

    Author Comment

    by:AshaRRichardson
    Sorry, I forgot to grab the end quote on ScottPletcher.  It is running right now..  Do I want to do this off hours because I have to disable contraints when other people are on the database?
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    Yes, that is a serious risk to data integrity, so it's best to do this in off hours if you can.  That is also why you at least want to do the DISABLE one at a time, and ENABLE as soon as possible.
    0
     

    Author Comment

    by:AshaRRichardson
    Dear ScottPletcher,
    Why does this do a dbcc on all of the tables?  I didn't see a dbcc in the code?
    0
     

    Author Comment

    by:AshaRRichardson
    Here is an example of the messages after this is ran.  I do see the print statement where is what updateing the table.  
    0
     

    Author Comment

    by:AshaRRichardson
    [DELTEK].[Z_PJPMJAS_SUM]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[ORG_SEC_GRP_SETUP]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[COMP_PLAN_LN]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[TEXT_WHERE_USED]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[Z_PJPCOMPP_PL_ALL]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[IWO_EXP_ACCT_LN]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[Z_AOPITEM_PCP_WK2]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[ACCT_ENTR_GRP]
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.
    [DELTEK].[STATE_SUTA_RPTING]
    DBCC execution complete
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    Don't know.  I don't get any messages related to DBCC when I run the same commands.

    You must have made at least some changes to the code because I don't see the "Updating " before the table name.  Perhaps you could post the actual code that you are running.
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    D'OH, now that I think about it, the ALTER statement might use a DBCC underneath, not sure.  
    0
     

    Author Comment

    by:AshaRRichardson
    I get this message:  Cannot insert duplicate key row in object 'ORG' with unique index 'PI_0058'.
      Does that mean I need to drop the index before I can make the changes to update the row?  Us there a different way to do this so I don't have to drop indexes, and disable contraints?
    0
     

    Author Comment

    by:AshaRRichardson
    How do I update primary Keys, it erros out on primary keys..
    0
     

    Author Comment

    by:AshaRRichardson
    There are also triggers on these tables that inforce the primary key, I guess I would have to remove them as well?  
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    The duplicate msg just means that when it attempted to change the value, there was also a row with a value of "06.1.02.N810" and a unique constraint on that column(s) prevented SQL from UPDATEing to that value.


    You *cannot* update primary keys, by definition.  That's why I *HATE* them so.  They violate relational rules.  I'll be thrilled when you don't have to specify them to make referential integrity useable.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    If you are an Active Directory administrator working with AD data in SQL Server, then this article is for you! INTRODUCTION As AD admins or those having to deal with AD data, you probably have had to convert a timestamp or two like last logo…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now