导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

这个.sql文件如何使用?

mlqxj35674 2007-12-16 08:09:09
--===============================================================================
--
-- 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

...全文
598 点赞 收藏 5
写回复
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
rouqu 2007-12-16
起这样的数据库名还真..
回复
-狙击手- 2007-12-16
create   database   [{0}]
go

use {0}

[Microsoft][ODBC SQL Server Driver]语法错误或访问冲突


use [{0}]

命令已成功完成。

drop database [{0}]
/*

正在删除数据库文件 'C:\Program Files\Microsoft SQL Server\MSSQL\data\{0}_log.LDF'。
正在删除数据库文件 'C:\Program Files\Microsoft SQL Server\MSSQL\data\{0}.mdf'。

*/
回复
fcuandy 2007-12-16
-- {0} = Database name

这里提示的很清楚了.
要手工使用的话,用数据库名,把{0}替换掉.

我一般做语句配置时是使用 {$n} 保存为xml的节点值.

调不同的sql语句时,读出不同的xml节点,再把{$n}这样的东西替换为我用要用的东西.

常用的是发邮件. 不同时候给客户发不同的邮件, {$1}表示客户名,{$2}表示订单号...
如果邮件内容直接写在代码里,每次更改时都要重新编译,麻烦,所以就这样弄了.
回复
mlqxj35674 2007-12-16
直接执行是产生如下错误
[Microsoft][ODBC SQL Server Driver]语法错误或违反访问规则
服务器: 消息 15010,级别 16,状态 1,过程 sp_dboption,行 70
数据库 '{0}' 不存在。请用 sp_helpdb 来显示可用的数据库。


服务器: 消息 15010,级别 16,状态 1,过程 sp_dboption,行 70
数据库 '{0}' 不存在。请用 sp_helpdb 来显示可用的数据库。


服务器: 消息 15010,级别 16,状态 1,过程 sp_dboption,行 70
数据库 '{0}' 不存在。请用 sp_helpdb 来显示可用的数据库。


服务器: 消息 15010,级别 16,状态 1,过程 sp_addlogin,行 63
数据库 '{0}' 不存在。请用 sp_helpdb 来显示可用的数据库。
[Microsoft][ODBC SQL Server Driver]语法错误或违反访问规则
服务器: 消息 15007,级别 16,状态 1,过程 sp_grantdbaccess,行 97
登录 '{1}' 不存在。
服务器: 消息 3726,级别 16,状态 1,行 3
未能除去对象 'dbo.TickerList',因为该对象正由一个 FOREIGN KEY 约束引用。
服务器: 消息 2714,级别 16,状态 6,行 2
数据库中已存在名为 'TickerList' 的对象。



是不是在命令行中执行是要传入参数,从内容看,是要创建数据库,但名称是运行是指定的
回复
liangCK 2007-12-16
使用查询分析器打开?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告