May 12, 2010 at 8:24 am
Hi all,
May be a simple question for you,
I have a table with 150 columns in it. I need to drop 50 columns out of it. what is the fastest way of doing this?
I have another table where I have names for all those column I need to keep.
May 12, 2010 at 8:36 am
Hello,
You can try the following script
select 'alter table A drop column ' + name from sys.columns where object_id = object_id(N'Customer')
But this creates drop column command for each column, so you have to remove undesired ones.
I hope that helps.
May 12, 2010 at 8:47 am
Yes this will help, ... I'll have to apply few more filters to restrict the result to desired columns and will be done... Thanks...
May 12, 2010 at 8:53 am
Being that you have a table with columns to keep, you can try this:
CREATE TABLE #Keep ( NAME sysname ) -- Create the columns to KEEP
INSERT INTO #Keep ( NAME )
VALUES ( '<ColumnToKeep>' )
SELECT 'ALTER TABLE <YourTableToRemove> DROP COLUMN ' + C.NAME
FROM sys.columns AS C
LEFT OUTER JOIN #Keep ON C.name = #Keep.NAME
WHERE Object_id = OBJECT_ID('<YourTableToRemoveFrom>')
AND #Keep.NAME IS NULL
Note, you do not need to create the #keep table, you just need to replace the keep table with your table. TEST TEST TEST
-- Cory
May 12, 2010 at 9:00 am
sure, this is a good way too... Thanks both for your quick help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply