sqlserver2000-创建表和修改表
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
修改表：
/
*添加列
drop table if exists userAdd 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