| Author |
Topic |
|
iammikey101
Starting Member
2 Posts |
 Posted - 2008-08-13 : 19:39:15
|
| I need to update a single coiumns info (CUSTNMBR) in about 500 dbo Tables. The customers number has changed and I would like to make the change to all the tables.Currently I can do this with the following command (but I need to name all 500 dbo names) can i do this with a * or /*Current Script is:update dbo.RM00102set CUSTNMBR = '111'where CUSTNMBR = '10'update dbo.RM00103set CUSTNMBR = '111'where CUSTNMBR = '10'etc..etc |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-13 : 19:42:13
|
| An OR will not work.You can generate the code like this:SELECT 'UPDATE dbo.' + TABLE_NAME + ' SET CUSTNMBR = ''111'' WHERE CUSTNMBR = ''10'''FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIK/= ...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
iammikey101
Starting Member
2 Posts |
Posted - 2008-08-15 : 12:44:47
|
| My knowledge of SQL scripting is limited, I only know basics. How would the script work? The program in question is Great Plains and the data has to be changed from withen SQL. The customers number needs to be changed and we have to update all records to the new customer number. There are over 400 tables that have the column "CUSTNMBR" that needs to be updated from the old number to the new one. How should the script be writted so I do not have to reference all 400+ table names? Is there a * command?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-15 : 12:50:31
|
| Use sp_Msforeachtable . |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-16 : 17:55:42
|
| sp_Msforeachtable will touch every user table in the db, can't use it if you don't want to update all tables. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-16 : 20:52:52
|
quote:
Originally posted by rmiao
sp_Msforeachtable will touch every user table in the db, can't use it if you don't want to update all tables.
.That' what op is looking for |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-16 : 21:56:30
|
| Wow, count 3. OP likes to update all tables in dbo schema, but sp_Msforeachtable will touch all tables in all schema. By the way, I like to know how you can do that with sp_Msforeachtable. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-17 : 00:42:03
|
| Needless to argue like this:Here is what OP says:"I would like to make the change to all the tables."Doesn't matter with what owner table has. Also there is no schema thing in SQL 2000 . |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-17 : 00:47:40
|
| If you are talking about schema , then you can also update tables with certain schema. You can do like this:Exec sp_MSforeachtable @command1 = yourstatement to Update,@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'yourschema') |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-17 : 21:50:44
|
| >> I need to update a single coiumns info (CUSTNMBR) in about 500 dbo Tables. The customers number has changed and I would like to make the change to all the tables.Did you see DBO part? True, sql2k doesn't have schema but has object owner. And here are queries posted:update dbo.RM00102set CUSTNMBR = '111'where CUSTNMBR = '10'update dbo.RM00103set CUSTNMBR = '111'where CUSTNMBR = '10'By the way, can you post working code? |
|
|
|
|
Mass dbo Update