Apply same function to all database columns

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

SQL Server 2005 Forums

Transact-SQL (2005)

Apply same function to all database columns

Author

Topic

netu
Starting Member

1 Post

Posted - 2008-07-15 : 13:28:15

What I need to do in sql server 2005 is


for each table in mydatabase.tables
      for each column in table.columns
          do_my_thing(column)

In other words, I need to "normalize" all column names in my database.
I need to replace some specific chars in column names.
I know I probably have to use sp_tables and sp_columns but I am not very sure, how.

Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 13:32:08

use something like

sp_msforeachtable 'SELECT do_my_thing(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=''?'''

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 13:33:02

Run this and copy the result to a query window and run the pasted text.

SELECT	'EXEC DoMyThing ''' + TABLE_NAME + ''', ''' + COLUMN_NAME + ''''
FROM	INFORMATION_SCHEMA.COLUMNS
WHERE	DATA_TYPE IN ('VARCHAR', 'NVARCHAR')

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources