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

Hello!

I have problem with sp_rename:

I do us follows:

EXECUTE sp_rename N'[emma].[dim_model_tmp]', N'[emma].[dim_model_tmp2]'

The table [emma].[dim_model_tmp] disappears but and I cannot find [emma].[dim_model_tmp2].

Tried to create a new [emma].[dim_model_tmp] and rename it again. But get the following error:

Msg 15335, Level 11, State 1, Procedure sp_rename, Line 402

Error: The new name '[emma].[dim_model_tmp2]' is already in use as a object name and would cause a duplicate that is not permitted.

Tried to find [emma].[dim_model_tmp2]:

select * from SYSOBJECTS where upper(name) = upper('dim_model_tmp2')

select * from INFORMATION_SCHEMA.TABLES where upper(table_name) = 'DIM_MODEL_TMP2'

But no hit!

I have also tried:

EXECUTE sp_rename N'[did_stage].[emma].[dim_model_tmp]', N'[did_stage].[emma].[dim_model_tmp2]'

But this has the same effect.

Can anyone explain where my table gone and how to rename it right?

Best regards,

Tina

|||

Hello!

The sp_rename should look like this:

EXECUTE sp_rename N'emma.dim_model_tmp', N'dim_model_tmp2'

The schema name shall not be on the new table name. Don’t use the "[" will become part of the table name.

To find the tables use like in the select statements:

select * from SYSOBJECTS where upper(name) like upper('%dim_model%')

select * from INFORMATION_SCHEMA.TABLES where upper(table_name) like '%DIM_MODEL%'

Best regards,

Tina