IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'asset') DROP DATABASE [asset]GO
CREATE DATABASE [asset] ON (NAME = N'asset_Data', FILENAME = N'D:/41y01/project/asset_Data.MDF' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'asset_Log', FILENAME = N'D:/41y01/project/asset_Log.LDF' , SIZE = 1, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_ASGO
exec sp_dboption N'asset', N'autoclose', N'false'GO
exec sp_dboption N'asset', N'bulkcopy', N'false'GO
exec sp_dboption N'asset', N'trunc. log', N'true'GO
exec sp_dboption N'asset', N'torn page detection', N'false'GO
exec sp_dboption N'asset', N'read only', N'false'GO
exec sp_dboption N'asset', N'dbo use', N'false'GO
exec sp_dboption N'asset', N'single', N'false'GO
exec sp_dboption N'asset', N'autoshrink', N'true'GO
exec sp_dboption N'asset', N'ANSI null default', N'false'GO
exec sp_dboption N'asset', N'recursive triggers', N'false'GO
exec sp_dboption N'asset', N'ANSI nulls', N'false'GO
exec sp_dboption N'asset', N'concat null yields null', N'false'GO
exec sp_dboption N'asset', N'cursor close on commit', N'false'GO
exec sp_dboption N'asset', N'default to local cursor', N'false'GO
exec sp_dboption N'asset', N'quoted identifier', N'false'GO
exec sp_dboption N'asset', N'ANSI warnings', N'false'GO
exec sp_dboption N'asset', N'auto create statistics', N'true'GO
exec sp_dboption N'asset', N'auto update statistics', N'true'GO
use [asset]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ChildKind_FatherKind]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[ChildKind] DROP CONSTRAINT FK_ChildKind_FatherKindGO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Action]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Action]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Asset]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Asset]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChildKind]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[ChildKind]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Employee]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FatherKind]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[FatherKind]GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Manager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Manager]GO
CREATE TABLE [dbo].[Action] ( [asset_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [action_time] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [manager_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_use] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO
CREATE TABLE [dbo].[Asset] ( [asset_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_type] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_price] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_buytime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_status] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [asset_user] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [childkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO
CREATE TABLE [dbo].[ChildKind] ( [fatherkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [childkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [childkind_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO
CREATE TABLE [dbo].[Employee] ( [employee_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [employee_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [employee_post] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [remark] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO
CREATE TABLE [dbo].[FatherKind] ( [fatherkind_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [fatherkind_name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO
CREATE TABLE [dbo].[Manager] ( [manager_id] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [manager_username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [manager_password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY]GO
ALTER TABLE [dbo].[Asset] WITH NOCHECK ADD CONSTRAINT [PK_Asset] PRIMARY KEY CLUSTERED ( [asset_id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[ChildKind] WITH NOCHECK ADD CONSTRAINT [PK_ChildKind] PRIMARY KEY CLUSTERED ( [childkind_id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[FatherKind] WITH NOCHECK ADD CONSTRAINT [PK_MainKind] PRIMARY KEY CLUSTERED ( [fatherkind_id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[Manager] WITH NOCHECK ADD CONSTRAINT [PK_Manager] PRIMARY KEY CLUSTERED ( [manager_id] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[ChildKind] ADD CONSTRAINT [FK_ChildKind_FatherKind] FOREIGN KEY ( [fatherkind_id] ) REFERENCES [dbo].[FatherKind] ( [fatherkind_id] )GO
