Referencing and renaming tables..

help how to reference n rename table..

is it possible by code?

renaming..

There are two ways to rename a table. The first and easier way is with the use of SQL Server Enterprise Manager. Simply go to the database where the table is located and right-click on the table you want to rename. Select "Rename" from the context menu and change the name. After entering the new name, the following message will be displayed:

Changing the name of the table will cause stored procedures, views, or triggers

that reference the table tobecome invalid. Are you sure you want to rename the table?

If you are sure that there are no stored procedures, views or triggers that reference the table, then click on the Yes button. If you are not sure if the table is being reference by any other object, click on the View Dependencies button to see the objects that depend on the table as well as the objects that the table depends on. You are more concerned on the objects that depend on the table because you have to modify those objects to reflect the new name of the table.

The second way to rename a table is with the use of sp_rename system stored procedure. The sp_rename system stored procedure changes the name of a user-created object, such as a table, column or user-defined type, in the current database

sp_rename [ @.objname = ] 'Object Name',

[ @.newname = ] 'New Name'

[ , [ @.objtype = ] 'Object Type' ]

The 'Object Name' is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. The 'New Name' is the new name for the specified object. The 'Object Name' is the type of object being renamed. Since we are only concerned of renaming a table, this optional parameter can be ignored.

Here's how to rename a table called [dbo].[Clients] to [dbo].[Customers]:

EXECUTE sp_rename '[dbo].[Clients]', 'Customers'

If there's no object with the name of Customers that exist in the current database, then the table will be renamed to [dbo].[Customers] and the following message will be displayed:

Caution: Changing any part of an object name could break scripts and stored procedures.

The object was renamed to 'Customers'.

This is just a warning message informing you that you have to modify any scripts or stored procedures that you may have that references the table using its previous name.

If an object already exists with the name that you specified, you will encounter the following error message:

Server: Msg 15335, Level 11, State 1, Procedure sp_rename, Line 342

Error: The @.newname value 'Customers' is already in use as a object name

and would cause a duplicate that is not permitted.

Just make sure that the new name that you are assigning to the table is not yet used by any object (not just by tables but also by other objects such as views) to avoid getting this error.

|||

referencing ...

To reference a table from another database in the same server, simply prefix the table with the database name. In other words, use the 3-part naming convention of the table.

To illustrate, to select from the [dbo].[Authors] table in the pubs database from the Northwind database, the SELECT statement will be as follows:

SELECT * FROM [pubs].[dbo].[Authors]|||the rename option isnt available when i roight click the table