USE
ClassNorthwind
IF OBJECT_ID ( ' dbo.Employees ' ) IS NOT NULL
DROP TABLE dbo.Employees
** 检验是否已存在该表
GO
CREATE TABLE dbo.Employees (
EmployeeID int IDENTITY ( 1 , 1 ) NOT NULL ,
LastName nvarchar ( 20 ) NOT NULL ,
FirstName nvarchar ( 10 ) NOT NULL ,
Title nvarchar ( 30 ) NULL ,
TitleOfCourtesy nvarchar ( 25 ) NULL ,
BirthDate datetime NULL ,
HireDate datetime NULL ,
Address nvarchar ( 60 ) NULL ,
City city , ** 用户自定义数据类型
Region region ,
PostalCode postalcode ,
Country country ,
HomePhone nvarchar ( 24 ) NULL ,
Extension nvarchar ( 4 ) NULL ,
Photo image NULL ,
Notes ntext NULL ,
ReportsTo int NULL ,
PhotoPath nvarchar ( 255 ) NULL
) ON [ PRIMARY ]
GO
/* Display results */
SELECT table_name
FROM information_schema.tables
WHERE table_name = ' Employees '
GO
修改表:
/* 添加列
Add a column called Age to the Employees table in the ClassNorthwind database.
*/
USE ClassNorthwind
ALTER TABLE Employees
ADD Age tinyint NULL
go
/* Display results */
exec sp_help Employees
GO
/* 修改
Creates user defined data types.
Drop existing versions first.
*/
USE ClassNorthwind
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' city ' )
EXEC sp_droptype city
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' region ' )
EXEC sp_droptype region
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' country ' )
EXEC sp_droptype country
GO
EXEC sp_addtype city, ' nvarchar(15) ' , NULL
EXEC sp_addtype region, ' nvarchar(15) ' , NULL
EXEC sp_addtype country, ' nvarchar(15) ' , NULL
GO
/* Display results */
SELECT domain_name
FROM information_schema.domains
ORDER BY domain_name
GO
/* 删除列
Drop column called Age from the Employees table in the
ClassNorthwind database.
*/
USE ClassNorthwind
ALTER TABLE Employees
DROP COLUMN age
go
/* Display results */
EXEC sp_help Employees
GO
IF OBJECT_ID ( ' dbo.Employees ' ) IS NOT NULL
DROP TABLE dbo.Employees
** 检验是否已存在该表
GO
CREATE TABLE dbo.Employees (
EmployeeID int IDENTITY ( 1 , 1 ) NOT NULL ,
LastName nvarchar ( 20 ) NOT NULL ,
FirstName nvarchar ( 10 ) NOT NULL ,
Title nvarchar ( 30 ) NULL ,
TitleOfCourtesy nvarchar ( 25 ) NULL ,
BirthDate datetime NULL ,
HireDate datetime NULL ,
Address nvarchar ( 60 ) NULL ,
City city , ** 用户自定义数据类型
Region region ,
PostalCode postalcode ,
Country country ,
HomePhone nvarchar ( 24 ) NULL ,
Extension nvarchar ( 4 ) NULL ,
Photo image NULL ,
Notes ntext NULL ,
ReportsTo int NULL ,
PhotoPath nvarchar ( 255 ) NULL
) ON [ PRIMARY ]
GO
/* Display results */
SELECT table_name
FROM information_schema.tables
WHERE table_name = ' Employees '
GO
修改表:
/* 添加列
Add a column called Age to the Employees table in the ClassNorthwind database.
*/
USE ClassNorthwind
ALTER TABLE Employees
ADD Age tinyint NULL
go
/* Display results */
exec sp_help Employees
GO
/* 修改
Creates user defined data types.
Drop existing versions first.
*/
USE ClassNorthwind
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' city ' )
EXEC sp_droptype city
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' region ' )
EXEC sp_droptype region
IF EXISTS ( SELECT domain_name FROM information_schema.domains
WHERE domain_schema = ' dbo ' AND domain_name = ' country ' )
EXEC sp_droptype country
GO
EXEC sp_addtype city, ' nvarchar(15) ' , NULL
EXEC sp_addtype region, ' nvarchar(15) ' , NULL
EXEC sp_addtype country, ' nvarchar(15) ' , NULL
GO
/* Display results */
SELECT domain_name
FROM information_schema.domains
ORDER BY domain_name
GO
/* 删除列
Drop column called Age from the Employees table in the
ClassNorthwind database.
*/
USE ClassNorthwind
ALTER TABLE Employees
DROP COLUMN age
go
/* Display results */
EXEC sp_help Employees
GO