SQL Server - how to view table columns - Asked By woo yowonn on 11-Dec-06 11:18 PM

we use alter table (t-sql)  script to modify columns in ms-sql

may i know what's the script to view the table columns and it attributes??

sp_help - sundar k replied to woo yowonn on 11-Dec-06 11:34 PM

open query analyser and type sp_help <your tablename> , execute it and you can see the complete table details

alter column name - woo yowonn replied to sundar k on 11-Dec-06 11:55 PM

thanks...i haven't try it ...yet...but for ms sql how do i modify the column name using alter table....kinda hard to find the right syntax for ms sql...

sp_rename - to rename a column - mv ark replied to woo yowonn on 12-Dec-06 12:05 AM

You could use the sp_rename system stored procedure to rename a column.

This example (from Books Online) renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

In Management Studio, you could go to the required table, collapse the + sign next to the table and further drill down to the columns to view the column names & data types

sp_rename (T-SQL) - K Pravin Kumar Reddy replied to woo yowonn on 12-Dec-06 12:55 AM

sp_rename (T-SQL)

Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database.

Syntax

sp_rename [@objname =] 'object_name',
    
[@newname =] 'new_name'
    [, [@objtype =] 'object_type']

Arguments
[@objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.

 

Value Description
COLUMN A column to be renamed.
DATABASE A user-defined database. This option is required when renaming a database.
INDEX A user-defined index.
OBJECT An item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.
USERDATATYPE A user-defined data type added by executing sp_addtype.

Return Code Values

0 (success) or a nonzero number (failure)

Result Sets

None

Remarks

You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed.

When you rename a view, information about the view is updated in the sysobjects table. When you rename a stored procedure, information about the procedure is changed in the sysobjects table.

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.


Important After renaming stored procedures and views, flush the procedure cache to ensure all dependent stored procedures and views are recompiled.


Stored procedures and views can be dropped and re-created quickly because neither object stores data. For best results renaming textual objects, drop and re-create the object by its new name.

Permissions

Members of the sysadmin fixed server role, the db_owner fixed database role, or the owner of the object can execute sp_rename.

Examples
A. Rename a table

This example renames the customers table to custs.

EXEC sp_rename 'customers', 'custs'

  

B. Rename a column

This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'

reference

http://doc.ddart.net/mssql/sql70/sp_ra-rz_11.htm