Freaking column names

[NickName]

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'
TIA

[Erland Sommarskog]

NickName (da****@rock.co m) writes:

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'

Correct. In difference to most other languages, you cannot pass
expressions as parameters to stored procedures. You can only psss
constants and variables.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Stu]

How about this? Rather than a stored procedure, generate a script to
run against all of your columns, like so:

SELECT 'exec sp_rename ''' + Table_Name + '.'
+ Column_name + ''', ''' +
REPLACE(COLUMN_ NAME, '-', '') + ''', ''COLUMN'''
FROM INFORMATION_SCH EMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

Cut and paste the results of the query into a new QA window and execute
the statements.

HTH,
Stu

[NickName]

Actually, I could do something like
select @colTemp = Replace(col,'-','')
then
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',@colTemp,'COL UMN'

I thought about that before posting but I did not try (just don't know
why somtimes I'm so freaking lazy).

Thanks for the note though.

Don

[NickName]

Interesting idea, however, I don't think it works.

[Stu]

Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu

[NickName]

Yeah, I see. Problem resolved. Thanks though.
Stu wrote:

Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu