How to add a column with NOT NULL to an existing table? I have tried like: ALTER TABLE MY_TABLE ADD STAGE INT NOT NULL; But it gives the error message like: "ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified" So, can anyone help me? ...
I can not for the life of me figure out what is wrong with this code: IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = 'Report_Date' AND object_id = OBJECT_ID('TempTable3')) ALTER TABLE TempTable3 ADD Report_Date datetime --Set "ReportDate" field to the date the data was extracted less one. UPDATE TempTa...
In my stored procedure, I make a temp_tbl and want to add several columns in a cursor or while loop. All works fine the cursor (the creation of a temp_bl but I can´t add the column when the column string is in a varchar variable. WHILE @@FETCH_STATUS = 0 BEGIN SET @webadressenrow = 'Webadresse_'+CAST(@counter as nchar(10)) A...
I need to rename columns in my MySQL table using PHP. This is made difficult because the syntax is ALTER TABLE [table] CHANGE COLUMN [oldname] [newname] [definition]. The definition is a required parameter. Is there a way to grab the definition and simply feed this back into the SQL statement? Some sample code would be fantastic, tha...
Hello all! I have been trawling google for answers to this but no luck. Any help would be great! I have a SQL table: tblFeedback. It stores answers to feedback questions. The Questions are held in a different table: tblQuestions. The questions on the feedback form can be changed via a user interface, therefore when the user edits the q...
I have ~1000 tables that start with the same prefix : table_prefix_{SOME_ID} (i can take the ids from another table) what is the fast way to loop over all the tables in mysql and do : ALTER TABLE `table_prefix_{some_id}` ADD INDEX `fields` (`field`) ...
We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it alone. Unfortunately, SQLite doesn't support an IF NOT EXISTS clause on ALTER TABLE. Our current workaround is to execute the A...
I'm using MS SQL Server, and I'd like to alter a view from within a stored procedure, by executing something like "alter view VIEWNAME as ([some sql])". A few pages thrown up by google assert that this is not supported directly (and neither are related alter-table statements), but there are also examples of how to work around it using c...
My requirement is that user performing alter CANNOT be sysadmin (it can have all other rights but not sysadmin). I am running a query from local server which should modify a remote one EXEC ('ALTER DATABASE REMOTEDB MODIFY FILEGROUP ftfg_REMOTEDB NAME=ftfg_REMOTEDB') at [REMOTESERVER] This query works once I add sysadmin right to the...
How do I use ALTER TABLE to add a new column and make it unique? ...
In SQL Server, I have a new column on a table: ALTER TABLE t_tableName ADD newColumn NOT NULL This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint. To get around this, I could create the table with the default constraint and then remove it. However, there...
For example: ALTER TABLE webstore.Store MODIFY COLUMN ( ShortName VARCHAR(100), UrlShort VARCHAR(100) ); The above however does not work. I am using MySql 5.x ...
I have a mysql database that runs for some time now with many changes on it. Lately i looked over it and i noticed that in some cases i have doubled index on the same field. Some Indexes are missing, And mainly there is a huge mess in all the indexes. I wants to drop all indexes from a table. Later on i have a prepared script that will ...
I'm using Migrator.NET with a DB2 database and my current migration executes an ALTER TABLE statement, which causes the table to go into the "reorg pending state". This state requires the reorganisation of the table with > REORG TABLE TableName before I can do anything else with it. I tried executing this with Database["DB2"].Execut...
Hi All, I right click on my table in ssms 2008 and select Script Table as / Drop and Create Table to new window and I try to run the script but get an error: Could not drop table because it is referenced by a foreign key constraint What was the point of the Drop and Create generate script then? Thanks, rod. ...