Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHscriptprimarykey

  No additional text.


Syntax


-- 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	

 
Last revision 2008RTM
See also

  sp_IHScriptIdxFile (Procedure)
sp_IHScriptSchFile (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash