Renaming a Column in Ms SQL Server 2005

Renaming a column in MS SQL Server 2005

You have to use a stored proc to rename a column. The following will rename your column from 'oldColumnName' to 'newColumnName' without affecting any data.

EXEC sp_rename 'tableName.[oldColumnName]', 'newColumnName', 'COLUMN'

Obviously you'll have to update any code / stored procs / SQL that uses the old name manually.

How to rename a column in SQL Server 2005

Its a old version of mssql so does not support above solutions but when i tried like this it works

EXEC sys.sp_rename 'fg.TABLE NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME','COLUMN';

anyway thanks for your efforts

How can I rename my column in a SQL table?

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

How can I rename my column in a SQL table?

sp_rename 'TableName.ColumnName', 'NewColumnName', 'COLUMN'

Rename column SQL Server 2008

Use sp_rename

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

See: SQL SERVER – How to Rename a Column Name or Table Name

Documentation: sp_rename (Transact-SQL)

For your case it would be:

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'

Remember to use single quotes to enclose your values.

How to change column order in a table using sql query in sql server 2005?

You cannot. The column order is just a "cosmetic" thing we humans care about - to SQL Server, it's almost always absolutely irrelevant.

What SQL Server Management Studio does in the background when you change column order there is recreating the table from scratch with a new CREATE TABLE command, copying over the data from the old table, and then dropping it.

There is no SQL command to define the column ordering.

SQL, How to change column in SQL table without breaking other dependencies?

For Microsoft SQL Server Redgate have a (not free) product that can help with this refactoring http://www.red-gate.com/products/sql_refactor/index.htm

In the past I have managed to do this quite easily (if primitively) by simply getting a list of things to review

SELECT * FROM sys.objects
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Contacts%' 

(and possibly taking dependencies information into account and filtering by object type)

Scripting all the ones of interest in Management Studio then simply going down the list and reviewing them all and changing the CREATE to ALTER. It should be quite a simple and repetitive change even for 60 possible dependencies. Additionally if you are referring to a non existent column you should get an error message when you run the script to ALTER.

If you use * in your queries or adhoc SQL in your applications obviously things may be a bit more difficult.

What's the best way to rename a column referenced in a computed column?

Given the SSMS runs into exactly the same error, I'm thinking there's not a better way than dropping and recreating the computed column.

sql 2005 force table rename that has dependencies

Find the "enforced dependencies", then remove or disable them.

By "enforced dependencies", it means Schema binding, so you'll have to look specifically for that.

Here's a query to look for schema binding references to your object:

select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = @YourObjectName

As I noted in the comments, there is no way to FORCE-ibly override Schema Binding. When you use Schema Binding, you are explicitly saying "Do not let me or anyone else override this." The only way around Schema Binding is to undo it, and that's intentional.

Problem with Renaming a Column in SQL Server

Never mind I found out:

ALTER TABLE dbo.AllocationDetails
DROP COLUMN [[Conversion_Fee]]]

OR

sp_RENAME 'dbo.AllocationDetails.[[Conversion_Fee]]]' , 'Conversion_Fee', 'COLUMN'

these will work fine. :)

Using Double Quotes:

exec sp_rename 'dbo.AllocationDetails."[Conversion_Fee]"' , 'Conversion_Fee', 'COLUMN' 

will also work.

---

Related Topics