use SQL to rename a table

[PushCode]

I have tables in my database named Jay and Members.

I want to use SQL code to delete the Members table then rename the Jay table to Members

I thought the following would do the trick but no luck. It is saying Incorrect syntax near 'RENAME'.

This is the SQL code I'm trying to run:

DROP TABLE Members

RENAME TABLE Jay TO Members


...anyone know how to do this? And no, I can't just do it manually through Enterprise Manager. I need to be able to do it using SQL statements.

Thanks!

 

[fluteplr]

From BOL Books Online

sp_rename
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.