SQL Function used to alter an existing table object.

learn more… | top users | synonyms

0
votes
0answers
5 views

PostgreSQL: Which connections is blocking ALTER TABLE?

I would like to add some fields to a table in all testing and production DBs, all of which are stored in PostgreSQL 9.3 on Amazon RDS. ALTER TABLE users DROP COLUMN IF EXISTS device_language; ALTER ...
0
votes
2answers
15 views

How to delete a column named grave accent in MySQL

As title. Every time when i try to execute the query: ALTER TABLE tablename DROP COLUMN ```, got syntax error, which is understandable. So, what should i do to delete this column whose name is the ...
-1
votes
1answer
33 views

Does changing the collation of a column change TIMESTAMP (rowversion) values?

I need to run ALTER TABLE ... ALTER COLUMN ... COLLATE Czech_CI_AS ... for multiple columns in a database. All those tables have TIMESTAMP column which is used for database synchronization. Will ...
0
votes
1answer
37 views

Apparantly errorneous Exceeds 8K row length message

In attempting to alter an SQL Server table adding DATE and FLOAT columns. I get a message: Warning: The table "CustomerTransactions" has been created, but its maximum row size exceeds the allowed ...
0
votes
1answer
25 views

Altering a table to add a New Column vs Create table with New Column

I have Table A which i'm tasked to add a new column is_fully_complete bit(1), and i'm thinking: Would it be better to a.) Create a new table Table_A_new like Table_A and add the new column there ...
2
votes
2answers
248 views

Changing data type from mediumint to int without downtime

My current table is using MEDIUMINT for ID column ‘primary key auto increment’. The current table records soon will reach the maximum value for mediumint which is 8388607. That being the case, I need ...
1
vote
0answers
41 views

SQL Server : ALTER TABLE tablename SWITCH PARTITION 2 to tempempty

Have a sliding partition where I need to remove the trailing partition when they expire. I create an empty table that matches the existing table. When I create the empty table, I specify the file ...
2
votes
1answer
60 views

Auto_increment value needs to be set as S1

Hi I need to set auto increment for a table as S1. I tried by altering the table and also by creating by new table I am aware how to set auto_increment but when i try to set auto increment as S1 i am ...
3
votes
3answers
106 views

Adding non-nullable NEWSEQUENTIALID() column to existing table

In SQL Server 2008, I am adding a non-nullable column to an existing table, and want to make the default NEWSEQUENTIALID(). ALTER TABLE MyTable ADD MyColumn UNIQUEIDENTIFIER NULL CONSTRAINT ...
1
vote
1answer
45 views

Why does adding a column to a MySQL table take incredibly long?

I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with ...
0
votes
2answers
45 views

Alter auto-increment of table with 16 million rows

Learned this the hard way. Need to alter a table's auto-increment with 16 million rows. Now, it's taking quite sometime. Any estimate how long it would take? Sorry.
0
votes
1answer
36 views

How to add a column in table via stored procedure? [closed]

create or replace procedure SP_PARAMETER_TEST -- this is my table ( NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum" ) is begin ALTER TABLE PARAMETER_TEST ADD ...
1
vote
1answer
99 views

MySQLSyntaxErrorException on ALTER TABLE … CONVERT TO … in MySQL Connector/J

Running Ubuntu 14.04 with mysql-connector-java-5.1.28 installed. Let's create a table: CREATE TABLE mytable (value1 VARCHAR(32000), value2 TEXT, value3 VARCHAR(1000), value4 VARCHAR(32000)) ...
0
votes
1answer
32 views

Should I add it as an additional column or a seperate table?

I have a table in my SQL Server thats called "Regions", it has information about 9 regions and the 10th row holds the info about our Division. Now that we will be merging another 2 divisions with its ...
1
vote
1answer
44 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
0
votes
1answer
36 views

MySQL 'copying to tmp table' while enlarging VARCHAR

One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table. I watched PROCESSLIST and saw that ALTER TABLE ...
1
vote
1answer
79 views

Add NOT NULL constraint to large table without table scan

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table ...
2
votes
3answers
516 views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
0
votes
2answers
293 views

Moving a table to a different tablespace in Oracle using Online Redefinition Vs Alter table move tablespace

I have a table with 1 Billion records with size 300GB to move to different tablespace. My question here is - What is the advantage of using DBMS_REDEFINITION (Online redefinition) package over Alter ...
3
votes
2answers
257 views

Altering Multiple Tables

I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to ...
0
votes
1answer
118 views

Best way to add a new column to a large table? (MySQL - MyISAM)

Here's more info on the table that needs to be altered: 15GB size MyISAM table with Index around 5GB Over 100 million rows MySQL 5.1 on RHEL 5 with 4GB RAM Currently live online What's the best ...
0
votes
2answers
60 views

Table size reduce with help changing collation charset [MySQL]

I have table which is big enough, phpmyadmin shows that it takes more than 40Gb space. I can't remove data from it, but I've figure out one thing: there is one field which is described as ...
3
votes
1answer
148 views

Online Schema migration in MySQL : When to start?

I'm the tech lead of a Social Media SAAS, where we're currently experiencing a big increase in data amount we're storing in our mySQL-database. We're having a single MySQL db, and besides many ...
1
vote
1answer
238 views

PostgreSQL - how to rename CamelCase column?

When I tried to rename a CamelCase column like this: ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column; I get the following error: ERROR: column "camelcasecolumn" does not ...
2
votes
1answer
64 views

How safe is it to widen a column in MySQL?

Apologies for the simple question but SQL isn't really my forte, and I want to be sure I don't shoot myself in the foot. A MySQL database I volunteered to help maintain has a table that looks like ...
3
votes
1answer
394 views

When changing the size of a nvarchar column, do I need to drop the unique index? And will the table get locked when recreating the index?

In our database a big table exists that more or less looks like this: CREATE TABLE dbo.production_data ( pd_id BIGINT PRIMARY KEY, serial NVARCHAR(16) NOT NULL UNIQUE, ... ); but now ...
0
votes
0answers
105 views

Is it possible to auto generate ALTER scripts for differences between existing DB and updated schema?

I built a internal web application for a client that manages applications to programmes for their employees. The system is Classic ASP on a set of clustered and load balanced Windows 2000 Servers ...
1
vote
4answers
105 views

Amount of data in a database table

I am trying to run an alter query on a table, and its giving me a Lost connection to MySQL server during query MySQL server has gone away. The query that I am running is alter table table_name ...
0
votes
1answer
122 views

Should I use begin tran to do an alter table column that increases the column length?

I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72: ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL The column is not null ...
1
vote
1answer
167 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
0
votes
2answers
941 views

How long will it takes to rename table

How long will it takes to rename table with a size of 219 GB? This is the specification of our server: Memory: 65 GB Available disk space: 266 GB mysql Ver 14.12 Distrib 5.0.77 Sorry for the ...
11
votes
2answers
871 views

Fastest way to change a 600GB table indexed key datatype from INT to BIGINT

I need to change a datatype from INT to BIGINT in a 600GB MySQL table. The column has a unique index. I might be good with unsigned INT, but I assume changing to that or BIGINT will be pretty much ...
2
votes
2answers
467 views

Huge disk space usage increase when altering a column

I hope this question is easy for someone to answer :) I have a table that looks like this (unimportant columns hidden) CREATE TABLE [dbo].[Log]( [LogID] [int] IDENTITY(1,1) NOT NULL, ...
0
votes
1answer
174 views

use function arguments or variable in postgres function

I want to set the default value for the column building and floor in my table points in the below function called test(b,f) Create or replace function test(b text, f text) returns void as $$ Begin ...
0
votes
1answer
77 views

Altering a parent table in Postgresql 8.4 breaks child table defaults

The problem: In Postgresql, if table temp_person_two inherits fromtemp_person, default column values on the child table are ignored if the parent table is altered. How to replicate: First, create ...
4
votes
4answers
936 views

Alter Table for Multiple Databases (same table name)

Is there a good way to Alter a table (add a column) for multiple databases (think like 50 or so). Or is it pretty much manual for all of them? the Tables are all the same and the column added will be ...
-3
votes
1answer
42 views

Will column be overwritten when add column is executed?

ALTER TABLE {abc_in} ADD COLUMN abcd_nid int(10) unsigned NOT NULL default '0' AFTER abcd_in What if column abcd_nid already exists ? Will it be overwritten ? how can I insert new values into column ...
3
votes
3answers
3k views

Quickly change NULL column to NOT NULL

I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I ...
1
vote
3answers
2k views

Convert all ntext columns to nvarchar(max)

I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max). This is the code ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar] AS BEGIN SET NOCOUNT ...
1
vote
1answer
971 views

Removing foreign key when it is not named

I've created a table named 'student' as : create table student( stud_id number primary key, stud_name varchar2(30) not null, branch_id varchar2(4) references branch(branch_id), course_id ...
1
vote
2answers
805 views

Running out of Transaction Log space during Alter Table

I'm running an alter table, alter column on a table with nearly 30 million rows and SQL Azure fails after approximately 18 minutes saying that The session has been terminated because of excessive ...
2
votes
1answer
268 views

Alter mysql database engine doesn't work from cluster to other

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked. It did not throw any ...
1
vote
1answer
797 views

How to change a column type from SET to an ENUM

I have a table with a SET column type that need to be changed to ENUM type. Can I do it with a simple ALTER or will this mess up my data? NB: there are no multiple values (a,b,c) in the table.
0
votes
0answers
423 views

H2 ALTER TABLE ADD COLUMN BEFORE/AFTER

According to H2 syntax documentation available here I should be able to execute a query like: ALTER TABLE EMPLOYEE ADD COLUMN SECOND_NAME VARCHAR (124) AFTER FIRST_NAME; However apparently only ...
2
votes
2answers
2k views

Reset IDENTITY value

I have a table with an IDENTITY column. While developing I delete the rows from time to time and add them again. But the IDENTITY values always kept increasing and didn't start from 1 when I added ...
0
votes
1answer
233 views

H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

I would like to check if a column has a NOT NULL constraint in order to start an automated upgrade of the table definition. However in H2 the SQL SELECT NULLABLE FROM TABLE_NAME doesn't return me the ...
2
votes
1answer
846 views

Can a view affect ALTER TABLE commands?

I provide a database system to a number of customers. The database is installed locally on Oracle. We periodically upgrade the database structure (either adding new fields, renaming old fields, ...
1
vote
3answers
5k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
1
vote
3answers
246 views

Altering same table across multiple databases

I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases. I am not sure I know what the right procedure for this ...
1
vote
2answers
438 views

Modify all tables in a database with a single command

Is there a single or a one line command to modify all tables within a database. I would like to issue this command in every table within a database: ALTER TABLE `table_name` CONVERT TO CHARACTER SET ...