Skip to content

Frequently Asked Questions - SQL Server Tables

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

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]

How do I rename a table?

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.

How do I change the owner of a table?

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.

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

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

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

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

SQL Server Maximum Capacity Specifications

How can I determine if a table has a primary key?

To determine if a table has a primary key, you will use the OBJECTPROPERTY metadata function.

OBJECTPROPERTY ( id, property )

The OBJECTPROPERTY metadata function, which returns information about objects in the current database, accepts two parameters, namely the id, which is the ID of the object in the current database, and the property, which is the information to be returned for the object specified by id. To determine if a table has a primary key, you will use the TableHasPrimaryKey property.

IF OBJECTPROPERTY( OBJECT_ID( '[dbo].[Customers]' ), 'TableHasPrimaryKey' ) = 1
    PRINT '[dbo].[Customers] table has a primary key.'

In this script, the OBJECTPROPERTY metadata function will return a value of 1 if the table has a primary key. Otherwise, it will return a value of 0. If the table name passed to the function is not a valid table in the current database, the function will return a value of NULL.

How can I change the length of a VARCHAR or CHAR column?

To change the length of a VARCHAR or CHAR column, you will use the ALTER TABLE ALTER COLUMN command as follows:

ALTER TABLE Table Name ALTER COLUMN Column Name VARCHAR ( New Length )

To illustrate, let's say you have the following table definition:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]     INT,
    [FirstName]      VARCHAR(20),
    [LastName]       VARCHAR(20)
)

After initial design, you found out that the length of the [FirstName] and [LastName] columns are not long enough. To increase their length, you will issue the following statements:

ALTER TABLE [dbo].[Customers] ALTER COLUMN [FirstName] VARCHAR(50)
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName]  VARCHAR(50)

Be careful when decreasing the length of a column especially when there's data in the table already. If you try to decrease the length of a VARCHAR or CHAR column that has data and the new length is not long enough to store the existing column data, the following error message will be encountered:

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

How do I change a column from a NULL to a NOT NULL?

To change a column to have the NOT NULL constraint, you will use the ALTER TABLE ALTER COLUMN command as follows:

ALTER TABLE Table Name ALTER COLUMN Column Name Data Type NOT NULL)

As can be seen from this command, to add the NOT NULL constraint to a column you still have to specify the data type of the column even if you are not changing the data type.

To illustrate, let's say you have the following table definition:

CREATE TABLE [dbo].[Customers] (
    [CustomerID]     INT,
    [FirstName]      VARCHAR(20),
    [LastName]       VARCHAR(20)
)

Since the nullability of the columns was not specified, it defaults to NULL, meaning the column will accept NULL values. There has been some changes in the business rules and you are now asked to make sure that all columns will have a value. To implement this change in the business rule, you will simply execute the following statements

ALTER TABLE [dbo].[Customers] ALTER COLUMN [CustomerID] INT NOT NULL
ALTER TABLE [dbo].[Customers] ALTER COLUMN [FirstName] VARCHAR(20) NOT NULL
ALTER TABLE [dbo].[Customers] ALTER COLUMN [LastName] VARCHAR(20) NOT NULL

Just make sure that the column you are changing doesn't have any NULL values otherwise you will encounter the following error:

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'LastName', table 'SQLServerHelper.dbo.Customers'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

To avoid this error, update your table by setting the columns with NULL values to a certain value. If the column is a VARCHAR or CHAR data type, you can update it to an empty string. If the column is a numeric data type, you can set it to 0.

UPDATE [dbo].[Customers]
SET [LastName] = ''
WHERE [LastName] IS NULL

What's the maximum number of rows can a table have?

The maximum number of rows a table can have is limited by the available storage in the server. For more information on the maximum capacity specifications of SQL Server, please refer to the following link:

SQL Server Maximum Capacity Specifications

How can I determine if a column is an identity column?

To determine if a column is an identity column, you will use the COLUMNPROPERTY metdata function.

COLUMNPROPERTY ( id, column, property )

The COLUMNPROPERTY metadata function, which returns information about a column or stored procedure parameter in the current database, accepts three parameters, namely the id, which is the ID of the table or stored procedure in the current database, the column, which is the name of the column or stored procedure parameter, and the property, which is the information to be returned for the table or stored procedure specified by id. To determine if a column is an identity column, you will use the IsIdentity property.

IF COLUMNPROPERTY( OBJECT_ID( '[dbo].[Orders]' ), 'OrderID', 'IsIdentity' ) = 1
    PRINT 'Orders.OrderID is an identity column.'

In this script, the COLUMNPROPERTY metadata function will return a value of 1 if the column is an identity column. Otherwise, it will return a value of 0. If the table name passed to the function is not a valid table in the current database or if the column does not exist in the table or stored procedure, the function will return a value of NULL.