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
|