Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How do I go through every user table in the database

Posted on 2004-10-22
17
Medium Priority
?
182 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
Comment
Question by:AshaRRichardson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
17 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12382720
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 70

Expert Comment

by:Scott Pletcher
ID: 12382743
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 71

Expert Comment

by:Qlemo
ID: 12382807
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 12382943
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
ID: 12383527
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 70

Expert Comment

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

Author Comment

by:AshaRRichardson
ID: 12383677
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 70

Expert Comment

by:Scott Pletcher
ID: 12383853
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
ID: 12383914
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
ID: 12384110
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
ID: 12384115
[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 70

Expert Comment

by:Scott Pletcher
ID: 12384337
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 70

Expert Comment

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

Author Comment

by:AshaRRichardson
ID: 12385143
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
ID: 12385215
How do I update primary Keys, it erros out on primary keys..
0
 

Author Comment

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

Expert Comment

by:Scott Pletcher
ID: 12385324
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

636 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