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 6 3203
NickName (da****@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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bart Van der Donck |
last post by:
Hello,
I am using MySQL 4.0.
Say that I have a table named "mytable" having a column "ID" and a
column "test columnname".
When dumping:
mysqldump --opt DATABASE -uUSER -hHOST -pPASS >...
|
by: Matthew Cascio |
last post by:
My understanding is that using reserved words as column names is
allowable as long as they are quoted. I am trying to create a table
dynamically with columns defined by the first row of a text...
|
by: Randell D. |
last post by:
Folks,
I have a table of addresses and a seperate table with contact names -
All addresses tie to one or more names - I would like to keep track of
the number of names 'belonging' to an address...
|
by: noor |
last post by:
Hi
I have been working since 2 days to device a method to export sql table
into csv format. I have tried using bcp with format option to keep the
column names but I'm unable to transfer the file...
|
by: Colleyville Alan |
last post by:
I am trying to turn a short and fat (63 columns) table into one that is tall
and skinny (7 columns). Basically, I am trying to create a "reverse
crosstab" using a looping structure in VBA along...
|
by: Scot L. Harris |
last post by:
Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat
8.0 system.
I am writing some php scripts where I want to generate a list of the
column names in a particular table that...
|
by: Charleees |
last post by:
Hi all,
I have a DataGrid with Template Columns.....
There are LAbels,Linkbuttons in the Single Row..
I have to set the Constant Column width for those Template Columns in
Grid...
Wat...
|
by: Jon Davis |
last post by:
Bill Wagner posted something here ..
http://msdn2.microsoft.com/en-us/vcsharp/default.aspx
"Local Type Inference, Anonymous Types, and var"
"Of all the features in C# 3.0, local type inference...
|
by: christianlott1 |
last post by:
I want to provide users with an interface to create a custom merge
(all in Access, not Word). User will put in a set of brackets ("<>")
in a memo field and when they click the merge button it will...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |