SQL Server, How to DROP COLUMN if condition is satisfied?

Question

I have been trawling google for answers to this but no luck. Any help would be great!

I have a SQL table: tblFeedback. It stores answers to feedback questions. The Questions are held in a different table: tblQuestions. The questions on the feedback form can be changed via a user interface, therefore when the user edits the questions of replaces them, I rewrite the questions to tblQuestions and provide answer columns in tblFeedback (one for each question). Hope thats easy enough to understand.

So at the minute I have 3 questions. My tblFeedback columns look like:
FeedbackID
Name
Date
Question_1
Question_2
Question_3

I want to delete these columns and replace them with new ones when necessary. I am going down the path of using the ALTER TABLE tblFeedback DROP COLUMN ... but I cannot add any criteria using WHERE or anything else.. If I could specify something like 'if column_name starts with Question_%' or 'if column_ID > 3' but if I add WHERE after the COLUMN I get errors.

I am writing this for an asp.net app using c#.

Any help with this would be really really appreciated. Im going for a walk to calm down.

You can't include WHERE or any other DML in an ALTER TABLE command. — Will A
– Will A, Commented Aug 22, 2010 at 13:54
You've all been great but the answers were all so confusing I actually left this project for 2 days!! I am back at it now and am trying desperately to decide the best way forward.. Im thinking I should create a thrid table as suggested by Daniel below. I will probably be back with more questions in a while.. THanks everyone!! — Frank
– Frank, Commented Aug 26, 2010 at 9:18

Ilia G · Accepted Answer · 2010-08-22 14:09:26Z

Please reconsider your DB design. Drop question columns entirely and add tables

Question (QuestionID int identity, Deleted datetime, Text text) Answer (FeedbackID int FK to Feedback, QuestionID int FK to Question, Text text)

then use Question.Deleted == null as you criteria wherever to show or hide questions on your form.

gbn · Accepted Answer · 2010-08-22 13:54:55Z

IF COLUMNPROPERTY(OBJECT_ID('myTable'), 'ColumnID', 'Question_1') IS NOT NULL ALTER TABLE tblFeedback DROP COLUMN Question_1

However, this will drop all rows: you can't have some rows with Answer and some with Question for example.

If I understand you correctly, I would implement as a child table to allow 0 to n question/answer/feedback with a type to define question/answer/feedback etc.

Martin Smith · Accepted Answer · 2010-08-22 14:15:29Z

You might be better with an EAV (entity attribute vaue) style database structure for this kind of thing.

create table Question ( Id bigint not null primary key identity(1,1), Question nvarchar(max) not null ) create table Feedback ( Id bigint not null primary key identity(1,1), Date datetime not null, Name nvarchar(512) not null, ) create table FeedbackAnswers ( Id bigint not null primary key identity(1,1), FeedbackId bigint not null, QuestionId bigint not null, Answer nvarchar(max) not null )

You will probably need something else to "group" the questions together.

It does make reporting a little more involved though.

+1 I fixed up your code formatting though. Select the code to be formatted and click the 1010 icon to get it formatted as code.
Good answer but not really EAV though, just allowing 0 to n fixed type children (aka answers etc)...

Martin Smith · Accepted Answer · 2010-08-22 14:08:01Z

You would need to use dynamic SQL for this and query sys.columns if you really want to change the table structure based on column name every time the feedback form changes. This does seem an unusual requirement as it will delete historic data that you are presumably storing for some reason in the first place

but if you just want to automate something that you would otherwise need to do manually...

Example Syntax

DECLARE @dynsql NVARCHAR(4000) SELECT @dynsql = ISNULL(@dynsql + ',','') + QUOTENAME(name) FROM sys.columns WHERE name LIKE 'Question%' AND OBJECT_ID=OBJECT_ID('dbo.tblFeedback') IF(@@ROWCOUNT > 0) EXEC ('ALTER TABLE dbo.tblFeedback DROP COLUMN ' + @dynsql)

...but please, please, please, please, please don't DELETE FROM or UPDATE sys.columns or friends - the system shouldn't let you, but if it has been configured to permit this DON'T DO IT!
Might be best, Martin - am just envisaging the OP fiddling with sys.columns and having fun... ahem.

AjV Jsy · Accepted Answer · 2014-07-21 14:34:10Z

For the benefit of those finding this question later, this kind of thing is possible as the basis of conditionally executing a command on the results of a query (in this case checking a column contains nothing but NULL before taking an action like dropping the column) :

IF (SELECT COUNT([my column]) FROM [my table] WHERE [my column] IS NOT NULL) = 0 PRINT 'remove' ELSE PRINT 'keep';

Collectives™ on Stack Overflow

SQL Server, How to DROP COLUMN if condition is satisfied?

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

2 Comments

Comments

Related