How do I go through every user table in the database

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..
AshaRRichardsonAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
AshaRRichardsonAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
What errors did you get??  It's impossible to debug without knowing the specific error(s).
0
 
AshaRRichardsonAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
AshaRRichardsonAuthor Commented:
Dear ScottPletcher,
Why does this do a dbcc on all of the tables?  I didn't see a dbcc in the code?
0
 
AshaRRichardsonAuthor Commented:
Here is an example of the messages after this is ran.  I do see the print statement where is what updateing the table.  
0
 
AshaRRichardsonAuthor Commented:
[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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
D'OH, now that I think about it, the ALTER statement might use a DBCC underneath, not sure.  
0
 
AshaRRichardsonAuthor Commented:
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
 
AshaRRichardsonAuthor Commented:
How do I update primary Keys, it erros out on primary keys..
0
 
AshaRRichardsonAuthor Commented:
There are also triggers on these tables that inforce the primary key, I guess I would have to remove them as well?  
0
 
Scott PletcherSenior DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.