Double Quotes Around Field Names

Transact-SQL
1

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
2

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

1 Like
3

Thanks Mike - I will look into that

1 Like
4

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

1 Like
5

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
6

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
7

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

8

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

9

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
10

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

11

the message is just informational,

12

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