SQLTeam.com | Weblogs | Forums

Double Quotes Around Field Names

Someone has created a table with approx 300 fields (I know not great) but every field name has double quotes around them. Assume they used some 3rd party software to generate the table!?

Is there any easy way of removing the double quotes around each field name?

1 Like

you can read information_schema.Columns and generate an exec sp_rename script to run to remove them

1 Like

Thanks Mike - I will look into that

1 Like

But use sys.columns, and other sys. views, rather than any information_schema views.

1 Like

For example:

SELECT 'EXEC sp_rename ''' + OBJECT_NAME(object_id) + '.' + name + ''', ' +
    '''' + REPLACE(name, '"', '') + ''', ''COLUMN''; '    
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.<YOUR_TABLE_NAME_HERE>') AND
    name LIKE '%"%'
ORDER BY column_id
1 Like

just be careful that other objects downstream do not reference these columns. you might change them and break everything else that might be referencing them such as views, stored procedures etc.if they have double quotes most probably (guessing) they have a space in between the double quotes "First Name".

1 Like

Thanks for the example - my SQL is a bit rusty so will help a lot

Thanks for advice - it's just an initial table someone set up so nothing downstream yet

here is what I mean

use sqlteam
go


create table moonshine(["First Name"] nvarchar(50))

insert into moonshine
select 'Come Boocha' union
select 'Corn' union
select 'Apple'
go


create proc moonshine_sp
as
begin
	select ["First Name"] from moonshine
end
go

EXEC sp_rename 'moonshine.["First Name"]', 'First Name', 'COLUMN';  
GO  



exec moonshine_sp
go


drop proc moonshine_sp

drop table moonshine

and when you run the sp_rename is pops the following message

Caution: Changing any part of an object name 
could break scripts and stored procedures.
2 Likes

Thanks for that - I did get that message.

The table fields have no spaces in them - underscores are used where spaces might be.

I'm pretty sure nothing references the fields as there is no data in the table yet (that's my job but I'm trying to tidy up the table field names first)

Any idea how I get around this message?

Edit: Scratch that I can see it worked but hitting refresh didn't show it

the message is just informational,

Thank Everyone - working now

With a tiny tweak to ScottPletcher's example (adding square brackets) and the additional help of yosiasz it works nicely..........

SELECT
'EXEC sp_rename ''' + OBJECT_NAME(object_id) + '.[' + name + ']'', ' +
'''' + REPLACE(name, '"', '') + ''', ''COLUMN''; '
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.DQS_ALL_Data') AND name LIKE '%"%'
ORDER BY
column_id