mess delete common column in all tables

mess delete common column in all tables

Post by pei » Sat, 01 Dec 2001 07:09:21



Hi,

I have a common column exists in all my tables and now I don't need this
column any more. Is there a way to mess delete the column in all tables,
instead of openning each individual table and delete it. I have about 500
tables.

Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by oj » Sat, 01 Dec 2001 07:11:52


pei,

short answer, no.

a delete statement can only affect one table per instruction. so, you would
need to cursor through all of your tables and delete from one by one.

by the way, if you don't need the data in the column and you no longer use
it in your query, why not just leave it there. other than taking *extra*
space there is really no harm.

if the column is truly no longer needed, you ought to just remove/drop it.

-oj


Quote:> Hi,

> I have a common column exists in all my tables and now I don't need this
> column any more. Is there a way to mess delete the column in all tables,
> instead of openning each individual table and delete it. I have about 500
> tables.

> Thanks for the advise.


 
 
 

mess delete common column in all tables

Post by Tae Le » Sat, 01 Dec 2001 07:32:31


Actually there is.

You can use alter table statement to drop fields from the table.  This means
that you have to know which tables that you would like to drop this field
from.  You can either query master database to get the list of tables or
type it up in separate table.  Then you can use ALTER TABLE statement in a
loop.  This is just for one temp table that I created.

CREATE TABLE [#tbl1] (
nID INT,
szName VARCHAR(50)
) ON [PRIMARY]
GO
SELECT * FROM #tbl1
ALTER TABLE #tbl1 DROP COLUMN nID
GO

SELECT * FROM #tbl1
DROP TABLE #tbl1

If you have any questions, let me know.

-Tae Lee


> pei,

> short answer, no.

> a delete statement can only affect one table per instruction. so, you
would
> need to cursor through all of your tables and delete from one by one.

> by the way, if you don't need the data in the column and you no longer use
> it in your query, why not just leave it there. other than taking *extra*
> space there is really no harm.

> if the column is truly no longer needed, you ought to just remove/drop it.

> -oj



> > Hi,

> > I have a common column exists in all my tables and now I don't need this
> > column any more. Is there a way to mess delete the column in all tables,
> > instead of openning each individual table and delete it. I have about
500
> > tables.

> > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by pei » Sat, 01 Dec 2001 07:34:43


Thanks for the quick response. Actually I got that column 'rowid' when I set
my relication. I regret of doing it. The replication I set up didn't work so
I deleted the repication already. But that column still exsits in every
table. I know I can delete it from changing the property of the publisher
but too late. Do you think I can be saved in some other way, such as
recreating a same replication database? Thanks.


Quote:> Hi,

> I have a common column exists in all my tables and now I don't need this
> column any more. Is there a way to mess delete the column in all tables,
> instead of openning each individual table and delete it. I have about 500
> tables.

> Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by pei » Sat, 01 Dec 2001 07:35:37


Thanks for the quick response. Actually I got that column 'rowid' when I set
my relication. I regret of doing it. The replication I set up didn't work so
I deleted the repication already. But that column still exsits in every
table. I know I can delete it from changing the property of the publisher
but too late. Do you think I can be saved in some other way, such as
recreating a same replication database? Thanks.


> pei,

> short answer, no.

> a delete statement can only affect one table per instruction. so, you
would
> need to cursor through all of your tables and delete from one by one.

> by the way, if you don't need the data in the column and you no longer use
> it in your query, why not just leave it there. other than taking *extra*
> space there is really no harm.

> if the column is truly no longer needed, you ought to just remove/drop it.

> -oj



> > Hi,

> > I have a common column exists in all my tables and now I don't need this
> > column any more. Is there a way to mess delete the column in all tables,
> > instead of openning each individual table and delete it. I have about
500
> > tables.

> > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by Zachary Well » Sat, 01 Dec 2001 07:25:57


Hmm, here's something to play with:

select 'alter table ' + rtrim(name) + ' drop column ColumnName'
    from sysobjects
    where type = 'U'
    order by name

This will give you a list of all 500 tables (along with some others). Just
scan through the output, remove any tables referenced that don't have the
column in question, then copy and paste it back into the QA.

Zach


Quote:> Hi,

> I have a common column exists in all my tables and now I don't need this
> column any more. Is there a way to mess delete the column in all tables,
> instead of openning each individual table and delete it. I have about 500
> tables.

> Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by Joe Celk » Sat, 01 Dec 2001 07:39:11


Make a script from the system information tables and write a ton of ALTER TABLE ..DROP COLUMN statements.  Oh, watch out for the VIEWs that used these columns.  

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, datatypes, etc. in your schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

mess delete common column in all tables

Post by Steve Kas » Sat, 01 Dec 2001 07:39:18


Pei,

  Do you want to DROP COLUMN on all these tables?  There is no
SQL statement that "deletes a column." (DELETE deletes rows).  If
you want to DROP COLUMN on all of them, here is a script that
will create all the statements to do that - you can review them to be
sure that your column doesn't appear in a table where it should be
left, then copy what you need of the output up to the query analyzer
window and run it.

If there are constraints on the column you are deleting, it may not
go so smoothly - and I would be very cautious making widespread
changes to hundreds of tables in any case.

By the way, if you want to do something else with the tables, like
run

UPDATE T SET doomedColumn = NULL

on all of them, you can just change the definition of sql in my
code.  I could have had this script actually drop all the columns,
by using exec() instead of print, but I don't want to be responsible
for that suggestion!

SET NOCOUNT ON


--Put your column name here

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME

INTO DropColQrys
FROM INFORMATION_SCHEMA.TABLES
JOIN INFORMATION_SCHEMA.COLUMNS
ON TABLE_TYPE = 'BASE TABLE'

AND INFORMATION_SCHEMA.TABLES.TABLE_NAME
  = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME


DECLARE C CURSOR FOR
SELECT sql FROM DropColQrys

OPEN C





END

CLOSE C
DEALLOCATE C

DROP TABLE DropColQrys

Steve Kass
Drew University


> pei,

> short answer, no.

> a delete statement can only affect one table per instruction. so, you would
> need to cursor through all of your tables and delete from one by one.

> by the way, if you don't need the data in the column and you no longer use
> it in your query, why not just leave it there. other than taking *extra*
> space there is really no harm.

> if the column is truly no longer needed, you ought to just remove/drop it.

> -oj



> > Hi,

> > I have a common column exists in all my tables and now I don't need this
> > column any more. Is there a way to mess delete the column in all tables,
> > instead of openning each individual table and delete it. I have about 500
> > tables.

> > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by oj » Sat, 01 Dec 2001 07:44:46


hehehhe...

life would be easier if you just hit information_schema.columns
view...stealing your query...

select 'alter table [' + rtrim(table_name) + '] drop column [' + Column_Name
+ ']'
    from information_schema.columns
    where column_name='RowID'
    order by table_name

-oj


> Hmm, here's something to play with:

> select 'alter table ' + rtrim(name) + ' drop column ColumnName'
>     from sysobjects
>     where type = 'U'
>     order by name

> This will give you a list of all 500 tables (along with some others). Just
> scan through the output, remove any tables referenced that don't have the
> column in question, then copy and paste it back into the QA.

> Zach



> > Hi,

> > I have a common column exists in all my tables and now I don't need this
> > column any more. Is there a way to mess delete the column in all tables,
> > instead of openning each individual table and delete it. I have about
500
> > tables.

> > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by Dinesh T » Sat, 01 Dec 2001 07:50:19


Pei,

Run the below query and then copy the sql generated, from the results pane
to the query pane, and execute it.

select 'alter table ' +ist.TABLE_NAME+' drop column
columnname'+char(13)+'GO'
from INFORMATION_SCHEMA.TABLES ist
where ist.TABLE_TYPE='base table'

After that make sure that you run DBCC CLEANTABLE.

Dinesh.


Quote:> Hi,

> I have a common column exists in all my tables and now I don't need this
> column any more. Is there a way to mess delete the column in all tables,
> instead of openning each individual table and delete it. I have about 500
> tables.

> Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by oj » Sat, 01 Dec 2001 07:47:54


eek, eek...cursor! :)

-oj


> Pei,

>   Do you want to DROP COLUMN on all these tables?  There is no
> SQL statement that "deletes a column." (DELETE deletes rows).  If
> you want to DROP COLUMN on all of them, here is a script that
> will create all the statements to do that - you can review them to be
> sure that your column doesn't appear in a table where it should be
> left, then copy what you need of the output up to the query analyzer
> window and run it.

> If there are constraints on the column you are deleting, it may not
> go so smoothly - and I would be very cautious making widespread
> changes to hundreds of tables in any case.

> By the way, if you want to do something else with the tables, like
> run

> UPDATE T SET doomedColumn = NULL

> on all of them, you can just change the definition of sql in my
> code.  I could have had this script actually drop all the columns,
> by using exec() instead of print, but I don't want to be responsible
> for that suggestion!

> SET NOCOUNT ON


> --Put your column name here

> SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME

> INTO DropColQrys
> FROM INFORMATION_SCHEMA.TABLES
> JOIN INFORMATION_SCHEMA.COLUMNS
> ON TABLE_TYPE = 'BASE TABLE'

> AND INFORMATION_SCHEMA.TABLES.TABLE_NAME
>   = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME


> DECLARE C CURSOR FOR
> SELECT sql FROM DropColQrys

> OPEN C





> END

> CLOSE C
> DEALLOCATE C

> DROP TABLE DropColQrys

> Steve Kass
> Drew University


> > pei,

> > short answer, no.

> > a delete statement can only affect one table per instruction. so, you
would
> > need to cursor through all of your tables and delete from one by one.

> > by the way, if you don't need the data in the column and you no longer
use
> > it in your query, why not just leave it there. other than taking *extra*
> > space there is really no harm.

> > if the column is truly no longer needed, you ought to just remove/drop
it.

> > -oj



> > > Hi,

> > > I have a common column exists in all my tables and now I don't need
this
> > > column any more. Is there a way to mess delete the column in all
tables,
> > > instead of openning each individual table and delete it. I have about
500
> > > tables.

> > > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by Steve Kas » Sat, 01 Dec 2001 07:48:51


That gives you views as well as tables, I think.

Steve


> hehehhe...

> life would be easier if you just hit information_schema.columns
> view...stealing your query...

> select 'alter table [' + rtrim(table_name) + '] drop column [' + Column_Name
> + ']'
>     from information_schema.columns
>     where column_name='RowID'
>     order by table_name

> -oj



> > Hmm, here's something to play with:

> > select 'alter table ' + rtrim(name) + ' drop column ColumnName'
> >     from sysobjects
> >     where type = 'U'
> >     order by name

> > This will give you a list of all 500 tables (along with some others). Just
> > scan through the output, remove any tables referenced that don't have the
> > column in question, then copy and paste it back into the QA.

> > Zach



> > > Hi,

> > > I have a common column exists in all my tables and now I don't need this
> > > column any more. Is there a way to mess delete the column in all tables,
> > > instead of openning each individual table and delete it. I have about
> 500
> > > tables.

> > > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by oj » Sat, 01 Dec 2001 07:53:00


you're probably right... don't have sqlserver near by to verify...but this
will prove that mass dropping a column can be detrimental...<evil grin>

beside views, stored procedures, triggers can be affected.

-oj


> That gives you views as well as tables, I think.

> Steve


> > hehehhe...

> > life would be easier if you just hit information_schema.columns
> > view...stealing your query...

> > select 'alter table [' + rtrim(table_name) + '] drop column [' +
Column_Name
> > + ']'
> >     from information_schema.columns
> >     where column_name='RowID'
> >     order by table_name

> > -oj



> > > Hmm, here's something to play with:

> > > select 'alter table ' + rtrim(name) + ' drop column ColumnName'
> > >     from sysobjects
> > >     where type = 'U'
> > >     order by name

> > > This will give you a list of all 500 tables (along with some others).
Just
> > > scan through the output, remove any tables referenced that don't have
the
> > > column in question, then copy and paste it back into the QA.

> > > Zach



> > > > Hi,

> > > > I have a common column exists in all my tables and now I don't need
this
> > > > column any more. Is there a way to mess delete the column in all
tables,
> > > > instead of openning each individual table and delete it. I have
about
> > 500
> > > > tables.

> > > > Thanks for the advise.

 
 
 

mess delete common column in all tables

Post by Steve Kas » Sat, 01 Dec 2001 08:15:59


You know, I just realized there's no need for a cursor - I modified
an old query that had to exec(), not print.

eek  is right...  do you realize all the cursor does here is print each
row of a table!  How embarrassing.

Steve


> eek, eek...cursor! :)

> -oj



> > Pei,

> >   Do you want to DROP COLUMN on all these tables?  There is no
> > SQL statement that "deletes a column." (DELETE deletes rows).  If
> > you want to DROP COLUMN on all of them, here is a script that
> > will create all the statements to do that - you can review them to be
> > sure that your column doesn't appear in a table where it should be
> > left, then copy what you need of the output up to the query analyzer
> > window and run it.

> > If there are constraints on the column you are deleting, it may not
> > go so smoothly - and I would be very cautious making widespread
> > changes to hundreds of tables in any case.

> > By the way, if you want to do something else with the tables, like
> > run

> > UPDATE T SET doomedColumn = NULL

> > on all of them, you can just change the definition of sql in my
> > code.  I could have had this script actually drop all the columns,
> > by using exec() instead of print, but I don't want to be responsible
> > for that suggestion!

> > SET NOCOUNT ON


> > --Put your column name here

> > SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.TABLES.TABLE_NAME

> > INTO DropColQrys
> > FROM INFORMATION_SCHEMA.TABLES
> > JOIN INFORMATION_SCHEMA.COLUMNS
> > ON TABLE_TYPE = 'BASE TABLE'

> > AND INFORMATION_SCHEMA.TABLES.TABLE_NAME
> >   = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME


> > DECLARE C CURSOR FOR
> > SELECT sql FROM DropColQrys

> > OPEN C





> > END

> > CLOSE C
> > DEALLOCATE C

> > DROP TABLE DropColQrys

> > Steve Kass
> > Drew University


> > > pei,

> > > short answer, no.

> > > a delete statement can only affect one table per instruction. so, you
> would
> > > need to cursor through all of your tables and delete from one by one.

> > > by the way, if you don't need the data in the column and you no longer
> use
> > > it in your query, why not just leave it there. other than taking *extra*
> > > space there is really no harm.

> > > if the column is truly no longer needed, you ought to just remove/drop
> it.

> > > -oj



> > > > Hi,

> > > > I have a common column exists in all my tables and now I don't need
> this
> > > > column any more. Is there a way to mess delete the column in all
> tables,
> > > > instead of openning each individual table and delete it. I have about
> 500
> > > > tables.

> > > > Thanks for the advise.

 
 
 

1. Does dropping a column from a table mess up foreign

If you've dumped and restored with 7.0's pg_dump (I'm not sure when it was
fixed, may have been in 7.1.2) there was a problem with the dumped trigger
statements which caused the relationship that tells when to drop the
triggers for fk to not exist after the restore.  Theoretically, your
constraints should have gone away at the drop table accounts;.  I.e.,
even in the best case, the above will not preserve foreign key constraints
pointing to the changed table, you'd need to use alter table to re-add
the constraints.

When do you get the relation ACCOUNTS doesn't exist message?  When you try
to do an insert/update?

I'd suggest starting by looking pg_trigger and dropping the constraint
triggers (warning, you need to double quote the constraint name, the
case is significant) that reference accounts and use alter table add
constraint to add the constraints back.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

2. HELP : ERROR 706 and 5702

3. Dropping column silently kills multi-coumn index (was [ODBC] Error when accessing tables with deleted columns)

4. PHP and Informix

5. ODBC and DB2 via DDCS/2

6. instead of delete trigger delete data from table using execute and temporary table for deleted

7. SQL update query on an access db in VB

8. OOPS...I messed up and expanded tempdb on to a device that I need to delete

9. Copying tables - changing table messes up Transformations

10. Creating view from OLEDB source messed up column order

11. err mess: view definition includes no output columns

12. Color of column headers is messed up!