这个.sql文件如何使用?
--===============================================================================
--
-- FMStocks7 SQL Server Schema Creation Script
--
-- schema.sql
--
--===============================================================================
--
-- Copyright (C) 2000 Microsoft Corporation
-- All rights reserved.
--
-- This source code is intended only as a supplement to Microsoft
-- Development Tools and/or on-line documentation. See these other
-- materials for detailed information regarding Microsoft code samples.
--
-- THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
-- OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
-- LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
-- FITNESS FOR A PARTICULAR PURPOSE.
--
--===============================================================================
-- {0} = Database name
use master
go
--
-- Create the FMStocks7 database
--
create database {0}
go
--
-- Turn on the following options for this new database
-- Allow use of the bulkcopy program "bcp".
-- Truncate the transaction log when a checkpoint command is issued.
-- Periodically try to shrink the database
--
sp_dboption '{0}', 'bulkcopy', 'true'
go
sp_dboption '{0}', 'trunc. log', 'true'
go
sp_dboption '{0}', 'autoshrink', 'true'
go
--
-- Create the FMStocks7_login account
--
if not exists (select * from master..syslogins where name = '{1}')
exec sp_addlogin '{1}', '{2}', '{0}'
go
use {0}
go
sp_grantdbaccess '{1}', '{1}'
go
--
-- Drop triggers
--
if exists (select * from sysobjects where id = object_id(N'dbo.tr_accounts_d') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger dbo.tr_accounts_d
go
--
-- Drop views
--
--
-- Drop tables
--
if exists (select * from sysobjects where id = object_id(N'dbo.Positions') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.Positions
go
if exists (select * from sysobjects where id = object_id(N'dbo.Transactions') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.Transactions
go
if exists (select * from sysobjects where id = object_id(N'dbo.Accounts') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.Accounts
go
if exists (select * from sysobjects where id = object_id(N'dbo.TickerList') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.TickerList
go
if exists (select * from sysobjects where id = object_id(N'dbo.CurrentPrices') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.CurrentPrices
go
if exists (select * from sysobjects where id = object_id(N'dbo.Fundamentals') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.Fundamentals
go
if exists (select * from sysobjects where id = object_id(N'dbo.TransactionTypes') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.TransactionTypes
go
--
-- Create tables
--
--
-- Tables, constraints, indexes and defaults
-- Note: the DML presented here was not generated natively from SQL Server 7.
-- It was cleaned up by hand to incorporate everything into a single create table stmt.
--
create table dbo.Accounts (
AccountID int identity ( 5000, 1 ) not null constraint PK_AccountID primary key
, Password binary ( 24 ) not null
, FirstName nvarchar ( 30 ) not null
, LastName nvarchar ( 30 ) not null
, DateOpened datetime null default( getdate() )
, Email nvarchar ( 50 ) not null constraint IX_Email unique
, Closed bit not null default( 0 )
)
go
create table dbo.TickerList (
Ticker nchar ( 6 ) not null constraint PK_TickerList_Ticker primary key
, Company nvarchar ( 60 ) not null
, Exchange nchar ( 4 ) not null
)
go
create table dbo.CurrentPrices (
Ticker nchar( 6 ) not null constraint PK_CurrentPrices_Ticker primary key references TickerList( ticker )
, Last money not null
)
go
create table dbo.Fundamentals (
Ticker nchar ( 6 ) not null constraint PK_Fundamentals_Ticker primary key references TickerList( ticker )
, MarketCap money null
, Sales money null
, Price money null
, DailyDollarVol money null
, SalesGrowth float null
, EPSGrowth float null
, NetProfitMargin float null
, InsiderShares float null
, CashFlowPerShare money null
, PE money null
, EPS_TTM money null
, Date_Q1 datetime null
, EPS_Q1 money null
, Date_FY1 datetime null
, EPS_FY1 money null
, EPS_FY2 money null
, Status int not null default( 0 )
)
go
create table dbo.Positions (
PositionID int identity ( 10000, 1 ) not null constraint PK_PositionsID primary key nonclustered
, AccountID int not null constraint FK_Positions_Accounts references Accounts( AccountID )
, Ticker nchar ( 6 ) not null references CurrentPrices( Ticker )
, Shares int not null
, SharePrice money not null
, Commission money not null default( 0 )
, PurchaseDate datetime not null default( getdate() )
)
go
create clustered index IX_AccountID_Ticker on dbo.Positions(AccountID, Ticker)
go
create table dbo.TransactionTypes (
TransactionTypeID tinyint not null constraint PK_TxTypes primary key
, Description nchar ( 20 ) not null
)
go
create table dbo.Transactions (
TransactionID int IDENTITY ( 1, 1 ) not null constraint PK_TxID primary key nonclustered
, AccountID int not null constraint FK_Transactions_Accounts references Accounts( AccountID )
, Ticker nchar (6) not null references TickerList( ticker )
, Shares nchar (10) not null
, TransactionTypeID tinyint not null references TransactionTypes( TransactionTypeID )
, DateRecorded datetime not null default( getdate() )
, DateExecuted datetime null
, PositionID int null
)
go
create clustered index IX_AccountID_Ticker on dbo.Transactions( AccountID, Ticker )
go
--
-- Create views
--
--
-- Create triggers
--
create trigger tr_accounts_d
on dbo.Accounts
for delete
as
delete Positions
from Positions, deleted
where Positions.AccountID = deleted.AccountID
delete Transactions
from Transactions, deleted
where Transactions.AccountID = deleted.AccountID
go
-- Dropping the DRI. The desired effect is enforced by triggers
alter table Accounts nocheck constraint all
alter table Positions nocheck constraint FK_Positions_Accounts
alter table Transactions nocheck constraint FK_Transactions_Accounts
go