SQL Function used to alter an existing table object.

learn more… | top users | synonyms

3
votes
2answers
567 views

Alternate ways to increase column size

I have a database that is in production, and in some instances will have millions of records. It has been requested that we increase the sizes of several columns, which is for the most part pretty ...
2
votes
2answers
4k views

SQL Server : how to drop all constraints on a table in T-SQL

I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables. Is there any way to do that with T-SQL? I cannot find any solution ...
8
votes
1answer
2k views

How do I swap tables in MySQL?

Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries. That's why I want to compute more recent statistics in foo_new ...
3
votes
1answer
2k views

Why does altering a table and adding a foreign key create an extra constaint?

Edit: It was a default constraint as a result of setting a default value when adding the column. The name gave it away when starting with 'DF__'. Anyway, solved it by adding the column and manually ...
0
votes
2answers
892 views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
5
votes
4answers
11k views

Add a new Column and define its position in a table

I have Table A with 5 columns: TableA -- Name Tel Email Address I want to add a new column (mobile) in between Tel & Email: TableA -- Name Tel Mobile Email Address If I use ALTER TABLE ...
2
votes
1answer
901 views

Default values in SQLite3

When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify ...
1
vote
1answer
84 views

how to use mysql temporal cache tables

I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table. Create table ...
5
votes
5answers
2k views

Altering table schema takes too much time

I am using PostgreSQL database. I have a table with millions of rows. I have a varchar column with 2000 size and i want to make it to 4000. I ran the alter table command, but it is taking too much ...
4
votes
1answer
2k views

I need to run VACUUM FULL with no available disk space

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is ...
1
vote
1answer
678 views

Changing collation type database-wide in MySQL

How do I retroactively update the collation type database wide without dropping and recreating it? I accidentally created a database without specifying UTF8 as the default collation type. I then went ...
2
votes
1answer
965 views

Rotate a table in PostgreSQL

Problem description I need to rotate (with DROP and CREATE) a table which is heavily used by other clients. At present, I have a program which replaces (DROP + CREATE) this table. Sometimes, just ...
2
votes
2answers
1k views

Will an ALTER TABLE ADD COLUMN on a slave break replication?

I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
2
votes
1answer
385 views

Add columns Query With parameters

Hello all I'm having 1 Question with following query syntax ALTER TABLE table_name ADD column_name column-definition; Question is can we use parameters to column_name field like @column_name? ...
3
votes
1answer
747 views

Preventing replication of alter table command to change engine

I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a ...
1
vote
2answers
231 views

How to recover previous values in Mysql Table?

I have MySQL Innodb table of 14M rows. There was a coloumn called is_disabled which had different values. By mistake I ran the query without the where clause: update url_queue set is_disabled=1 ...
4
votes
2answers
9k views

Why does simple ALTER TABLE command take so long on table with full-text index?

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue). My question is: If I try to run the following sql command: ALTER TABLE VisitorData ...
0
votes
1answer
149 views

Columns are added in wrong order

I have a encountered strange behaviour adding columns to an existing table. I run those scripts using osql several instances. Let Table a_table have initially three columns A_col, B_col and C_col. ...
3
votes
3answers
6k views

MySQL - fastest way to ALTER TABLE for InnoDB

I have an InnoDB table that I want to alter. The table has ~80M rows, and quit a few indices. I want to change the name of one of the columns and add a few more indices. What is the fastest way to ...
1
vote
1answer
497 views

Can't run Alter Table command due to locks

I'm unable to drop, delete or alter a database in my Local SQL Server instance. I have run sp_Who2 and there don't appear to be any processes using the database apart from a process which gets ...
3
votes
1answer
2k views

Alter table modify column on a huge table containing more than 25 million records

I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts What is the most efficient way to alter a column ...
3
votes
1answer
2k views

Can I rename the values in a MySQL ENUM column in one query?

Suppose I have a Database table with an ENUM('value_one','value_two'). I want to change that to an ENUM('First value','Second value'). I an currently doing that as follows: ALTER TABLE `table` MODIFY ...
3
votes
5answers
852 views

Modifying table structure within a transaction?

In Oracle (and probably elsewhere), executing an ALTER TABLE statement will do an implicit commit on the current transaction. We have a tool (written in Java) that should modify a schema by: adding ...
4
votes
3answers
5k views

How to change the datatype of a column from integer to money?

I am attempting to convert a PostgreSQL table column from integer to money, but I am receiving the error: cannot cast type MyColumn to money I have tried these two statements, but just haven't ...
4
votes
2answers
827 views

Preordering the table by a specified column

I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering. Is it possible to order the table ...
17
votes
3answers
4k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
5
votes
3answers
5k views

ALTER TABLE on a large table with an indexed column

I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
1
vote
1answer
885 views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type ...