Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Determine if a Table has a Primary Key
FAQ - SQL Server

FAQ - SQL Server 2005
Home > Frequently Asked Questions > SQL Server Tables
SQL Server Tables - Frequently Asked Questions

 1.  How do I reference a table from another database in the same server?

Answer:

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]

 2.  How do I rename a table?

Answer:

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 to	become 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.


 3.  How do I change the owner of a table?

Answer:

To change the owner of a table, you will use the sp_changeobjectowner system stored procedure.  The sp_changeobjectowner system stored procedure changes the owner of an object, such as a table, view or stored procedure, in the current database.

sp_changeobjectowner [ @objname = ] 'Object Name',  [ @newowner = ] 'New Owner'

The 'Object Name' is the name of an existing table, view or stored procedure in the current database and can be qualified with the existing object owner, in the form of current_owner.object_name.

The 'New Owner' is the name of the security account that will be the new owner of the object and it must be a valid Microsoft SQL Server user or role, or Microsoft Windows NT user or group in the current database.  Otherwise, the following error will be encountered:

Server: Msg 15410, Level 11, State 1, Procedure sp_changeobjectowner, Line 55
User or role 'New Owner' does not exist in this database.

Here's an example on how to change the owner of the Customers table in the Northwind database to 'my':

EXECUTE sp_changeobjectowner 'dbo.Customers', 'my'

Once the sp_changeobjectowner is successfully executed, the following messages will be displayed:

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

Since you've changed the owner of the table, any scripts or stored procedures that you have that reference the table using the convention owner.table will now produce an error.  Simply go to those scripts and stored procedures and change the owner to the new owner.


 4.  How do I get the list of user tables in a database?

Answer:

There are three ways to get the list of user tables in a database.  The first method is by querying the [dbo].[sysobjects] system table.  The [dbo].[sysobjects] contains one row for each object, such as constraint, table, view, stored procedure, function and so on, created within a database.  To determine the type of object, you will query the [xtype] column which contains the object type.  For user tables the [xtype] value is 'U' which stands for user tables.

SELECT [Name] FROM [dbo].[sysobjects]
WHERE [xtype] = 'U'

The second method is by querying the [Information_Schema].[Tables] system view.  The [Information_Schema].[Tables] system view contains one row for each table in the current database for which the current user has permissions.  This viw is based on the [dbo].[sysobjects] system table.  The [Information_Schema].[Tables] system view will also include views in the list.  To filter out just the user tables, you will only output those records where the [Table_Type] is 'BASE TABLE', as can be seen from the following query:

SELECT * FROM [Information_Schema].[Tables]
WHERE [Table_Type] = 'BASE TABLE'

The third method of listing the user tables in a database is by using the sp_tables system stored procedure.  The sp_tables system stored procedure returns a list of objects that can appear in a FROM clause.  Since you are only concerned with user tables and not system tables or views, you must set the @table_type parameter to "'TABLE'", as can be seen from the following query:

EXEC sp_tables @table_type = "'TABLE'"

 5.  What's the maximum number of columns can a table have?

Answer:

The maximum number of columns a table can have is 1,024 columns.  For more information on the maximum capacity specifications of both SQL Server 7.0, SQL Server 2000 and SQL Server 2005, please refer to the following link:

SQL Server Maximum Capacity Specifications

More Frequently Asked Questions - SQL Server Tables Next Page  
Related Articles :