-- ***********************************************************************
-- PROCEDURE NAME : SP_GetCreateTableScript
-- Description    : ƒe[ƒuƒ‹‚̐¶¬‚r‚p‚k‚̎擾 
-- ***********************************************************************
CREATE PROCEDURE SP_GetCreateTableScript
	@v_tableName varchar(256),
	@IdentityOn int = 1       --identity€–Ú‚ª‚ ‚Á‚½‚çAidentity‚Őݒ肷‚é‚©‚Ç‚¤‚©
AS
BEGIN  
	DECLARE @i_objectId int,
	        @i_indId smallint,   --Index id  
	        @v_pkInfo varchar(500),   
	        @v_clusteredInfo varchar(20),
	        @v_pkCol varchar(500),
	        @v_key varchar(50),
	        @i_i smallint;
	        
	SET @i_objectId = object_id(@v_tableName);  
	IF @i_objectId is null RETURN;	
	IF OBJECTPROPERTY(@i_objectId, 'IsTable ') <> 1 RETURN;
	
	CREATE TABLE #CreateTableScript(i_id int identity,v_desc varchar(1000));
	
	INSERT INTO #CreateTableScript(v_desc) VALUES('CREATE TABLE ['+ @v_tableName + '](');

	INSERT INTO #CreateTableScript(v_desc)
	SELECT SPACE(4) + '[' + a.name + ']' + SPACE(1) + '[' + b.name +  ']' + 
		CASE WHEN b.xtype in(167,175,231,239) THEN '('+ case when a.prec = -1 then 'max' else CAST(a.prec  as varchar) end + ') '  
			 WHEN b.xtype in(106,108) THEN '('+ CAST(a.xprec as varchar) + ','+ CAST(a.xscale as varchar)+ ') '  --¬”‚ ‚é
		     ELSE ' '
		END 
		+ SPACE(1) +  
		CASE WHEN (a.colstat & 1 = 1) AND @IdentityOn = 1
		     THEN 'IDENTITY('+ CAST(ident_seed(@v_tableName) as varchar)+ ',' +  
		                       CAST(ident_incr(@v_tableName) as varchar)+ ') '   
		     ELSE ' '
        END
        + CASE a.isnullable WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END + '|'  
	FROM syscolumns a,systypes b  
	WHERE a.id = @i_objectId AND a.xtype = b.xusertype  
	ORDER BY a.colid; 

	IF EXISTS(SELECT 1 FROM sysobjects WHERE parent_obj = @i_objectId AND xtype = 'PK')
	BEGIN  
		SELECT @v_pkInfo=b.name,@i_indId=indid,
	           @v_clusteredInfo = (CASE WHEN (a.status & 16)=16 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END)  
		FROM sysindexes a,sysobjects b  
	    WHERE a.id=b.parent_obj AND a.name=b.name AND b.xtype='PK' AND b.parent_obj=@i_objectId;
		
		SELECT @v_pkCol =  '[' + index_col(@v_tableName,@i_indId,1) + ']';  --PrimaryKey‚̈ê”Ԗڂ̍€–ڎ擾
		
		--‘¼‚ÌPrimaryKey‚̍€–ڎ擾
		SET @i_i = 2;
		SELECT @v_key = index_col(@v_tableName,@i_indId,@i_i);
		WHILE (@v_key IS NOT NULL)  
		BEGIN  
			SELECT @v_pkCol = @v_pkCol + ',[' + @v_key + ']', @i_i = @i_i + 1;  
			SELECT @v_key = index_col(@v_tableName, @i_indId, @i_i);
		END
		
		SET @v_pkInfo = ' CONSTRAINT ['+@v_pkInfo+ '] PRIMARY KEY '+ @v_clusteredInfo + ' ('+ @v_pkCol + ') ';  
		INSERT INTO #CreateTableScript(v_desc) VALUES(@v_pkInfo);   --   ›’ŽåŒ®M‘§‡“ü—ÕŽž•\  
	END
	ELSE
	BEGIN  
		SELECT @i_i = COUNT(-1) FROM #CreateTableScript;
		UPDATE #CreateTableScript SET v_desc = replace(v_desc, '|', ' ') WHERE i_id = @i_i;
	END

	INSERT INTO #CreateTableScript(v_desc) values( ')');
	UPDATE #CreateTableScript SET v_desc = replace(v_desc, '|', ',');
	SELECT v_desc FROM #CreateTableScript ORDER BY i_id;  
	DROP TABLE #CreateTableScript
END