ね・・・眠い Sergeant です。
長大なSQLになった・・・
便利なものがチラホラあるにはある。
/************************************************************/
--なぜかprimary領域しか作ってないＤＢが多いので
--（調べたら、それしかなかったじょ(TдT)・・・）
--『力技でファイルグループ化したＤＢ作っちゃってこっそり移動しちゃおうか？』
--■仕様
--１．ファイルグループのファイルは２個固定
--２．テーブル、プライマリキー、インデックス。１つ１つを分ける
--３．オブジェクト名判断で１箇所にぶち込む（分けた方がいいかもね）
--４．細かい指定とか「見なかった」事にする
--５．物理設計とか完全無視 <(￣^￣)>
--６．行き当たりばったりで作ったのでSQLとかテキトー
--７．プライマリキーの判定がテキトーかも？
--
--■使い方
--対象のDBに繋げて走行するとテーブル型変数に必要情報が登録される
--あとは好きに料理すればいい ε=ε=ε=┌(;￣▽￣)┘
--Management Studio で取得すると[TAB]が[SPACE]扱いになる
--
--そうそう
--■作られるもの
--１．ＤＢ本体
--２．テーブル（プライマリ付き）
--３．インデックス
--４．トリガー
--５．プロシージャ
--６．ファンクション
--７．ビュー
--※SQL Server 2000 のManagement Studio的なものには
-- ＤＢ定義一式吐き出し機能があったんじゃなかったか？
-- なぜなくなった・・・
--
/************************************************************/
BEGIN
--作るDB名
DECLARE @CREATE_DB_NAME VARCHAR(MAX) = 'TEST_DB';
--格納する先（最後は「￥」で終わること）
--DECLARE @FILEPATHP VARCHAR(MAX) = 'C:\PRIMARY_DB\';
--DECLARE @FILEPATHL VARCHAR(MAX) = 'C:\LOG_FILE\';
--DECLARE @FILEPATH1 VARCHAR(MAX) = 'D:\FILE_PATH\';
--DECLARE @FILEPATH2 VARCHAR(MAX) = 'E:\FILE_PATH\';
DECLARE @FILEPATHP VARCHAR(MAX) = 'C:\PRIMARY_DB\';
DECLARE @FILEPATHL VARCHAR(MAX) = 'C:\LOG_FILE\';
DECLARE @FILEPATH1 VARCHAR(MAX) = 'D:\FILE_PATH\';
DECLARE @FILEPATH2 VARCHAR(MAX) = 'E:\FILE_PATH\';
--CREATE DB
DECLARE @DB_CMD TABLE(
OBJ_NAME VARCHAR(64),
ROW_NO INTEGER,
CMD VARCHAR(MAX))
--CREATE TABLE
DECLARE @TB_CMD TABLE(
OBJ_NAME VARCHAR(64),
ROW_NO INTEGER,
CMD VARCHAR(MAX)
)
--EXEC テーブル付帯情報
DECLARE @EX_CMD TABLE(
OBJ_NAME VARCHAR(64),
ROW_NO INTEGER,
CMD VARCHAR(MAX)
)
--CREATE INDEX
DECLARE @ID_CMD TABLE(
OBJ_NAME VARCHAR(64),
ROW_NO INTEGER,
CMD VARCHAR(MAX)
)
DECLARE @ROW INTEGER=1 --行番号
--プロシージャ、ファンクション、トリガーのソース登録
DECLARE @PROC TABLE(
PROC_NM VARCHAR(64),
ROW_NO INTEGER,
CMD VARCHAR(MAX)
)
----------------------------------------------------------------------------------------------------
--ファイルグループ名の一覧を作っておく
DECLARE @FG_T TABLE (
ID INTEGER,
NAME VARCHAR(60),
xtype VARCHAR(3),
FG VARCHAR(90)
)
INSERT INTO @FG_T
SELECT
ID,
NAME,
XTYPE,
CASE WHEN CHARINDEX('_MST', UPPER(NAME)) > 0 THEN
'FG_MASTER'
ELSE
NAME + '_FG'
END FG
FROM(
SELECT ID,NAME,XTYPE FROM SYS.SYSOBJECTS
WHERE XTYPE ='U' --ユーザー テーブル
AND NAME NOT LIKE '%DIAGR%'
UNION ALL
SELECT DISTINCT IK.ID, I.NAME,'IDX'
FROM SYS.SYSINDEXES I
INNER JOIN SYS.SYSINDEXKEYS IK
ON I.ID = IK.ID
AND I.INDID = IK.INDID
WHERE I.INDID!=0
AND I.ID IN (SELECT ID FROM SYS.SYSOBJECTS WHERE XTYPE='U'AND NAME NOT LIKE '%sysdiagr%') --いつ入れられたんだろう？
) RS
;
----------------------------------------------------------------------------------------------------
--CREATE DATABASE の構成
DECLARE @CREATE_DB VARCHAR(MAX)=''
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'CREATE DATABASE ' + @CREATE_DB_NAME )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'ON PRIMARY ' )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N''PRIMARY_' + @CREATE_DB_NAME + ''', FILENAME = N'''+ @FILEPATHP +'PRIMARY_' + @CREATE_DB_NAME + '.mdf'',MAXSIZE = UNLIMITED),')
--とりあえず作成時用のファイルグループ（デフォルト・ファイルグループ）PRIMARYに入れられたくないんだよぅっ！ｗｗ
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'FILEGROUP DEFAULT_FG CONTAINS FILESTREAM DEFAULT')
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N''DEFAULT_FG1'',FILENAME = N'''+ @FILEPATH1 +'DEFAULT_FG1.ndf'',MAXSIZE = UNLIMITED,SIZE = 10MB,FILEGROWTH = 10MB), ')
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N''DEFAULT_FG2'',FILENAME = N'''+ @FILEPATH2 +'DEFAULT_FG2.ndf'',MAXSIZE = UNLIMITED,SIZE = 10MB,FILEGROWTH = 10MB) ')
DECLARE @FG_NM_BASE VARCHAR(90)
DECLARE @XTYPE VARCHAR(3)
DECLARE FG_CUR CURSOR FOR
SELECT
distinct
FG,
xtype
FROM @FG_T
OPEN FG_CUR;
FETCH NEXT FROM FG_CUR INTO @FG_NM_BASE, @XTYPE
WHILE (@@FETCH_STATUS = 0) BEGIN
DECLARE @FG_NM VARCHAR(MAX) = SUBSTRING(@XTYPE,1,1) + @FG_NM_BASE
DECLARE @FG_F1 VARCHAR(MAX) = @FG_NM + '1'
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, ',FILEGROUP ' + @FG_NM )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N'''+ @FG_NM + '1'',FILENAME = N'''+ @FILEPATH1 + @FG_NM + '1.ndf'',MAXSIZE = UNLIMITED,SIZE = 500MB,FILEGROWTH = 10MB),')
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N'''+ @FG_NM + '2'',FILENAME = N'''+ @FILEPATH2 + @FG_NM + '2.ndf'',MAXSIZE = UNLIMITED,SIZE = 500MB,FILEGROWTH = 10MB)' )
FETCH NEXT FROM FG_CUR INTO @FG_NM_BASE, @XTYPE
END
CLOSE FG_CUR;
DEALLOCATE FG_CUR;
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'LOG ON' )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, '(NAME = N''' + @CREATE_DB_NAME + '_log'',FILENAME = N'''+ @FILEPATHL + @CREATE_DB_NAME + '_log.ldf'',SIZE = 100MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB)' )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'GO' )
----------------------------------------------------------------------------------------------------
--作ったＤＢを使うようにする
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'USE ' + @CREATE_DB_NAME )
SET @ROW +=1;INSERT INTO @DB_CMD VALUES('DB', @ROW, 'GO' )
----------------------------------------------------------------------------------------------------
--登録してあるオブジェクトのリスト
DECLARE OBJ_LST CURSOR FOR
SELECT ID,NAME,XTYPE FROM SYS.SYSOBJECTS
WHERE 1=1
AND XTYPE IN
('FN' --スカラー関数
,'P' --ストアド プロシージャ
--,'PK' --PRIMARY KEY 制約 (TYPE は K)
,'TF' --テーブル関数
,'TR' --SQL DML トリガー
,'U' --ユーザー テーブル
--,'UQ' --UNIQUE 制約 (TYPEは K)
,'V' --ビュー
)
AND NAME NOT LIKE '%DIAGR%'
ORDER BY
CASE XTYPE
WHEN 'U' THEN 1 --ユーザー テーブル
WHEN 'FN' THEN 2 --スカラー関数
WHEN 'P' THEN 3 --ストアド プロシージャ
WHEN 'TF' THEN 4 --テーブル関数
WHEN 'TR' THEN 5 --SQL DML トリガー
--WHEN 'UQ' THEN 6 --UNIQUE 制約 (TYPEは K)
WHEN 'V' THEN 7 --ビュー
END
,ID
;
OPEN OBJ_LST;
DECLARE @MAIN_ID BIGINT;
DECLARE @MAIN_NAME VARCHAR(MAX);
DECLARE @TB VARCHAR(MAX);
DECLARE @TB_OPT VARCHAR(MAX);
FETCH NEXT FROM OBJ_LST INTO
@MAIN_ID,
@MAIN_NAME,
@XTYPE
WHILE (@@FETCH_STATUS=0) BEGIN
----------------------------------------------------------------------------------------------------
--ユーザテーブル
IF @XTYPE = 'U' BEGIN
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, 'CREATE TABLE ' + @MAIN_NAME + '(' )
--カラムIDの最大
DECLARE @COL_ID_MAX INTEGER
SELECT
@COL_ID_MAX = MAX(C.COLID)
FROM SYS.SYSCOLUMNS C
LEFT JOIN (SELECT ID,COLID FROM SYS.SYSINDEXKEYS WHERE INDID IN(
SELECT INDID FROM SYS.SYSINDEXES
WHERE ID IN(SELECT ID FROM SYS.SYSOBJECTS WHERE XTYPE='U' AND NAME NOT LIKE '%sysdiagr%')
AND INDID IN (SELECT INDID FROM SYS.SYSINDEXKEYS)
AND NAME IN (SELECT NAME FROM SYS.SYSOBJECTS)
)
) I
ON C.COLID = I.COLID
AND C.ID = I.ID
INNER JOIN SYS.SYSTYPES T
ON C.XTYPE = T.XTYPE
WHERE C.ID=@MAIN_ID
--カラム名一覧取得
DECLARE COL_CUR CURSOR FOR
SELECT
C.COLID
,C.NAME COL_NM
,T.NAME
+CASE WHEN T.COLLATIONID IS NOT NULL THEN
'(' + CONVERT(VARCHAR,C.LENGTH) + ')'
ELSE
''
END TYPE_NM
,CASE WHEN I.id IS NULL THEN
'NULL'
ELSE
'NOT NULL'
END NUL
,ISNULL(c.collation,'') COLLATION
FROM SYS.SYSCOLUMNS C
LEFT JOIN (SELECT ID,COLID FROM SYS.SYSINDEXKEYS WHERE INDID IN(
SELECT INDID FROM SYS.SYSINDEXES
WHERE ID IN(SELECT ID FROM SYS.SYSOBJECTS WHERE XTYPE='U' AND NAME NOT LIKE '%sysdiagr%')
AND INDID IN (SELECT INDID FROM SYS.SYSINDEXKEYS)
AND NAME IN (SELECT NAME FROM SYS.SYSOBJECTS)
)
) I
ON C.COLID = I.COLID
AND C.ID = I.ID
INNER JOIN SYS.SYSTYPES T
ON C.XTYPE = T.XTYPE
WHERE C.ID=@MAIN_ID
ORDER BY C.COLID
;
OPEN COL_CUR;
DECLARE @COLID INTEGER;
DECLARE @COL_NM VARCHAR(MAX);
DECLARE @TYPE_NM VARCHAR(MAX);
DECLARE @NUL VARCHAR(MAX);
DECLARE @COLLATION VARCHAR(MAX);
DECLARE @PKEY_FLG TINYINT = 0;
FETCH NEXT FROM COL_CUR INTO
@COLID ,
@COL_NM ,
@TYPE_NM ,
@NUL ,
@COLLATION
WHILE (@@FETCH_STATUS=0) BEGIN
IF @COLID != 1 BEGIN
SET @TB += ','
END
SET @TB = ' '
SET @TB += @COL_NM
SET @TB += ' '
SET @TB += @TYPE_NM
SET @TB += ' '
SET @TB += @NUL
SET @TB += ' '
--SET @TB += @COLLATION --何かうまく行かなかったからデフォ値に任せる
IF NOT(@COL_ID_MAX = @COLID AND @PKEY_FLG = 0)
SET @TB += ','
IF @NUL != 'NULL' SET @PKEY_FLG = 1
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, @TB)
--フィールドのコメント取得
DECLARE @OPT_VAL VARCHAR(MAX)=''
SELECT @OPT_VAL = CONVERT(VARCHAR,value)
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @MAIN_NAME, 'column', default)
WHERE OBJNAME = @COL_NM;
IF ISNULL(@OPT_VAL,'') != '' BEGIN
SET @TB_OPT = 'EXEC sys.sp_addextendedproperty'
SET @TB_OPT += ' @name=N''MS_Description'','
SET @TB_OPT += ' @value=N'''+ @OPT_VAL + ''','
SET @TB_OPT += ' @level0type=N''SCHEMA'','
SET @TB_OPT += ' @level0name=N''dbo'','
SET @TB_OPT += ' @level1type=N''TABLE'','
SET @TB_OPT += ' @level1name=N'''+ @MAIN_NAME + ''','
SET @TB_OPT += ' @level2type=N''COLUMN'','
SET @TB_OPT += ' @level2name=N''' + @COL_NM + ''''
SET @ROW +=1;INSERT INTO @EX_CMD VALUES(@MAIN_NAME,@ROW,@TB_OPT)
SET @TB_OPT = 'GO'
SET @ROW +=1;INSERT INTO @EX_CMD VALUES(@MAIN_NAME,@ROW,@TB_OPT)
END
FETCH NEXT FROM COL_CUR INTO
@COLID ,
@COL_NM ,
@TYPE_NM ,
@NUL ,
@COLLATION
END
CLOSE COL_CUR;
DEALLOCATE COL_CUR;
IF @PKEY_FLG = 1 BEGIN
--プライマリキー作成処理
DECLARE @P_KEY_NAME VARCHAR(MAX)
DECLARE @P_KEY_ID INTEGER;
select
@P_KEY_NAME=name
,@P_KEY_ID=indid
FROM sys.sysindexes
where id in(select id from sys.sysobjects where xtype='U' and name not like '%sysdiagr%')
and indid in (select indid from sys.sysindexkeys)
and name in (select name from sys.sysobjects)
and id=@MAIN_ID
SET @TB = ''
SET @TB += ' CONSTRAINT '
SET @TB += @P_KEY_NAME
SET @TB += ' PRIMARY KEY NONCLUSTERED ('
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, @TB)
DECLARE @p_object_id INTEGER
DECLARE @p_index_id INTEGER
DECLARE @p_index_column_id INTEGER
DECLARE @p_column_id INTEGER
DECLARE @p_key_ordinal INTEGER
DECLARE @p_partition_ordinal INTEGER
DECLARE @p_is_descending_key INTEGER
DECLARE @p_is_included_column INTEGER
SET @TB = ''
DECLARE P_KEY CURSOR FOR
SELECT
object_id
,index_id
,index_column_id
,column_id
,key_ordinal
,partition_ordinal
,is_descending_key
,is_included_column
FROM SYS.INDEX_COLUMNS
WHERE OBJECT_ID = @MAIN_ID
AND INDEX_ID =@P_KEY_ID
ORDER BY OBJECT_ID,KEY_ORDINAL
OPEN P_KEY
FETCH NEXT FROM P_KEY INTO
@p_object_id ,
@p_index_id ,
@p_index_column_id ,
@p_column_id ,
@p_key_ordinal ,
@p_partition_ordinal ,
@p_is_descending_key ,
@p_is_included_column
WHILE (@@FETCH_STATUS=0) BEGIN
IF @p_key_ordinal != 1
SET @TB += ','
DECLARE @p_col_nm VARCHAR(64)
SELECT
@p_col_nm = name
FROM sys.columns c
WHERE c.object_id = @MAIN_ID
AND c.column_id = @p_column_id
SET @TB += @p_col_nm
IF @p_is_descending_key = 0
SET @TB += ' ASC'
ELSE
SET @TB += ' DESC'
FETCH NEXT FROM P_KEY INTO
@p_object_id ,
@p_index_id ,
@p_index_column_id ,
@p_column_id ,
@p_key_ordinal ,
@p_partition_ordinal ,
@p_is_descending_key ,
@p_is_included_column
END
CLOSE P_KEY;
DEALLOCATE P_KEY;
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, @TB)
SET @TB = ''
SET @TB += ')'
SET @TB += ' ON '
DECLARE @PF VARCHAR(MAX)
SELECT @PF= a.FG from @FG_T a where a.NAME in(
select name from sys.SYSOBJECTS
WHERE parent_obj = @p_object_id
)
SET @TB += @pf
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, @TB)
END
SET @TB = ''
SET @TB += ') ON '
DECLARE @tFG VARCHAR(MAX)
SELECT @tFG = FG from @FG_T where NAME = @MAIN_NAME
SET @TB += @tFG
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, @TB)
SET @ROW +=1;INSERT INTO @TB_CMD VALUES(@MAIN_NAME, @ROW, 'GO')
END --IF @XTYPE = 'U'
----------------------------------------------------------------------------------------------------
--プロシージャ、スカラー値関数、トリガー、テーブル関数、ビュー
IF @XTYPE IN('P','FN','TR','TF','V') BEGIN
DECLARE @PROC_ROW_NO INTEGER = 1
DECLARE @PG VARCHAR(MAX)
--
DECLARE @PG_TYPE VARCHAR(MAX)
IF @XTYPE = 'P' SET @PG_TYPE = 'PROCEDURE';
IF @XTYPE = 'FN' SET @PG_TYPE = 'FUNCTION';
IF @XTYPE = 'TR' SET @PG_TYPE = 'TRIGGER';
IF @XTYPE = 'TF' SET @PG_TYPE = 'FUNCTION';
IF @XTYPE = 'V' SET @PG_TYPE = 'VIEW';
INSERT INTO @PROC VALUES(@MAIN_NAME,@PROC_ROW_NO,'IF OBJECT_ID(''dbo.' + @MAIN_NAME + ''', ''' + RTRIM(@XTYPE) + ''') IS NOT NULL');SET @PROC_ROW_NO += 1
INSERT INTO @PROC VALUES(@MAIN_NAME,@PROC_ROW_NO,' DROP ' + @PG_TYPE + ' dbo.' + @MAIN_NAME + ';') ;SET @PROC_ROW_NO += 1
INSERT INTO @PROC VALUES(@MAIN_NAME,@PROC_ROW_NO,'GO') ;SET @PROC_ROW_NO += 1
DECLARE @FLG TINYINT =0
DECLARE @ST INTEGER = 1
DECLARE @P_ROW VARCHAR(MAX) --プロシージャの行情報
--いろいろあって行ごとに分解する
WHILE @FLG = 0 BEGIN
--位置指定で場所が存在しないとエラーになるっぽい
--エラーになったら「データがない」って事でループを抜ける
BEGIN TRY
SELECT
@P_ROW = SUBSTRING(DEFINITION,@ST, CHARINDEX(NCHAR(10),DEFINITION,@ST) - @ST)
FROM SYS.SQL_MODULES WHERE OBJECT_ID IN(SELECT ID FROM SYS.SYSOBJECTS WHERE NAME=@MAIN_NAME)
SET @ST += ISNULL(LEN(@P_ROW),0) +1
INSERT INTO @PROC
VALUES(@MAIN_NAME,@PROC_ROW_NO,@P_ROW)
SET @PROC_ROW_NO += 1
END TRY
BEGIN CATCH
SET @FLG = 1
END CATCH
END
INSERT INTO @PROC VALUES(@MAIN_NAME,@PROC_ROW_NO,'GO')
END
----------------------------------------------------------------------------------------------------
FETCH NEXT FROM OBJ_LST INTO
@MAIN_ID,
@MAIN_NAME,
@XTYPE
END;
CLOSE OBJ_LST;
DEALLOCATE OBJ_LST;
----------------------------------------------------------------------------------------------------
--インデックス作成
DECLARE @IDX_COUNT INTEGER
SELECT
@IDX_COUNT = COUNT(*)
FROM @FG_T
IF @IDX_COUNT > 0 BEGIN
DECLARE @INDEX_NAME VARCHAR(MAX)
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE @COLMN_NAME VARCHAR(MAX)
DECLARE @KEYNO INTEGER
DECLARE @preTABLE_NAME VARCHAR(MAX)
DECLARE @INDEX_FG_NAME VARCHAR(MAX)
DECLARE @IDX_FLG TINYINT = 0
--インデックス情報の取得
DECLARE IDX CURSOR FOR
SELECT
si.name AS index_name
,so.name AS table_name
,scol.name AS column_name
,sik.keyno
FROM
sysindexkeys sik
,sysobjects so
,syscolumns scol
,sysindexes si
WHERE sik.id = so.id
AND sik.id = scol.id
AND sik.colid = scol.colid
AND sik.id = si.id
AND sik.indid = si.indid
AND so.xtype = 'U'
AND so.name in(SELECT DISTINCT OBJ_NAME FROM @TB_CMD )
AND si.name not in (SELECT name from sys.objects soso WHERE soso.type='PK')
ORDER BY
si.name
,sik.id
,sik.indid
,sik.keyno
OPEN IDX;
--作りが何か美しくない・・・
FETCH NEXT FROM IDX INTO @INDEX_NAME,@TABLE_NAME,@COLMN_NAME,@KEYNO
WHILE @@FETCH_STATUS=0 BEGIN
IF @KEYNO = 1 BEGIN
IF @IDX_FLG != 0 BEGIN
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, ') ON ' + @INDEX_FG_NAME)
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, 'GO')
END
SET @preTABLE_NAME = @TABLE_NAME
SELECT
@INDEX_FG_NAME = FG
FROM @FG_T
WHERE NAME=@INDEX_NAME
DECLARE @UQ VARCHAR(MAX)
SELECT
@UQ = CASE WHEN COUNT(*) = 0 THEN '' ELSE 'UNIQUE ' END
FROM sys.sysobjects
WHERE name=@INDEX_NAME
--とりあえず必要ないから（￣▽￣）
--インデックス構成の種類を取得してない
--昇順降順も取得してない
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, 'CREATE ' + @UQ + 'INDEX ' + @INDEX_NAME + ' ON '+ @TABLE_NAME + '(')
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, @COLMN_NAME + ' ASC')
SET @IDX_FLG = 1
END ELSE BEGIn
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, ',' + @COLMN_NAME + ' ASC')
END
FETCH NEXT FROM IDX INTO @INDEX_NAME,@TABLE_NAME,@COLMN_NAME,@KEYNO
END
CLOSE IDX
DEALLOCATE IDX
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, ') ON ' + @INDEX_FG_NAME)
SET @ROW +=1;INSERT INTO @ID_CMD VALUES(@preTABLE_NAME, @ROW, 'GO')
END
END
--CREATE DB
SELECT OBJ_NAME,ROW_NO,CMD FROM @DB_CMD
ORDER BY OBJ_NAME,ROW_NO
--CREATE TABLE
SELECT OBJ_NAME,ROW_NO,CMD FROM (
SELECT OBJ_NAME,1 TYP,ROW_NO,CMD FROM @TB_CMD
UNION ALL
SELECT OBJ_NAME,2 TYP,ROW_NO,CMD FROM @EX_CMD
) RS
ORDER BY OBJ_NAME, TYP, ROW_NO
--CREATE INDEX
SELECT OBJ_NAME,ROW_NO,CMD FROM @ID_CMD
ORDER BY OBJ_NAME,ROW_NO
--CREATE PROC & FUNC & TRI & VIEW
SELECT PROC_NM,ROW_NO,CMD FROM @PROC
ORDER BY PROC_NM,ROW_NO
