Update all tables in a database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update all tables in a database

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-14 : 14:30:42

I have to apply an LTRIM statement to one particular column in a table. There are multiple tables in the database which have this column in common and i need to update all such tables/columns in that database. Is there a way to code this into a single statement as opposed to rewriting it multiple times for the different tables? The statement, as it is written now is:
USE Evidence
UPDATE EVP_COC
SET EVPNO=LTRIM(RTRIM(REPLACE(EVPNO,'RAV00EVP000','RCSO00EVP00')))

thanks

james

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-14 : 14:35:43

You can only update one table at a time, so you would need multiple statements. Are you instead asking for a code generator? If so, that's easy with INFORMATION_SCHEMA.COLUMNS view. Let me know.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-14 : 14:54:06

I know nothing of this 'code generator' you speak of. please 'splain.

thanks

james

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-14 : 14:56:57

This will generate the code. You'll need to copy/paste to execute it. Please ensure the generated code is correct before running it. I take no responsibility if it is wrong.

SELECT 'UPDATE ' + TABLE_NAME + ' SET EVPNO=LTRIM(RTRIM(REPLACE(EVPNO,''RAV00EVP000'',''RCSO00EVP00'')))'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'EVPNO'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-14 : 15:02:06

Very much ranked as 'awesome'. This will come in handy as they start burying me with more 'stuff' here.

thank you!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-14 : 15:06:39

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-14 : 23:42:42

[code]
EXEC sp_Msforeachtable 'UPDATE ?
SET EVPNO=LTRIM(RTRIM(REPLACE(EVPNO,''RAV00EVP000'',''RCSO00EVP00'')))'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-15 : 04:58:42

quote:
Originally posted by visakh16

EXEC sp_Msforeachtable 'UPDATE ?
SET EVPNO=LTRIM(RTRIM(REPLACE(EVPNO,''RAV00EVP000'',''RCSO00EVP00'')))'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Although this works, the procedure is undocumented and can be be removed from future release of SQL Server without any intimation. sp_makewebtask is one such system procedure which is not available from version 2008

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-15 : 06:47:11

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

EXEC sp_Msforeachtable 'UPDATE ?
SET EVPNO=LTRIM(RTRIM(REPLACE(EVPNO,''RAV00EVP000'',''RCSO00EVP00'')))'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Although this works, the procedure is undocumented and can be be removed from future release of SQL Server without any intimation. sp_makewebtask is one such system procedure which is not available from version 2008

Madhivanan

Failing to plan is Planning to fail

Still it can be used as long as its one off activity and you're not going to put it in production

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources