Deleting the columns from the same database through the script

Question

Deleting the columns from the same database through the script

kunaal desai

Mr or Mrs. 500

Points: 506
More actions
April 5, 2010 at 5:00 pm

#213480

Hey guys,
I want to write a script that actually removes a particular column from ALL THE TABLES IN THE DATABASE. Is there any way to do it with SQL Server?

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

hi there, here is the code u asked for.. check the output of this from that print statement.. if that matches your requirement, uncomment the exec part and your requirement is done

DECLARE @Query VARCHAR(MAX)

DECLARE @COLUMN_TO_DELETE VARCHAR(128)

SET @COLUMN_TO_DELETE = 'YOUR_COLUMN_NAME'

SET @Query = ''

SELECT @Query = @Query + ' ALTER TABLE ' + TABLE_NAME + ' DROP COLUMN ' + @COLUMN_TO_DELETE + CHAR(10) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

PRINT @Query

-- EXEC (@Query )

Please inform us if that helps you.

Cheers,

C'est Pras!!

P.S : i am writing this code from my home where i have no access to SQL Server. once i reach office i will check the validity of that query.

kunaal desai Mr or Mrs. 500 Points: 506 More actions · Answer 2

Hi,

Thank you very much for your reply. I do not have access to SSMS right now but I will surely check that tomorrow morning. But I had one question: would this code work on tables with foreign key constraint?

crazy4sql SSCoach Points: 19590 More actions · Answer 3

You will get error for those tables which are having PK reference.. .rest will be removed.

----------
Ashish

kunaal desai Mr or Mrs. 500 Points: 506 More actions · Answer 4

Hmmmmmm. can I write a script a script to remove Pk constraint from all th etables in a single database, if the PK column is the same all throuugh the database and coming from single reference table?

Garadin One Orange Chip Points: 29613 More actions · Answer 5

kunaal desai (4/6/2010)
Hmmmmmm. can I write a script a script to remove Pk constraint from all th etables in a single database, if the PK column is the same all throuugh the database and coming from single reference table?

SELECT 'EXEC ( ''ALTER TABLE [' + OBJECT_NAME(i.OBJECT_ID) + '] DROP CONSTRAINT [' + i.name + ']'')'

FROM sys.indexes I

WHERE I.is_primary_key = 1

Note: This query will give you the code to execute to drop all your PK's. Note that if they are referenced as FK's, you've gotta drop those first.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

kunaal desai Mr or Mrs. 500 Points: 506 More actions · Answer 6

I am sorry for the late reply guys. thank you very much Ashish ans garadin. That method worked. :).

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

ColdCoffee

SSC-Dedicated

Points: 39972

April 8, 2010 at 12:00 pm

#1148528

Mate, did u test my code? din't it work for you?

kunaal desai Mr or Mrs. 500 Points: 506 More actions · Answer 8

I am sorry cold coffee actually I used your code. i jumbled with the names. I thought it was Garadin who posted the previous post. I am really really sorry for that.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 9

Just out of curiosity ... does the business requirements really ask to drop a column that is part of a PK? really? :blink:

On the same line of thinking... why if the affected column is part of an index?

Is this automatic process going to trash referential integrity and indexing strategy in a single batch?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

kunaal desai Mr or Mrs. 500 Points: 506 More actions · Answer 10

Actually this is a complex problem. Actually we are using Dot Net Nuke framework here and the DNN( dotnet Nuke) actually comes with its own set of database tables. Now, we had added replication in the tables which led to each table having a replication field in the table. Now, this replication field's values are coming from the replication table which has all the replication keys. So that actually imposes Foreign key constraint on each of the table.

Now adding complexity to the schema, DNN came up with the upgrade which actualy upgrades all the existing tables. Because of us having replication field inside, we can not do that. So we had to drop all the replication fields from all the table.

Now actually my orginal question should had been phrased well. i wanted to get rid with foreign key constraint not primary key constraint. Still that breaks the integrity for a while until we go ahead and add the replication field again in the upgraded database.

so yeah a lot of things going on ):.