/******************************************************************************/
/* Access All F1 ファイル管理                                                 */
/*                                                                            */
/*                                                                            */
/* 作成するデータベース：All_F1_SQL                                           */
/* 2014/08/01 松本　泰也                                                      */
/******************************************************************************/

/***********************************/
/* 対象データベースの指定          */
/***********************************/
use [master]
GO

/***********************************/
/* 実行者の権限チェック            */
/***********************************/
-- 変数定義
DECLARE @SRsysadmin int , @SRdbcreator int

-- 実行者が、管理者権限またはデータベースの作成者以外は実行できない
-- 現在のユーザーが sysadmin または dbcreator のメンバであるかどうかを確認する
SELECT @SRsysadmin=IS_SRVROLEMEMBER('sysadmin')
SELECT @SRdbcreator=IS_SRVROLEMEMBER( 'dbcreator' )

IF @SRsysadmin=0 AND @SRdbcreator=0
    -- いずれのメンバでもないとき
    BEGIN
        -- RAISERRORで127を指定してOSQLを強制終了する
        RAISERROR( 'データベース作成の権限がありません。処理を終了します',16,127)
        RETURN
    END
GO

/***********************************/
/* bitadp データベースの作成  */
/***********************************/
-- すでに bitadp データベースが存在する場合は削除する
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'All_F1_SQL')
	DROP DATABASE [All_F1_SQL]
GO
-- データベースの作成
CREATE DATABASE [All_F1_SQL]
PRINT '    All_F1_SQL データベースを作成しました'
GO

/***********************************/
/* 対象データベースの指定          */
/***********************************/
use [All_F1_SQL]
GO

/***********************************/
/* 既存テーブルの削除              */
/* 存在する場合には削除する        */
/***********************************/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_All_DATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_All_DATA]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Main]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WK_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[WK_Main]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Cad]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Cad]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Gazou]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Gazou]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Gazou_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Gazou_2]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Gazou_Main]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Gazou_Main]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Office]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Office]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Video]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Video]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_Sonota]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_Sonota]
GO

/***********************************/
/* テーブルの作成                  */
/***********************************/
CREATE TABLE [dbo].[T_All_DATA](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[FuruPath] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](100) COLLATE Japanese_CI_AS NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](100) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
 CONSTRAINT [PK_T_All_DATA] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Main](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
	[Tourokubi] [datetime] NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Bikou] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Domain] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Main] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WK_Main](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
	[Tourokubi] [datetime] NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Bikou] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Domain] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_WK_Main] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Cad](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Cad] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Gazou](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FuruPath] [varchar](255) COLLATE Japanese_CI_AS NULL,	
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Gazou] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Gazou_2](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[img] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FuruPath] [varchar](255) COLLATE Japanese_CI_AS NULL,	
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](255) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Gazou_2] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Office](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Office] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Video](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Video] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Sonota](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Sonota] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[T_Gazou_Main](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ID2] [int] NULL,
	[kanrenID] [int] NULL,
	[FileName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[Comento] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[FolderName] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[kaku] [varchar](10) COLLATE Japanese_CI_AS NULL,
	[Hizuke] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[Size] [varchar](50) COLLATE Japanese_CI_AS NULL,
	[FuruPath] [varchar](255) COLLATE Japanese_CI_AS NULL,
	[登録日] [datetime] NULL,
	[Kanri] [varchar](100) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_T_Gazou_Main] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE INDEX IDX_T_CAD
ON T_CAD(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO

CREATE INDEX IDX_T_Gazou
ON T_Gazou(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO

CREATE INDEX IDX_T_Gazou_2
ON T_Gazou_2(KanrenID,FileName,Comento,Kanri,登録日);
GO

CREATE INDEX IDX_T_Main
ON T_Main(KanrenID,Kanri,FolderName,Bikou);
GO

CREATE INDEX IDX_WK_Main
ON WK_Main(KanrenID,Kanri,FolderName,Bikou);
GO

CREATE INDEX IDX_T_Office
ON T_Office(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO

CREATE INDEX IDX_T_Sonota
ON T_Sonota(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO

CREATE INDEX IDX_T_Video
ON T_Video(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO

CREATE INDEX IDX_T_Gazou_Main
ON T_Gazou_Main(KanrenID,FileName,Comento,FolderName,Kanri,登録日);
GO