I'm just returning to MS SQL Server after two years of dealing with
Sybase ASE. I need to drop a column, using the alter table command.
I keep getting an error indicating that a constraint is using the
column. Here is the create script for the table
create table mytable
(col1 char(1) not null,
col2 char(1) default 'A' not null
)
Here is the alter table command:
alter table mytable drop column col2
When I run it I get the following error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__mytable__col2__114A936A' is dependent on column
'col2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col2 failed because one or more objects access
this column.
Is there anyway to tell the database to drop all column constraints
when the column is deleted?
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
'Unfortunately', there is still a SQL6.5 sp4 server that I need to handle. I need to drop a coulmn xyz from an important table tblABC. But neither Enterprise manager could do it nor 'alter table tblABC drop column xyz' could run.
Without the hastle of makin a tblABCcopy and copy all data from tblABC except column xyz and re-do all fks; is there another way to drop the column?
I have a question, Every month I get a request from cutomer service to go into a SQL VIEW and DELETE a column to reflect the past Month. How to write a query to create a SQL job to performed this task automatically. For example today I deleted the column [2015/03] which represents the month of March. The columns left are [2015/04] representing the current month, [2015/05], [2015/06],[2015/07]....[2016/08].Next month I will have to delete column [2015/04]and the current month will be [2015/05]....etc.
So I'm thinking I will have to Drop the column and also Maybe using a date funtion to look at the current date and format it to reflect [YYYY/MM] and IF the Column Month is less than current date month then drop the column.
I checked column from xsd file which created from DataBase. And before installing my application I will check any column in destination base whether have complete column or not.
And if there is any column which is not wanted column(not same as xsd structure) I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ......" and Execute it by program initialization.
So I need delete it by run-time
However some column may be Primary Key with any reason
That's why I can't delete them by simple command
I expect to delete them by specific column instead of specific constraint name which is not sure name.
ALTER TABLE tm_spn_equip_def ADD Customer_No int NOT NULL DEFAULT dbo.f_spn_GetCustomerNo() WITH VALUES
Now when I attempt to do this...
ALTER TABLE tm_spn_equip_def DROP COLUMN Customer_No
I get an error:
Msg 5074, Level 16, State 1, Line 1 The object 'DF__tm_spn_eq__Custo__019E3B86' is dependent on column 'Customer_No'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN Customer_No failed because one or more objects access this column.
I have a database that I have made using sql server 2005 express edition. I want to run a query to drop one of the columns from one of the tables. I have tried the following but had no luck.
USE database
DROP column FROM table
This is the error that I got when I tried this.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Of course, my query had the real database, table and column names. I have generic ones that I have provided here.
Do I have to alter the table instead of dropping the column that I don't want? Do I have to remake the table without the column that I don't want?
I am trying to remove the Indentity property from a column so I can do some clean up. Then will need to add it back. Adding Indentity property back is not a problem, but cant figure out how to remove it in the first place.
Is there a way to remove the Identity property of a column in SQL Server 2000? The statement: <code> ALTER TABLE <table name> ALTER COLUMN <column name> DROP IDENTITY </code> returns a syntax error. Thank you, Jeff
I have a huge table in it own tablespace (250 GB). I just deleted more thanhalf of the columns in that table, but I didn't see any change in allocatedspace. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also saysthat CLEANTABLE only reclaims space for columns with text, char datatypes.How can I get space back for integer, decimal, number datatypes? I'm justall out of ideas.
I need to know definitively when I need to drop and recreate column statistics (histogram) when altering a column. Empirically, it seems I can lengthen a varchar or change a not null column to a nullable one, and then existing statistics dont' cause
Msg 5074, Level 16, State 1, Line 1
The statistics 'c1' is dependent on column 'c1'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN c1 failed because one or more objects access this column.
But, if I change a column to not nullable or shorten a column, I get the error.
Is this the complete description of when I need to drop and recreate column statistics and when I don't?
Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.
Is there any way to remove the IDENTITY property of a column? I originally used it, but it caused so many headaches with ADO.NET that I've decided to just increment the darn things manually. So I want to turn it into just a normal integer column.
If there's no direct way, can someone suggest a quick way to create a new column, copy all the old values into the new column, change the primary key to the new column, and drop the old column?
1. We have user defined type (Sql type = bit ) with rule setting it to 0 2. New column(isVIP) was created using this user defined type 3. sp_unbindrule was used to unbind rule from new column (isVIP) 4. Alter table Client drop Column isVIP !!!! (invalid syntax )
I have a SQL Server 2005 DB in which a design change requires me to drop a column.Is there a way of identifying all references in the DB including functions & stored procs's that reference this column? I understand there are 3rd party products that can do this but was wondering if there was something baked into SQL Server itself.
How can I pervert dropping the column in the table (probably some process doing that and I want to find it) ? I need to be able to modify but not alter / drop column .
I have a table with a identity column in sql server 7 database. Now i need to update this identity column. Directly i couldn't able to update this column since it is an identity column. So, i like to drop this identity nature first and then update it is easy to update it. For this purpose, I need a Transact-sql script. Please Let me know if you have any thoughts on this. Thanks.
My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)
- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A). - Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records. - We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.
Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
