表是用来存储数据和操作数据的逻辑结构，用来组织和存储数据，关系数据库中的所有数据都表现为表的形式，数据表由行和列组成。SQL Server中的数据表分为临时表和永久表，临时表存储在tempdb系统数据库中，当不再使用或退出SQL Server时，临时表会自动删除；永久表一旦创建之后，除非用户删除，否则将一直存在数据库文件中。
创建数据表的两种方法：(1) 通过对象资源管理器创建；(2) 通过Transact-SQL语句进行创建。
1 Transact-SQL创建表
1.1 语法
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
( { <column_definition> | <computed_column_definition>
| <column_set_definition> }
[ <table_constraint> ] [ ,...n ] )
[ ON { partition_scheme_name ( partition_column_name ) | filegroup
| "default" } ]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
[ FILESTREAM_ON { partition_scheme_name | filegroup
| "default" } ]
[ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]<column_definition> ::=
column_name <data_type>
[ FILESTREAM ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT constant_expression ]
| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ]
]
[ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]
[ SPARSE ] <data type> ::=
[ type_schema_name . ] type_name
[ ( precision [ , scale ] | max |
[ { CONTENT | DOCUMENT } ] xml_schema_collection ) ] <column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ { PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ , ...n ] )
]
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
| [ FOREIGN KEY ]
REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
} <computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[
WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ , ...n ] )
]
| [ FOREIGN KEY ]
REFERENCES referenced_table_name [ ( ref_column ) ]
[ ON DELETE { NO ACTION | CASCADE } ]
[ ON UPDATE { NO ACTION } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
[ ON { partition_scheme_name ( partition_column_name )
| filegroup | "default" } ]
] <column_set_definition> ::=
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
(column [ ASC | DESC ] [ ,...n ] )
[
WITH FILLFACTOR = fillfactor
|WITH ( <index_option> [ , ...n ] )
]
[ ON { partition_scheme_name (partition_column_name)
| filegroup | "default" } ]
| FOREIGN KEY
( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
<table_option> ::=
{
DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF}
| ALLOW_PAGE_LOCKS ={ ON | OFF}
| DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]
}
<range> ::=
<partition_number_expression> TO <partition_number_expression>
2 Transact-SQL修改表
2.1 语法
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name
[ (
{
precision [ , scale ]
| max
| xml_schema_collection
}
) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ] [ SPARSE ]
| {ADD | DROP }
{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
}
| [ WITH { CHECK | NOCHECK } ] | ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
| <column_set_definition>
} [ ,...n ] | DROP
{
[ CONSTRAINT ]
{
constraint_name
[ WITH
( <drop_clustered_constraint_option> [ ,...n ] )
]
} [ ,...n ]
| COLUMN
{
column_name
} [ ,...n ]
} [ ,...n ]
| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] } | { ENABLE | DISABLE } CHANGE_TRACKING
[ WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } ) ] | SWITCH [ PARTITION source_partition_number_expression ]
TO target_table
[ PARTITION target_partition_number_expression ]
[ WITH ( <low_lock_priority_wait> ) ]
| SET ( FILESTREAM_ON =
{ partition_scheme_name | filegroup | "default" | "NULL" }
) | REBUILD
[ [PARTITION = ALL]
[ WITH ( <rebuild_option> [ ,...n ] ) ]
| [ PARTITION = partition_number
[ WITH ( <single_partition_rebuild_option> [ ,...n ] ) ]
]
] | <table_option> | <filetable_option>}
[ ; ]
2.2 示例
修改表添加新列
ALTER TABLE [dbo].[Product] ADD [UnitPrice] DECIMAL(18,2) NULL
ALTER TABLE [dbo].[Product] ADD [UnitsInStock] INT NULL, [UnitsOnSale] INT NULL
修改列
ALTER TABLE [dbo].[Product] ALTER COLUMN [ProductName] VARCHAR(100) NOT NULL
删除列
ALTER TABLE [dbo].[Product] DROP COLUMN [UnitPrice]
3 Transact-SQL删除表
3.1 语法
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
table_name [ ,...n ]
[ ; ]
4 表操作示例
查看数据库中全部的表
USE [Portal]
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES
GO
INFORMATION_SCHEMA是一个特定的访问路径，用于显示系统数据库及其内容的相关元数据。如INFORMATION_SCHEMA.VIEWS、INFORMATION_SCHEMA.SCHEMATA。
查看表的属性
存储过程sp_help的功能是查看任意数据库对象、用户自定义数据类型或SQL Server数据类型的信息。
执行存储过程sp_help的语法结构：
EXEC sp_help <name>
查看Product表的属性：
EXEC sp_help Product
