test.sql

物业管理和办公自动化系统

/****** Object:  Database InforTower    Script Date: 2002-06-20 10:05:53 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'InforTower')
	DROP DATABASE [InforTower]
GO

CREATE DATABASE [InforTower]  ON (NAME = N'InforTower_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$CAIXIAOYAN\data\InforTower_Data.MDF' , SIZE = 8, FILEGROWTH = 10%) LOG ON (NAME = N'InforTower_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL$CAIXIAOYAN\data\InforTower_Log.LDF' , SIZE = 13, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

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

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

exec sp_dboption N'InforTower', N'trunc. log', N'false'
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

use [InforTower]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/****** Object:  Table [dbo].[T_Plan]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Plan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Plan]
GO

/****** Object:  Table [dbo].[T_Employee]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Employee]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Employee]
GO

/****** Object:  Table [dbo].[T_Position]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Position]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Position]
GO

/****** Object:  Table [dbo].[T_Education]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Education]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Education]
GO

/****** Object:  Table [dbo].[T_Folk]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Folk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Folk]
GO

/****** Object:  Table [dbo].[T_Health]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Health]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Health]
GO

/****** Object:  Table [dbo].[T_Marriage]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Marriage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Marriage]
GO

/****** Object:  Table [dbo].[T_Node]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Node]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Node]
GO

/****** Object:  Table [dbo].[T_Political]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Political]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Political]
GO

/****** Object:  Table [dbo].[T_Role]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Role]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Role]
GO

/****** Object:  Table [dbo].[T_Title]    Script Date: 2002-06-20 10:05:56 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Title]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Title]
GO

/****** Object:  Login cxy    Script Date: 2002-06-20 10:05:53 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'cxy')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'InforTower', @loginlang = N'简体中文'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'cxy', null, @logindb, @loginlang
END
GO

/****** Object:  Login infortower    Script Date: 2002-06-20 10:05:53 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'infortower')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'InforTower', @loginlang = N'简体中文'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'infortower', null, @logindb, @loginlang
END
GO

/****** Object:  Login TSS.SHNSS\s10141    Script Date: 2002-06-20 10:05:53 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'TSS.SHNSS\s10141')
	exec sp_grantlogin N'TSS.SHNSS\s10141'
	exec sp_defaultdb N'TSS.SHNSS\s10141', N'master'
	exec sp_defaultlanguage N'TSS.SHNSS\s10141', N'简体中文'
GO

/****** Object:  Login TSS.SHNSS\s10141    Script Date: 2002-06-20 10:05:53 ******/
exec sp_addsrvrolemember N'TSS.SHNSS\s10141', sysadmin
GO

/****** Object:  User cxy    Script Date: 2002-06-20 10:05:53 ******/
if not exists (select * from dbo.sysusers where name = N'cxy' and uid < 16382)
	EXEC sp_grantdbaccess N'cxy', N'cxy'
GO

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

/****** Object:  User infor    Script Date: 2002-06-20 10:05:54 ******/
if not exists (select * from dbo.sysusers where name = N'infor' and uid < 16382)
	EXEC sp_grantdbaccess N'infor'
GO

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

/****** Object:  User infortower    Script Date: 2002-06-20 10:05:54 ******/
if not exists (select * from dbo.sysusers where name = N'infortower' and uid < 16382)
	EXEC sp_grantdbaccess N'infortower', N'infortower'
GO

/****** Object:  DatabaseRole common    Script Date: 2002-06-20 10:05:54 ******/
if not exists (select * from dbo.sysusers where name = N'common' and uid > 16399)
	EXEC sp_addrole N'common'
GO

/****** Object:  User common    Script Date: 2002-06-20 10:05:54 ******/
exec sp_addrolemember N'db_datareader', N'common'
GO

/****** Object:  User common    Script Date: 2002-06-20 10:05:54 ******/
exec sp_addrolemember N'db_datawriter', N'common'
GO