Table Operations in SQL Server
Table Operations in SQL Server
This article, I want to show you some operations on table such as determining table size, copying, renaming and deleting a table, and changing table structure through the ALTER TABLE command.
Determining table size
You can determine the size that a table takes up with the sp_spaceused stored procedure. If you add a table name to the procedure, as in the example :
Exec sp_spaceused Authors
It will return result as below :
Name rows reserved data index_size
------------------- ------------------- ----------------------------- ------------------- --------------------
Authors 23 48 KB 8 KB 40 KB
You learn that SQL Server has reserved 48 KB for the Authors table, which includes 8 KB of data and 40 KB for indexes.
Copying, renaming and deleting a table
When you copy, rename or delete a table, SQL Server takes care of references to the table in the database’s system tables.
You can use Transact-SQL to copy a table with the SELECT INTO command
SELECT * INTO Authors_copy from Authors;
This will extract all the rows from the Authors table and put the result into a new table called authors_copy.
Remember in mind that in order for the SELECT INTO command to work, the new table must not yet exist, and you must enable Select Into/Bulk Copy database option. You can set it either in database properties or in the stored procedure sp_dboption.
Next, we are going to script renaming table name. You can call the SQL Server system stored procedure sp_rename below :
Exec sp_rename Customer, Customer_copy.
Obviously, you can’t rename a table if here is already a table with the new name.
To drop a table, issue SQL DROP TABLE command :
DROP TABLE Customer_copy;
Remember when you drop table, be careful about dependencies. If you are trying to drop a parent table that has other tables referencing it with foreign key constraints, SQL Server will not allow drop to occur. You must drop the child tables first.
Changing a table’s structure through ALTER TABLE command
You can use the SQL ALTER TABLE command to add and drop columns to a table, and also add and drop constraints.
To add a new column called Address to our Customer table, you should submit :
ALTER TABLE Customer
ADD Address varchar(50) NULL
With the ALTER TALBE command, you can add and drop columns and also modify their characteristics. Such as changing a data type
ALTER TABLE Customer
ALTER COLUMN Address CHAR(20) NULL
Also, you ca drop the column :
ALTER TABLE Customer
DROP COLUMN Address
Any columns you add using ALTER TABLE will be placed at the end of a table, so you cannot insert them in any position you want. However, using GUI, you can place the columns in the order you desire.
Written by,
Kasim Wirama, MCSD.NET, MCDBA
Database Administrator