﻿* SET_DB_M.TXT HARASAWA
M-COMMENT_BEGIN
=LOGIN_SQL{EXEC SP_CONFIGURE 'SHOW ADVANCED OPTIONS','1'};
=LOGIN_SQL{RECONFIGURE WITH OVERRIDE};
=LOGIN_SQL{EXEC SP_CONFIGURE 'REMOTE LOGIN TIMEOUT',60};
=LOGIN_SQL{EXEC SP_CONFIGURE 'REMOTE QUERY TIMEOUT',0};
=LOGIN_SQL{RECONFIGURE WITH OVERRIDE};
***********************( create database )************************************************************************
=SET{W_PER='ZZZZ'};
NAME=DATABASE
REPEAT=4
=MESSAGE{CREATE DATABASE (?W_PER?)}; 
=LOGIN_SQL{CREATE DATABASE ?W_IS??W_##?_?W_PER? ON PRIMARY(
=NAME=DAT
=,FILENAME='?W_D##??_P??W_IS??W_##?_?W_PER?_DAT.MDF'
=,FILEGROWTH=1) LOG ON(NAME=LOG
=,FILENAME='?W_L##??_P??W_IS??W_##?_?W_PER?_LOG.LDF'
=,FILEGROWTH=1)}IF{_DB_ERROR!='M1801'}ERROR{};
=LOGIN_SQL{EXEC SP_ADDUMPDEVICE 'DISK'
=,'?W_IS??W_##?_?W_PER?_DAT'
=,'?W_BDAT##??_P??W_IS??W_##?_?W_PER?.DAT'}
=  IF{_DB_ERROR!='M15026'}ERROR{?_DB_ERROR? ?_ERROR?};
=LOGIN_SQL{EXEC SP_ADDUMPDEVICE 'DISK'
=,'?W_IS??W_##?_?W_PER?_LOG'
=,'?W_BLOG##??_P??W_IS??W_##?_?W_PER?.LOG'}
=  IF{_DB_ERROR!='M15026'}IF{_DB_ERROR!='M15061'}ERROR{};
=IF{_DB_SOFT/'M200'}JUMP{OLD};
=LOGIN_SQL{ALTER DATABASE ?W_IS??W_##?_?W_PER? SET AUTO_CLOSE OFF};
=LOGIN_SQL{ALTER DATABASE ?W_IS??W_##?_?W_PER? SET AUTO_SHRINK ON};
1+=LOGIN_SQL{ALTER DATABASE ?W_IS??W_##?_?W_PER? SET RECOVERY FULL};
1-=LOGIN_SQL{ALTER DATABASE ?W_IS??W_##?_?W_PER? SET RECOVERY SIMPLE};
*2-4-=LOGIN_SQL{?W_EX_DB?,'SELECT INTO/BULKCOPY','FALSE'};
*1-3-=LOGIN_SQL{?W_EX_DB?,'SELECT INTO/BULKCOPY','TRUE'};
=JUMP{J};
=OLD:
=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','AUTOCLOSE','FALSE'};
=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','AUTOSHRINK','TRUE'};
1+=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','TRUNC. LOG ON CHKPT.','FALSE'};
1-=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','TRUNC. LOG ON CHKPT.','TRUE'};
2-4-=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','SELECT INTO/BULKCOPY','FALSE'};
1-3-=LOGIN_SQL{EXEC SP_DBOPTION '?W_IS??W_##?_?W_PER?','SELECT INTO/BULKCOPY','TRUE'};
=J:
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=DATABASE
***********************( exec sp_addlogin )************************************************************************
=SET{W_PER='ZZZZ'};
NAME=LOGIN
REPEAT=2 (W_RT##)
REPEAT2=4 (W_DIOL$$)
=MESSAGE{EXEC SP_ADDLOGIN (?W_RT##?,?W_PER?)}; 
$1+=SET{WPAS=PASSWORD_?W_RT##?1};
$4+=SET{WPAS=PASSWORD_LINK1};
=LOGIN_SQL{EXEC SP_ADDLOGIN '?W_IS??W_RT##?_?W_DIOL$$?_?W_PER?','?WPAS?'
= ,'?W_IS??W_RT##?_MAST_?W_PER?'}
=  IF{_DB_ERROR!='M15025'}ERROR{};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=LOGIN
******************( create user )***********************************************************
=SET{W_PER='ZZZZ'};
NAME=USER
REPEAT=3 (W_RT_A##,W_RT_B##)
REPEAT2=2 (W_PER_ALL$$) 
=MESSAGE{EXEC CREATE USER (?W_PER?)}; 
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_DBO_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=  IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_DBO_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=  IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_INP_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_INP_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_OUT_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_OUT_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_LNK_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER_ALL$$?
=  CREATE USER ?W_IS??W_RT_B##?_LNK_?W_PER?
=  WITH DEFAULT_SCHEMA=DBO}
=IF{_DB_ERROR!='M15023'}ERROR{};
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=USER
*******************************( sp_addrolemember )***********************l
=SET{W_PER='ZZZZ'};
NAME=ADDROLLMEMBER
=MESSAGE{EXEC EXEC SP_ADDROLEMEMBER (?W_PER?)}; 
REPEAT=3 (W_RT_A##,W_RT_B##)
REPEAT2=2 (W_PER$$,(ZZ,自分)) 
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER? 
= EXEC SP_ADDROLEMEMBER 'DB_OWNER'
= ,'?W_IS?REAL_DBO_ZZZZ'};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_MAST_?W_PER? 
= EXEC SP_ADDROLEMEMBER 'DB_OWNER'
= ,'?W_IS??W_RT_B##?_DBO_?W_PER$$?'};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER?
= EXEC SP_ADDROLEMEMBER 'DB_OWNER'
= ,'?W_IS??W_RT_B##?_DBO_?W_PER$$?'};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER?
= EXEC SP_ADDROLEMEMBER 'DB_OWNER'
= ,'?W_IS??W_RT_B##?_INP_?W_PER$$?'};
=LOGIN_SQL{USE ?W_IS??W_RT_A##?_WORK_?W_PER?
= EXEC SP_ADDROLEMEMBER 'DB_OWNER'
= ,'?W_IS??W_RT_B##?_OUT_?W_PER$$?'};
=IF{W_PER='ZZZZ'}JUMP{J};
REPEAT2=
=J:
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=ADDROLLMEMBER
******************************( create view )*************************************
***
=SET{W_REAL_TEST='REAL'};
=SET{W_PER='ZZZZ'};
NAME=VIEW
=SET{W_DBX='?W_IS??W_REAL_TEST?'};
=SET{W_M=_M_?W_REAL_TEST?};
REPEAT=2
=SET{W_DB='?W_DBX?_?W_MW##?_?W_PER?'};
=MESSAGE{CREATE VIEW(?W_REAL_TEST?,?W_PER?)};
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_TEXT,ZZZZ_ADD_DATE17
=,ZZZZ_CHANGE_DATE17)AS SELECT
= CAST(CASE O.TYPE WHEN 'U' THEN 'TABLE' WHEN 'V'
= THEN 'VIEW' WHEN 'P' THEN 'PROC' WHEN 'FN' THEN 'FUNC' 
= WHEN 'TR' THEN 'TRIG' ELSE 'XXXXX' END AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4)),CAST('?W_MW##?' AS CHAR(4))
=,CAST(' ' AS CHAR(3)),CAST(O.NAME AS NCHAR(30)),C.DEFINITION
=,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),O.CREATE_DATE,121),'-',''),':',''),'.',''),' ','') AS CHAR(17))
=,CAST(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(23),O.MODIFY_DATE,121),'-',''),':',''),'.',''),' ','') AS CHAR(17))
=FROM ?W_DB?.SYS.SCHEMAS S
=    ,?W_DB?.SYS.OBJECTS O
=LEFT JOIN ?W_DB?.SYS.SQL_MODULES C 
=ON C.OBJECT_ID=O.OBJECT_ID 
=WHERE S.SCHEMA_ID=O.SCHEMA_ID AND S.NAME='DBO'
=AND O.TYPE IN('U','V','P','TR','FN')
=}IF{_DB_ERROR!='M2714'}ERROR{};
***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_GRANT,ZZZZ_SELECT
=,ZZZZ_INSERT,ZZZZ_UPDATE,ZZZZ_DELETE,ZZZZ_EXECUTE)AS SELECT
=CAST(CASE O.TYPE 
=WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' 
=WHEN 'P' THEN 'PROC'  WHEN 'FN' THEN 'FUNC'
=WHEN 'TR' THEN 'TRIG' ELSE 'XXXXX' END AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4))
=,CAST(' ' AS CHAR(3)),CAST(O.NAME AS NCHAR(30))
=,CAST(UPPER(RIGHT(U0.NAME,8)) AS CHAR(8))
=,CAST(CASE SUBSTRING(P0.TYPE,1,1)
=WHEN 'S' THEN 'S' ELSE ' ' END AS CHAR(1))
=,CAST(SUBSTRING(P1.TYPE,1,1) AS CHAR(1))
=,CAST(SUBSTRING(P2.TYPE,1,1) AS CHAR(1))
=,CAST(SUBSTRING(P3.TYPE,1,1) AS CHAR(1))
=,CAST(CASE SUBSTRING(P0.TYPE,1,1)
=WHEN 'E' THEN 'E' ELSE ' ' END AS CHAR(1))
=FROM ?W_DB?.SYS.OBJECTS O
=JOIN ?W_DB?.SYS.DATABASE_PERMISSIONS P0 
=ON O.OBJECT_ID=P0.MAJOR_ID
=AND (P0.TYPE='SL' AND O.TYPE IN('U','V')
=OR P0.TYPE='EX' AND O.TYPE IN('P','FN')) 
=JOIN ?W_DB?.SYS.DATABASE_PRINCIPALS U0
=ON (U0.NAME LIKE '?W_DBX?%' OR U0.NAME=LOWER('PUBLIC'))
=AND P0.GRANTEE_PRINCIPAL_ID=U0.PRINCIPAL_ID 
=LEFT JOIN ?W_DB?.SYS.DATABASE_PERMISSIONS P1
=ON O.OBJECT_ID=P1.MAJOR_ID AND 
=P0.GRANTOR_PRINCIPAL_ID=P1.GRANTOR_PRINCIPAL_ID
=AND P1.TYPE='IN' AND 
=P0.GRANTEE_PRINCIPAL_ID=P1.GRANTEE_PRINCIPAL_ID  
=LEFT JOIN ?W_DB?.SYS.DATABASE_PRINCIPALS U1 
=ON U0.NAME=U1.NAME 
=AND P1.GRANTEE_PRINCIPAL_ID=U1.PRINCIPAL_ID
=LEFT JOIN ?W_DB?.SYS.DATABASE_PERMISSIONS P2
=ON O.OBJECT_ID=P2.MAJOR_ID AND 
=P0.GRANTOR_PRINCIPAL_ID=P2.GRANTOR_PRINCIPAL_ID
=AND P2.TYPE='UP' AND 
=P0.GRANTEE_PRINCIPAL_ID=P2.GRANTEE_PRINCIPAL_ID
=LEFT JOIN ?W_DB?.SYS.DATABASE_PRINCIPALS U2 
=ON U0.NAME=U2.NAME AND 
=P2.GRANTEE_PRINCIPAL_ID=U2.PRINCIPAL_ID
=LEFT JOIN ?W_DB?.SYS.DATABASE_PERMISSIONS P3
=ON O.OBJECT_ID=P3.MAJOR_ID AND 
=P0.GRANTOR_PRINCIPAL_ID=P3.GRANTOR_PRINCIPAL_ID
=AND P3.TYPE='DE' AND
=P0.GRANTEE_PRINCIPAL_ID=P3.GRANTEE_PRINCIPAL_ID
=LEFT JOIN ?W_DB?.SYS.DATABASE_PRINCIPALS U3
=ON U0.NAME=U3.NAME AND 
=P3.GRANTEE_PRINCIPAL_ID=U3.PRINCIPAL_ID
=}IF{_DB_ERROR!='M2714'}ERROR{};
***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}
={(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_SEQ,ZZZZ_DATA_NAME
=,ZZZZ_DEFAULT_TYPE,ZZZZ_DEFAULT_LENGTH1,ZZZZ_DEFAULT_LENGTH2
=,ZZZZ_DEFAULT,ZZZZ_NULL)AS SELECT
=CAST(CASE O.TYPE WHEN 'U' 
=THEN 'TABLE' WHEN 'V' THEN 'VIEW' ELSE 'XXXXX' END AS CHAR(5))
=,CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4))
=,CAST(' ' AS CHAR(3)),CAST(O.NAME AS NCHAR(30))
=,CAST(C.COLUMN_ID AS INT)
=,CAST(C.NAME AS NCHAR(30)),CAST(UPPER(T.NAME) AS CHAR(9))
=,CAST(CASE WHEN T.NAME IN('BINARY','CHAR','VARCHAR'
=,'DECIMAL','NCHAR','NVARCHAR','DECIMAL','NUMERIC')
=THEN CASE C.PRECISION WHEN 0 THEN CASE
=WHEN C.MAX_LENGTH<=0 THEN 4000 ELSE 
=CASE WHEN T.NAME IN('NCHAR','NVARCHAR')
=THEN C.MAX_LENGTH/2 ELSE C.MAX_LENGTH END END
=ELSE C.PRECISION END
=WHEN T.NAME='DATETIME' THEN 17 WHEN T.NAME='DATE' THEN 8 WHEN T.NAME='TEXT'
=THEN 4000 ELSE 9 END AS INT),CAST(CASE WHEN T.NAME
=IN('DECIMAL','NUMERIC')THEN C.SCALE ELSE 0 END AS INT)
=,CAST(CASE WHEN D.DEFINITION IS NULL THEN NULL  
=WHEN DATALENGTH(RTRIM(D.DEFINITION))<3 THEN 
=D.DEFINITION ELSE SUBSTRING(D.DEFINITION,2
=,DATALENGTH(CONVERT(VARCHAR,D.DEFINITION))-2) END AS NCHAR(30))
=,CAST(CASE WHEN C.IS_NULLABLE='1'
=THEN '1' ELSE '0' END AS CHAR(1))
=FROM ?W_DB?.SYS.COLUMNS C JOIN
=     ?W_DB?.SYS.OBJECTS O
=ON C.OBJECT_ID=O.OBJECT_ID AND O.TYPE
= IN('U','V') JOIN ?W_DB?.SYS.SCHEMAS S
=ON S.SCHEMA_ID=O.SCHEMA_ID
= AND S.NAME='DBO' JOIN ?W_DB?.SYS.TYPES T
=ON C.USER_TYPE_ID=T.USER_TYPE_ID 
=AND C.SYSTEM_TYPE_ID=T.SYSTEM_TYPE_ID
=LEFT JOIN ?W_DB?.SYS.DEFAULT_CONSTRAINTS D
=ON D.OBJECT_ID=C.DEFAULT_OBJECT_ID
=}IF{_DB_ERROR!='M2714'}ERROR{};
***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}
={(ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_DATA_NAME,ZZZZ_IX,ZZZZ_NO)AS SELECT
= CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4))
=,CAST(' ' AS CHAR(3)),CAST(O.NAME AS NCHAR(30))
=,CAST(C.NAME AS NCHAR(30))
=,CAST(RIGHT(I.NAME,1) AS CHAR(1))
=,CAST(K.KEY_ORDINAL-I.IS_UNIQUE AS INT)
=FROM ?W_DB?.SYS.INDEXES I
=JOIN ?W_DB?.SYS.INDEX_COLUMNS K
=ON I.INDEX_ID=K.INDEX_ID
=AND I.OBJECT_ID=K.OBJECT_ID
=JOIN ?W_DB?.SYS.OBJECTS    O
=ON O.OBJECT_ID=I.OBJECT_ID  
=AND I.NAME IN(O.NAME+'0',O.NAME+'1',O.NAME+'2',O.NAME+'3'
=,O.NAME+'4',O.NAME+'5',O.NAME+'6',O.NAME+'7',O.NAME+'8'
=,O.NAME+'9') AND O.TYPE IN('U','V')
=JOIN ?W_DB?.SYS.COLUMNS C
=ON C.OBJECT_ID=I.OBJECT_ID
=AND C.COLUMN_ID=K.COLUMN_ID
=JOIN ?W_DB?.SYS.SCHEMAS S
=ON S.SCHEMA_ID=O.SCHEMA_ID
=AND S.NAME='DBO'
=}IF{_DB_ERROR!='M2714'}ERROR{};
***
REPEAT=
NAME=
=SET{W_PER='ZZZY'};
COPY=VIEW
=SET{W_REAL_TEST='TEST'};
=SET{W_PER='ZZZZ'};
COPY=VIEW
=SET{W_PER='ZZZY'};
COPY=VIEW
*********************( create function )***********************************
=MESSAGE{CREATE FUNCTIOM};
=SET{W_REAL_TEST='REAL'};
NAME=FUNC
=SET{W_M=_M_?W_REAL_TEST?};
=MESSAGE{CREATE FUNCTIOM (?W_REAL_TEST?)};
=DBO_DROP_OBJECT{?W_M?_DAY_CHAR_FUNC}{FUNC};
=DBO_CREATE_OBJECT{?W_M?_DAY_CHAR_FUNC}{FUNC}{(@IN CHAR(8),@ADD INT)
= RETURNS CHAR(8) AS
= BEGIN
= RETURN CONVERT(CHAR(8),DATEADD(DAY,@ADD,CONVERT(DATETIME,@IN)),112)
= END}IF{_DB_ERROR!='M2714'}ERROR{};
=DBO_DROP_OBJECT{?W_M?_DAY_DATE_FUNC}{FUNC};
=DBO_CREATE_OBJECT{?W_M?_DAY_DATE_FUNC}{FUNC}{(@IN DATETIME,@ADD INT)
= RETURNS CHAR(8) AS
= BEGIN
= RETURN CONVERT(CHAR(8),DATEADD(DAY,@ADD,@IN),112)
= END}IF{_DB_ERROR!='M2714'}ERROR{};
=DBO_DROP_OBJECT{?W_M?_WEEK_CHAR_FUNC}{FUNC};
=DBO_CREATE_OBJECT{?W_M?_WEEK_CHAR_FUNC}{FUNC}{(@IN CHAR(8))
= RETURNS INT AS
= BEGIN
= RETURN DATEPART(DW,CONVERT(DATETIME,@IN))
= END}IF{_DB_ERROR!='M2714'}ERROR{};
=DBO_DROP_OBJECT{?W_M?_WEEK_DATE_FUNC}{FUNC};
=DBO_CREATE_OBJECT{?W_M?_WEEK_DATE_FUNC}{FUNC}{(@IN DATETIME)
= RETURNS INT AS
= BEGIN
= RETURN DATEPART(DW,@IN)
= END}IF{_DB_ERROR!='M2714'}ERROR{};
NAME=
=SET{W_REAL_TEST='TEST'};
COPY=FUNC
M-COMMENT_END
