Trying to drop an invalid column name without recreating the table.

Question

Post reply

Trying to drop an invalid column name without recreating the table.

duncanburtenshaw

Ten Centuries

Points: 1323
More actions
July 23, 2018 at 8:09 am

#407234

Hi all,
Was wondering if it was possible to drop an invalid column on a table which a developer has somehow created, without dropping /recreating the whole table (as it's in use).
the column is >128 chars, appears on a select as NULL.. we don't need it..
SELECT *
FROM sys.columns c
WHERE c.name LIKE 'alter procedure%'
EXEC sys.sp_rename @objname = N'Metadata.dbo.ReportSummaryDataSets.[ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************]',
@newname = 'AA',
@objtype = 'COLUMN'
have tried dropping the column in MS Studio, using the DROP COLUMN command, and sp_rename, but the name won't allow me to put it in [] - as it already contains these characters
the column name is
name
"ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************"
without the quotes on the end...
any ideas please? else we will have to stop all writing to this table and recreate it without this column
on Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
thanks all!
Duncan.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

Can you post the results of running 'SCRIPT TABLE AS CREATE' please?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Bert-701015 SSCommitted Points: 1615 More actions · Answer 2

if the " (double quotes) are not part of the column name try:

alter table A drop column "ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************";

sgmunson SSC Guru Points: 110640 More actions · Answer 3

duncanburtenshaw - Monday, July 23, 2018 8:09 AM
Hi all,
Was wondering if it was possible to drop an invalid column on a table which a developer has somehow created, without dropping /recreating the whole table (as it's in use).
the column is >128 chars, appears on a select as NULL.. we don't need it..
SELECT *
FROM sys.columns c
WHERE c.name LIKE 'alter procedure%'
EXEC sys.sp_rename @objname = N'Metadata.dbo.ReportSummaryDataSets.[ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************]',
@newname = 'AA',
@objtype = 'COLUMN'
have tried dropping the column in MS Studio, using the DROP COLUMN command, and sp_rename, but the name won't allow me to put it in [] - as it already contains these characters
the column name is
name
"ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************"
without the quotes on the end...
any ideas please? else we will have to stop all writing to this table and recreate it without this column
on Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
thanks all!
Duncan.

Have you tried using single quote marks ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

duncanburtenshaw Ten Centuries Points: 1323 More actions · Answer 4

got this error

SQL2.sql: Error (1,1): ALTER TABLE DROP COLUMN failed because column 'ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************' does not exist in table 'ReportSummaryDataSets'.

duncanburtenshaw Ten Centuries Points: 1323 More actions · Answer 5

Phil Parkin - Monday, July 23, 2018 8:20 AM
Can you post the results of running 'SCRIPT TABLE AS CREATE' please?

USE Metadata
GO

CREATE TABLE dbo.ReportSummaryDataSets (
ReportSummaryDataSetsID INT IDENTITY,
TaskID VARCHAR(100) COLLATE Latin1_General_CI_AS NULL,
OrderlineItemID INT NOT NULL,
ReportType VARCHAR(50) COLLATE Latin1_General_CI_AS NULL,
ReportName VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL,
DataSet VARCHAR(255) COLLATE Latin1_General_CI_AS NOT NULL,
RecordCount INT NULL,
BufferDistance INT NULL,
AreaSearched FLOAT NULL,
InScotland INT NULL DEFAULT (0),
[ALTER PROCEDURE [dbo]]]].[AreaToEdge]]]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************] INT NULL
) ON [PRIMARY]
GO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Just double the closing brackets
[ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10 /*********************************]

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

The double quotes method seems to work, while using single quotes does not, which is interesting.

DROP TABLE IF EXISTS #garbage;

CREATE TABLE #garbage ( id INT );

ALTER TABLE #garbage ADD "ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************" INT;

ALTER TABLE #garbage DROP COLUMN "ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************";

SELECT * FROM #garbage g

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

duncanburtenshaw Ten Centuries Points: 1323 More actions · Answer 8

Luis Cazares - Monday, July 23, 2018 8:36 AM
Just double the single brackets
[ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10 /*********************************]

thank you very much all, the command below dropped the column successfully.. much obliged!

alter table ReportSummaryDataSets drop column
[ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

Phil Parkin - Monday, July 23, 2018 8:36 AM
The double quotes method seems to work, while using single quotes does not, which is interesting.
DROP TABLE IF EXISTS #garbage;
CREATE TABLE #garbage ( id INT );
ALTER TABLE #garbage ADD "ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************" INT;
ALTER TABLE #garbage DROP COLUMN "ALTER PROCEDURE [dbo].[AreaToEdge] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************";
SELECT * FROM #garbage g

Thank you for the code sample, here's my suggestion which also works
CREATE TABLE #garbage ( id INT );

ALTER TABLE #garbage ADD [ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************] INT;

SELECT * FROM #garbage g

ALTER TABLE #garbage DROP COLUMN [ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10/*********************************];

SELECT * FROM #garbage g

DROP TABLE #garbage;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 10

duncanburtenshaw - Monday, July 23, 2018 8:40 AM
thank you very much all, the command below dropped the column successfully.. much obliged!
alter table ReportSummaryDataSets drop column
[ALTER PROCEDURE [dbo]].[AreaToEdge]] @WKT VARCHAR(MAX), @SEARCH_DISTANCE INT, @MARGIN INT = 10
/*********************************]

You're welcome.
When dealing with weird names, you could always try to wrap them in QUOTENAME() to get the correct way of escaping the characters. Just be careful with the length of the string.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2