Fortunately, there is help on the way! I have developed a script based on a previous post, that will scan for the PONUMBER field in all tables in the company database. The script will automatically produce another script in the Results pane that can be copied and pasted into a new Query window and be executed against the company database.
The following example shows the script with the new PO number (@newponumber) and the old PO number (@oldponumber) variables being used to facilitate the interfacing with the person executing the change.
-- Created by Mariano Gomez, MVP -- This code is licensed under the Creative Commons -- Attribution-NonCommercial-ShareAlike 3.0 Unported License. -- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode -- DECLARE @NEWPONUMBER CHAR(25), @OLDPONUMBER CHAR(25); SET @NEWPONUMBER = 'PO1023'; SET @OLDPONUMBER = 'PO1001'; SELECT DISTINCT 'UPDATE ' + RTRIM(OBJS.NAME) + ' SET PONUMBER = ''' + RTRIM(@NEWPONUMBER) + ''' WHERE PONUMBER = ''' + RTRIM(@OLDPONUMBER) + '''' FROM SYSCOLUMNS COLS INNER JOIN SYSOBJECTS OBJS ON (COLS.ID = OBJS.ID) INNER JOIN SYSINDEXES INDX ON (COLS.ID = INDX.ID) WHERE (COLS.NAME = 'PONUMBER') AND (OBJS.XTYPE = 'U') AND (INDX.ROWCNT > 0);
When this script is executed against the company database, it produces the following results:
UPDATE POP10100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP10110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP10310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP10500 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP30100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP30110 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP30310 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE POP40100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' UPDATE SOP60100 SET PONUMBER = 'PO1023' WHERE PONUMBER = 'PO1001' (9 row(s) affected)
The above results can be copied and pasted into a new query window to effect all the changes in the tables where the PONUMBER field is used.
NOTE: If using third party products that use a different table column name, you will need to replace accordingly in the script. Enjoy!
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
1 comment:
Thanks for this, it was very helpful!
Post a Comment