Freaking column names

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'

TIANickName (dadada@.rock.com) 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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

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

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

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|||Interesting idea, however, I don't think it works.|||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|||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