ALTERing Column Names 4

[powahusr]

Through Query Analyzer, I would like to change the name of a column, but would also like to preserve the data in each record for that column. How can I do this without having to entirely drop the column? Is there a way to do it in an ALTER statement?

Thanks in Advance!!!

 

[Niv3k]

I'm not sure if you can rename a column using ALTER, but couldn't you create an updatable view using an Alias?

Example:
SELECT F_Name AS First, L_Name AS Last FROM Names

Kevin

 

[powahusr]

Thanks for the reply Niv3k.

That would work, but it would require a little bit more maintenance, especially if I do eventually change the column name through the GUI, then I would have to go back and change the SELECT statement in each page I had to compensate for the change.

I'm really just looking for the quickest fix to the problem itself with out having to go into Enterprise Manager and wait forever just to connect to the Database on my web host to fix the problem through the GUI. Connecting to the database on my remote web host using Query Analyzer is Much, Much faster and I would prefer to make changes that way via SQL statements, especially for something as small as the change I need to make.

I knew it would be a shot in the dark to solve the problem using the ALTER statement, but some guru out here always seems to know a clever way of doing something. Looks like it’s not possible for the ALTER statement. No problem, I will just deal with the slow db connectivity, and fix it through the GUI.

Thanks

 

[topher0303]

ALTER TABLE tablename
ALTER COLUMN colname newdatatype nullability

Chris

 

[tlbroadbent]

You can rename a column with the system stored procedure sp_rename. The keyword Column in the 3rd parameter position indicates you are renaming a column and is required.

EXEC sp_rename 'TableName.OldColName','NewColName','COLUMN'

Be aware that renaming the column will break any views or stored procedures that use the column. These must be modified and recompiled. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[topher0303]

Ignore my post. I was thinking you wanted to change the datatype. My bad.

Chris.

 

[powahusr]

Thanks, you folks are the best!

PS

Do you know where I can get a complete Listing of system stored procedures and their usage? I could only find Level (I) SSP's in SQL Server Books Online. Thanks again!!

 

[tlbroadbent]

System Stored Procedures in BOL

http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_00_519s.asp

Note, not all system stored procedures are documented. Some of these undocumented SPs are explained on various web sites. Here is one link...

http://www.databasejournal.com/features/mssql/article.php/1441221

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[powahusr]

Thank You tlbroadbent