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