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]

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.

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

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.

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

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

To determine if a table has a primary key, you will use the OBJECTPROPERTY metdata 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 OBJECTPROPERY 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.

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:

EG:1 ALTER TABLE [dbo].[Customers] ALTER

COLUMN [FirstName] VARCHAR(50)

EG:2 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 errormessage will be encountered:

Server: Msg 8152, Level 16, State 9, Line 1

String or binary data would be truncated.

The statement has been terminated.

To avoid this error, make sure that the new length to be assigned

to the column is long enough to hold the longest string in the column. Please

refer to the following link regarding this error message:

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

constraintto 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

EG:1 ALTER TABLE [dbo].[Customers] ALTER

COLUMN [CustomerID] INT NOT NULL

EG:2 ALTER TABLE [dbo].[Customers] ALTER

COLUMN [FirstName] VARCHAR(20) NOT NULL

EG:3 ALTER TABLE [dbo].[Customers] ALTER

COLUMN [LastName] VARCHAR(20) NOT NULL

Just make sure that the column you are changing doesn't

have any NULLvalues 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 NULLvalues 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 anumeric data type, you can set it to 0.

UPDATE [dbo].[Customers]

SET [LastName] = ''

WHERE [LastName] IS NULL

The maximum number of rows a table can have is limited by the available storage in the server.

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.