如何判断数据库和table的存在(50分)

如何判断数据库和table的存在，即使用语句如何实现，判断文件是否存在可以用fileexists（filename），
而数据库呢？请高手指点！

这个要看你是什么数据库啊

/****** Object: Database pubs Script Date: 2002-10-26 13:33:44 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'pubs')
DROP DATABASE [pubs]
GO

CREATE DATABASE [pubs] ON (NAME = N'pubs', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/pubs.mdf' , SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N'pubs_log', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/pubs_log.ldf' , FILEGROWTH = 10%)
GO

exec sp_dboption N'pubs', N'autoclose', N'false'
GO

exec sp_dboption N'pubs', N'bulkcopy', N'false'
GO

exec sp_dboption N'pubs', N'trunc. log', N'true'
GO

exec sp_dboption N'pubs', N'torn page detection', N'true'
GO

exec sp_dboption N'pubs', N'read only', N'false'
GO

exec sp_dboption N'pubs', N'dbo use', N'false'
GO

exec sp_dboption N'pubs', N'single', N'false'
GO

exec sp_dboption N'pubs', N'autoshrink', N'false'
GO

exec sp_dboption N'pubs', N'ANSI null default', N'false'
GO

exec sp_dboption N'pubs', N'recursive triggers', N'false'
GO

exec sp_dboption N'pubs', N'ANSI nulls', N'false'
GO

exec sp_dboption N'pubs', N'concat null yields null', N'false'
GO

exec sp_dboption N'pubs', N'cursor close on commit', N'false'
GO

exec sp_dboption N'pubs', N'default to local cursor', N'false'
GO

exec sp_dboption N'pubs', N'quoted identifier', N'false'
GO

exec sp_dboption N'pubs', N'ANSI warnings', N'false'
GO

exec sp_dboption N'pubs', N'auto create statistics', N'true'
GO

exec sp_dboption N'pubs', N'auto update statistics', N'true'
GO

use [pubs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titleauth__au_id__0519C6AF]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[titleauthor] DROP CONSTRAINT FK__titleauth__au_id__0519C6AF
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__employee__job_id__1BFD2C07]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK__employee__job_id__1BFD2C07
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__employee__pub_id__1ED998B2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[employee] DROP CONSTRAINT FK__employee__pub_id__1ED998B2
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__pub_info__pub_id__173876EA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[pub_info] DROP CONSTRAINT FK__pub_info__pub_id__173876EA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titles__pub_id__014935CB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[titles] DROP CONSTRAINT FK__titles__pub_id__014935CB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__discounts__stor___0F975522]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[discounts] DROP CONSTRAINT FK__discounts__stor___0F975522
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__sales__stor_id__0AD2A005]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sales] DROP CONSTRAINT FK__sales__stor_id__0AD2A005
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__roysched__title___0DAF0CB0]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[roysched] DROP CONSTRAINT FK__roysched__title___0DAF0CB0
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__sales__title_id__0BC6C43E]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[sales] DROP CONSTRAINT FK__sales__title_id__0BC6C43E
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__titleauth__title__060DEAE8]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[titleauthor] DROP CONSTRAINT FK__titleauth__title__060DEAE8
GO

/****** Object: Trigger dbo.employee_insupd Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee_insupd]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[employee_insupd]
GO

/****** Object: Stored Procedure dbo.byroyalty Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[byroyalty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[byroyalty]
GO

/****** Object: Stored Procedure dbo.reptq2 Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reptq2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[reptq2]
GO

/****** Object: Stored Procedure dbo.reptq1 Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reptq1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[reptq1]
GO

/****** Object: Stored Procedure dbo.reptq3 Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reptq3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[reptq3]
GO

/****** Object: View dbo.titleview Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titleview]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[titleview]
GO

/****** Object: Table [dbo].[roysched] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[roysched]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[roysched]
GO

/****** Object: Table [dbo].[sales] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sales]
GO

/****** Object: Table [dbo].[titleauthor] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titleauthor]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[titleauthor]
GO

/****** Object: Table [dbo].[discounts] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[discounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[discounts]
GO

/****** Object: Table [dbo].[employee] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[employee]
GO

/****** Object: Table [dbo].[pub_info] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pub_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[pub_info]
GO

/****** Object: Table [dbo].[titles] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[titles]
GO

/****** Object: Table [dbo].[authors] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[authors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[authors]
GO

/****** Object: Table [dbo].[jobs] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[jobs]
GO

/****** Object: Table [dbo].[publishers] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[publishers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[publishers]
GO

/****** Object: Table [dbo].[stores] Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stores]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stores]
GO

/****** Object: User Defined Datatype empid Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.systypes where name = N'empid')
exec sp_droptype N'empid'
GO

/****** Object: User Defined Datatype id Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.systypes where name = N'id')
exec sp_droptype N'id'
GO

/****** Object: User Defined Datatype tid Script Date: 2002-10-26 13:33:49 ******/
if exists (select * from dbo.systypes where name = N'tid')
exec sp_droptype N'tid'
GO

/****** Object: User dbo Script Date: 2002-10-26 13:33:44 ******/
/****** Object: User guest Script Date: 2002-10-26 13:33:44 ******/
if not exists (select * from dbo.sysusers where name = N'guest' and uid < 16382 and hasdbaccess = 1)
EXEC sp_grantdbaccess N'guest'
GO

GRANT CREATE TABLE , CREATE VIEW , CREATE PROCEDURE , CREATE DEFAULT , CREATE RULE TO [guest]
GO

/****** Object: User Defined Datatype empid Script Date: 2002-10-26 13:33:50 ******/
setuser
GO

EXEC sp_addtype N'empid', N'char (9)', N'not null'
GO

setuser
GO

/****** Object: User Defined Datatype id Script Date: 2002-10-26 13:33:50 ******/
setuser
GO

EXEC sp_addtype N'id', N'varchar (11)', N'not null'
GO

setuser
GO

/****** Object: User Defined Datatype tid Script Date: 2002-10-26 13:33:50 ******/
setuser
GO

EXEC sp_addtype N'tid', N'varchar (6)', N'not null'
GO

setuser
GO

/****** Object: Table [dbo].[authors] Script Date: 2002-10-26 13:33:50 ******/
CREATE TABLE [dbo].[authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) NOT NULL ,
[au_fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL ,
[address] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[zip] [char] (5) NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[jobs] Script Date: 2002-10-26 13:33:52 ******/
CREATE TABLE [dbo].[jobs] (
[job_id] [smallint] IDENTITY (1, 1) NOT NULL ,
[job_desc] [varchar] (50) NOT NULL ,
[min_lvl] [tinyint] NOT NULL ,
[max_lvl] [tinyint] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[publishers] Script Date: 2002-10-26 13:33:52 ******/
CREATE TABLE [dbo].[publishers] (
[pub_id] [char] (4) NOT NULL ,
[pub_name] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[country] [varchar] (30) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[stores] Script Date: 2002-10-26 13:33:52 ******/
CREATE TABLE [dbo].[stores] (
[stor_id] [char] (4) NOT NULL ,
[stor_name] [varchar] (40) NULL ,
[stor_address] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[zip] [char] (5) NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[discounts] Script Date: 2002-10-26 13:33:52 ******/
CREATE TABLE [dbo].[discounts] (
[discounttype] [varchar] (40) NOT NULL ,
[stor_id] [char] (4) NULL ,
[lowqty] [smallint] NULL ,
[highqty] [smallint] NULL ,
[discount] [decimal](4, 2) NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[employee] Script Date: 2002-10-26 13:33:52 ******/
CREATE TABLE [dbo].[employee] (
[emp_id] [empid] NOT NULL ,
[fname] [varchar] (20) NOT NULL ,
[minit] [char] (1) NULL ,
[lname] [varchar] (30) NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NULL ,
[pub_id] [char] (4) NOT NULL ,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[pub_info] Script Date: 2002-10-26 13:33:53 ******/
CREATE TABLE [dbo].[pub_info] (
[pub_id] [char] (4) NOT NULL ,
[logo] [image] NULL ,
[pr_info] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[titles] Script Date: 2002-10-26 13:33:53 ******/
CREATE TABLE [dbo].[titles] (
[title_id] [tid] NOT NULL ,
[title] [varchar] (80) NOT NULL ,
[type] [char] (12) NOT NULL ,
[pub_id] [char] (4) NULL ,
[price] [money] NULL ,
[advance] [money] NULL ,
[royalty] [int] NULL ,
[ytd_sales] [int] NULL ,
[notes] [varchar] (200) NULL ,
[pubdate] [datetime] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[roysched] Script Date: 2002-10-26 13:33:53 ******/
CREATE TABLE [dbo].[roysched] (
[title_id] [tid] NOT NULL ,
[lorange] [int] NULL ,
[hirange] [int] NULL ,
[royalty] [int] NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[sales] Script Date: 2002-10-26 13:33:53 ******/
CREATE TABLE [dbo].[sales] (
[stor_id] [char] (4) NOT NULL ,
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NOT NULL ,
[payterms] [varchar] (12) NOT NULL ,
[title_id] [tid] NOT NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[titleauthor] Script Date: 2002-10-26 13:33:53 ******/
CREATE TABLE [dbo].[titleauthor] (
[au_id] [id] NOT NULL ,
[title_id] [tid] NOT NULL ,
[au_ord] [tinyint] NULL ,
[royaltyper] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[authors] WITH NOCHECK ADD
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[jobs] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[job_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[publishers] WITH NOCHECK ADD
CONSTRAINT [UPKCL_pubind] PRIMARY KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[stores] WITH NOCHECK ADD
CONSTRAINT [UPK_storeid] PRIMARY KEY CLUSTERED
(
[stor_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[pub_info] WITH NOCHECK ADD
CONSTRAINT [UPKCL_pubinfo] PRIMARY KEY CLUSTERED
(
[pub_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[titles] WITH NOCHECK ADD
CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED
(
[title_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[sales] WITH NOCHECK ADD
CONSTRAINT [UPKCL_sales] PRIMARY KEY CLUSTERED
(
[stor_id],
[ord_num],
[title_id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[titleauthor] WITH NOCHECK ADD
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id],
[title_id]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [employee_ind] ON [dbo].[employee]([lname], [fname], [minit]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[authors] WITH NOCHECK ADD
CONSTRAINT [DF__authors__phone__78B3EFCA] DEFAULT ('UNKNOWN') FOR [phone],
CHECK ([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'),
CHECK ([zip] like '[0-9][0-9][0-9][0-9][0-9]')
GO

ALTER TABLE [dbo].[jobs] WITH NOCHECK ADD
CONSTRAINT [DF__jobs__job_desc__1273C1CD] DEFAULT ('New Position - title not formalized yet') FOR [job_desc],
CHECK ([max_lvl] <= 250),
CHECK ([min_lvl] >= 10)
GO

ALTER TABLE [dbo].[publishers] WITH NOCHECK ADD
CONSTRAINT [DF__publisher__count__7D78A4E7] DEFAULT ('USA') FOR [country],
CHECK ([pub_id] = '1756' or ([pub_id] = '1622' or ([pub_id] = '0877' or ([pub_id] = '0736' or [pub_id] = '1389'))) or [pub_id] like '99[0-9][0-9]')
GO

ALTER TABLE [dbo].[employee] WITH NOCHECK ADD
CONSTRAINT [DF__employee__job_id__1B0907CE] DEFAULT (1) FOR [job_id],
CONSTRAINT [DF__employee__job_lv__1CF15040] DEFAULT (10) FOR [job_lvl],
CONSTRAINT [DF__employee__pub_id__1DE57479] DEFAULT ('9952') FOR [pub_id],
CONSTRAINT [DF__employee__hire_d__1FCDBCEB] DEFAULT (getdate()) FOR [hire_date],
CONSTRAINT [PK_emp_id] PRIMARY KEY NONCLUSTERED
(
[emp_id]
) ON [PRIMARY] ,
CONSTRAINT [CK_emp_id] CHECK ([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')
GO

ALTER TABLE [dbo].[titles] WITH NOCHECK ADD
CONSTRAINT [DF__titles__type__00551192] DEFAULT ('UNDECIDED') FOR [type],
CONSTRAINT [DF__titles__pubdate__023D5A04] DEFAULT (getdate()) FOR [pubdate]
GO

CREATE INDEX [aunmind] ON [dbo].[authors]([au_lname], [au_fname]) ON [PRIMARY]
GO

CREATE INDEX [titleind] ON [dbo].[titles]([title]) ON [PRIMARY]
GO

CREATE INDEX [titleidind] ON [dbo].[roysched]([title_id]) ON [PRIMARY]
GO

CREATE INDEX [titleidind] ON [dbo].[sales]([title_id]) ON [PRIMARY]
GO

CREATE INDEX [auidind] ON [dbo].[titleauthor]([au_id]) ON [PRIMARY]
GO

CREATE INDEX [titleidind] ON [dbo].[titleauthor]([title_id]) ON [PRIMARY]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[authors] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[jobs] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[publishers] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[stores] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[discounts] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[employee] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[pub_info] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[titles] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[roysched] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[sales] TO [guest]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[titleauthor] TO [guest]
GO

ALTER TABLE [dbo].[discounts] ADD
FOREIGN KEY
(
[stor_id]
) REFERENCES [dbo].[stores] (
[stor_id]
)
GO

ALTER TABLE [dbo].[employee] ADD
FOREIGN KEY
(
[job_id]
) REFERENCES [dbo].[jobs] (
[job_id]
),
FOREIGN KEY
(
[pub_id]
) REFERENCES [dbo].[publishers] (
[pub_id]
)
GO

ALTER TABLE [dbo].[pub_info] ADD
FOREIGN KEY
(
[pub_id]
) REFERENCES [dbo].[publishers] (
[pub_id]
)
GO

ALTER TABLE [dbo].[titles] ADD
FOREIGN KEY
(
[pub_id]
) REFERENCES [dbo].[publishers] (
[pub_id]
)
GO

ALTER TABLE [dbo].[roysched] ADD
FOREIGN KEY
(
[title_id]
) REFERENCES [dbo].[titles] (
[title_id]
)
GO

ALTER TABLE [dbo].[sales] ADD
FOREIGN KEY
(
[stor_id]
) REFERENCES [dbo].[stores] (
[stor_id]
),
FOREIGN KEY
(
[title_id]
) REFERENCES [dbo].[titles] (
[title_id]
)
GO

ALTER TABLE [dbo].[titleauthor] ADD
FOREIGN KEY
(
[au_id]
) REFERENCES [dbo].[authors] (
[au_id]
),
FOREIGN KEY
(
[title_id]
) REFERENCES [dbo].[titles] (
[title_id]
)
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: View dbo.titleview Script Date: 2002-10-26 13:33:54 ******/

CREATE VIEW titleview
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[titleview] TO [guest]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.reptq1 Script Date: 2002-10-26 13:33:54 ******/

CREATE PROCEDURE reptq1 AS
select pub_id, title_id, price, pubdate
from titles
where price is NOT NULL
order by pub_id
COMPUTE avg(price) BY pub_id
COMPUTE avg(price)


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[reptq1] TO [public]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.reptq3 Script Date: 2002-10-26 13:33:54 ******/

CREATE PROCEDURE reptq3 @lolimit money, @hilimit money,
@type char(12)
AS
select pub_id, type, title_id, price
from titles
where price >@lolimit AND price <@hilimit AND type = @type OR type LIKE '%cook%'
order by pub_id, type
COMPUTE count(title_id) BY pub_id, type


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[reptq3] TO [public]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.byroyalty Script Date: 2002-10-26 13:33:54 ******/

CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[byroyalty] TO [public]
GO

GRANT EXECUTE ON [dbo].[byroyalty] TO [guest]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.reptq2 Script Date: 2002-10-26 13:33:54 ******/

CREATE PROCEDURE reptq2 AS
select type, pub_id, titles.title_id, au_ord,
Name = substring (au_lname, 1,15), ytd_sales
from titles, authors, titleauthor
where titles.title_id = titleauthor.title_id AND authors.au_id = titleauthor.au_id
AND pub_id is NOT NULL
order by pub_id, type
COMPUTE avg(ytd_sales) BY pub_id, type
COMPUTE avg(ytd_sales) BY pub_id


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[reptq2] TO [public]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/****** Object: Trigger dbo.employee_insupd Script Date: 2002-10-26 13:33:54 ******/

CREATE TRIGGER employee_insupd
ON employee
FOR insert, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
raiserror ('Job id 1 expects the default level of 10.',16,1)
ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
raiserror ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
end


GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

多人接受答案了。