Delete a column in all tables

Question

Post reply

Delete a column in all tables

amadrazo2006

Old Hand

Points: 389
More actions
April 4, 2014 at 4:49 am

#290007

need to delete a column named "SSMA_TimeStamp", this column could be in some tables of db. My idea is to use sp_MSforeachtable procedure to delete the field "SSMA_TimeStamp" of the tables that this field appear. I'm trying to do this on this way.
set @mycommand='IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME="SSMA_TimeStamp")'
sp_MSforeachtable
@command1='ALTER TABLE ? DROP COLUMN SSMA_TimeStamp
@whereand = 'IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME="SSMA_TimeStamp")'
But it return an error
Someone can help me?
Thanks in advance.
Arsenio.

Viewing 8 posts - 1 through 8 (of 8 total)

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

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

What error you are getting?

sp_MSforeachtable will perform the execution on all the tables but you need to remove the column from some tables?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Lowell SSC Guru Points: 323546 More actions · Answer 2

since this is a one off, i would generate the statements, and copy/paste/run like this:

select

'ALTER TABLE '

+ quotename(object_name(object_id))

+ ' DROP COLUMN '

+ quotename(name)

from sys.columns

where name = 'SSMA_TimeStamp'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101200 More actions · Answer 3

EXEC sp_MSforeachtable '

IF EXISTS ( SELECT TOP (1) * FROM sys.columns

WHERE object_id(''?'') IS NOT NULL AND

name = ''SSMA_TimeStamp'')

BEGIN

PRINT ''Altering table "?" to remove column "SSMA_TimeStamp"''

--ALTER TABLE ? DROP COLUMN SSMA_TimeStamp

END

'

Edit: I commented out the ALTER TABLE so you could do a test run to verify. Naturally uncomment the ALTER when you're ready to actually make the table changes.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

amadrazo2006 Old Hand Points: 389 More actions · Answer 4

Thanks for your reply. I have run the sript but I get this error "Error in ALTER TABLE DROP COLUMN.The column 'SSMA_TimeStamp' not exist in table 'Doctors'. I had cretaed a DB whith 3 tables to try the script.

CREATE TABLE [dbo].City(

[Id] [int] NULL,

[LastName] [varchar](255) NULL,

[FirstName] [varchar](255) NULL,

SSMA_TimeStamp varchar(30)

) ON [PRIMARY]

CREATE TABLE [dbo].Doctors(

[Id] [int] NULL,

[LastName] [varchar](255) NULL,

[FirstName] [varchar](255) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].Persons(

[Id] [int] NULL,

[LastName] [varchar](255) NULL,

[FirstName] [varchar](255) NULL,

SSMA_TimeStamp varchar(30)

) ON [PRIMARY]

As you can see table Doctors don't have column "SSMA_TimeStamp". In my truly DB it happens, it means that in DB there are tables that have column "SSMA_TimeStamp" and others don't have this column.

Have you any idea to change the script you posted?

Thanks again

ScottPletcher SSC Guru Points: 101200 More actions · Answer 5

D'OH, sorry, quite right, here's a

CORRECTED script:

EXEC sp_MSforeachtable '

IF EXISTS ( SELECT * FROM sys.columns

WHERE object_id = OBJECT_ID(''?'') AND name = ''SSMA_TimeStamp'' )

BEGIN

PRINT ''Altering table "?" to remove column "SSMA_TimeStamp"''

--ALTER TABLE ? DROP COLUMN SSMA_TimeStamp

END

'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

free_mascot One Orange Chip Points: 27164 More actions · Answer 6

Yes, your error is very correct as there is no column called 'SSMA_TimeStamp' on the table 'doctors'.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

amadrazo2006 Old Hand Points: 389 More actions · Answer 7

Thanks you. Now It's work fine.

I appreciateso much your solution. Now I can resolve the trouble I had have and I can continue with my work.

Greetings,

Arsenio.