-- Name:
--          sp_IHscriptprimarykey

-- Description:
--          Script primary key constraint

-- Inputs:
--			@article_id		== article id
--			@useAlterTable	== 0 - Use constraint syntax (create table)
--							   1 - Use ALTER TABLE syntax
--			@useClustered	== 0 - Don't add CLUSTERED to the PRIMARY KEY constraint
--							   1 - Add CLUSTERED to PRIMARY KEY constraint

-- Security:
--          Internal (used by script generation)

-- Returns:
--          Success or failure
--			Temp table (#proctext) of commands

-- Owner:
--          

CREATE PROCEDURE sys.sp_IHscriptprimarykey
(
	@article_id		int,
	@useAlterTable	bit = 0,
	@useClustered	bit = 0
)
AS
BEGIN
	DECLARE @retcode				int
	DECLARE @cmd					nvarchar(4000)
	DECLARE @dest_owner				nvarchar(255)
	DECLARE @dest_tabname			sysname
	declare @src_tabname			sysname
	DECLARE @table_id				int
	DECLARE @constraint_name		sysname
	DECLARE @unq_constraint_name	sysname
	DECLARE @clustered				nvarchar(20)
	DECLARE @spacer					nvarchar(1)
	DECLARE @colname				sysname
	declare @cmd_sep      			nvarchar(10)

	SET @retcode = 0
	
	-- Get article info
	select	@dest_owner		= iha.dest_owner,
			@dest_tabname	= iha.dest_table,
			@src_tabname	= ihpt.name
	from	IHarticles iha,
			IHpublishertables ihpt
	where	iha.table_id = ihpt.table_id
	  and	iha.article_id = @article_id

	IF @dest_owner is not null
	BEGIN
	    SELECT @dest_owner = QUOTENAME( @dest_owner ) + N'.'
	END
	ELSE
	BEGIN
	    SELECT @dest_owner = N''
	END
	
	-- Get primary key
	SELECT	@constraint_name		= ihpc.name,
			@unq_constraint_name	= ihpc.name,
			@table_id				= ihpc.table_id
	FROM	IHarticles iha WITH (INDEX(idx_IHarticles_articleid) ),
			IHpublisherconstraints ihpc WITH (INDEX(idx_IHpublisherconstraints_tableid))
	WHERE	ihpc.publisher_id		= iha.publisher_id
	  AND	ihpc.table_id			= iha.table_id
	  AND	ihpc.type				= 'PRIMARYKEY'
	  AND	iha.article_id			= @article_id
	OPTION (LOOP JOIN, FORCE ORDER)
	
	IF @constraint_name IS NOT NULL
	BEGIN
		IF @useAlterTable = 1
		BEGIN
			-- Put command separator (if any) into buffer	
			select @cmd_sep = N'GO'
		END

		-- Verify key can be scripted
		EXEC @retcode = sys.sp_IHVerifyConstraint
							@article_id			= @article_id,
							@constraint_name	= @constraint_name

		IF @retcode != 0 OR @@ERROR != 0
		BEGIN
			GOTO FINISHPK
		END

		-- Generate a unique PK name
		SELECT @unq_constraint_name = sys.fn_IHGenerateUniqueName(@article_id, N'PK', NULL)

		-- Determine clustered state
		IF @useClustered = 1
		BEGIN
			SELECT @clustered = N' CLUSTERED '
		END
		ELSE
		BEGIN
			SELECT @clustered = N' '
		END

		select @cmd =	case @useAlterTable
							when 1 then N'ALTER TABLE '
										+ @dest_owner
										+ QUOTENAME(@dest_tabname) collate database_default
										+ N' ADD CONSTRAINT '
							else N' CONSTRAINT '
						end
						+ QUOTENAME(@unq_constraint_name) collate database_default
						+ N' PRIMARY KEY' + @clustered + N'('

		insert into #proctext(procedure_text) values( @cmd )

		select @spacer	= N' ',
				@cmd	= N''

		DECLARE hArtCol CURSOR LOCAL FAST_FORWARD FOR
		SELECT	ihc.name
		FROM	dbo.IHarticles iha WITH (INDEX( idx_IHarticles_articleid)),
				dbo.IHpublisherconstraints ihpcn WITH ( INDEX( idx_IHpublisherconstraints_tableid)),
				dbo.IHpublishercolumnconstraints ihpcc WITH (INDEX(idx_IHpublishercolumnconstraints_pubconstraintid)),
				dbo.IHpublishercolumns ihpc WITH (INDEX(idx_IHpublishercolumns_pubcolumnid)),
				dbo.IHcolumns ihc WITH (INDEX(idx_IHcolumns_pubcolumnid))
		WHERE	ihpcn.publisher_id				= iha.publisher_id
		  AND	ihpcn.table_id					= iha.table_id
		  AND	ihc.publishercolumn_id			= ihpc.publishercolumn_id
		  AND	ihpc.publishercolumn_id			= ihpcc.publishercolumn_id
		  AND	ihpcn.publisherconstraint_id	= ihpcc.publisherconstraint_id
		  AND	iha.article_id					= ihc.article_id
		  AND	iha.article_id					= @article_id
		  AND	ihpcn.type						= 'PRIMARYKEY'
		ORDER BY ihpcc.indid asc
		OPTION  (LOOP JOIN, FORCE ORDER)
		
		OPEN	hArtCol
		FETCH	hArtCol
		INTO 	@colname

		-- Create column list	
		select	@spacer	= N' ',
				@cmd	= N''

		WHILE (@@fetch_status <> -1)
		begin
			select @cmd = @cmd + @spacer + QUOTENAME(@colname)
			select @spacer = N','

			if len(@cmd) > 3000
			begin
				insert into #proctext(procedure_text) values(@cmd)
				select @cmd = N''
			end

			FETCH	hArtCol
			INTO 	@colname
		end

		CLOSE hArtCol
		DEALLOCATE hArtCol

		insert into #proctext(procedure_text) values( @cmd )
		insert into #proctext(procedure_text) values( N')' )

		IF @useAlterTable = 1
		BEGIN
			-- Write command separator
			insert into #proctext(procedure_text) values (@cmd_sep)
		END
		ELSE
		BEGIN
			-- Close CREATE TABLE
			insert into #proctext(procedure_text) values (N')')
		END
	END

FINISHPK:
	IF @useAlterTable = 0
	BEGIN
		DECLARE @proctext nvarchar(4000)

		-- Replace trailing ',' with ')' if necessary
		SELECT	@proctext = procedure_text
		FROM	#proctext
		WHERE	seq = IDENT_CURRENT('#proctext')

		IF RIGHT(@proctext, 1) = ','
		BEGIN
			UPDATE	#proctext
			SET		procedure_text = LEFT(procedure_text, LEN(procedure_text) - 1) + N')'
			WHERE 	seq = IDENT_CURRENT('#proctext')
		END
	END
	
	RETURN (@retcode)
END