April 27, 2012

sp_helpmergearticlecolumn (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helpmergearticlecolumn(nvarchar @publication
, nvarchar @article)

MetaData:

   
create procedure sys.sp_helpmergearticlecolumn (
@publication sysname, -- The publication name --
@article sysname -- The article name --
) AS
SET NOCOUNT ON

--
-- Declarations.
--
declare @colid int
declare @colmax int
declare @colname sysname
declare @published bit
declare @columns binary(128)
declare @pubid uniqueidentifier
declare @retcode int
declare @objid int
declare @publisher sysname
declare @publisher_db sysname

select @publisher = publishingservername()
select @publisher_db = db_name()

--
-- Parameter Check: @publication.
-- The @publication name must conform to the rules for identifiers.
--
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_helpmergearticlecolumn')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publication
IF @retcode <> 0 or @@ERROR<>0
RETURN (1)

SELECT @pubid = pubid FROM dbo.sysmergepublications WHERE name = @publication
and LOWER(publisher)=LOWER(@publisher)
and publisher_db = @publisher_db
IF @pubid IS NULL
BEGIN
RAISERROR (21423, 11, -1, @publication)
RETURN (1)
END

-- Security check. (Done after @pubid retrieval, because this param is needed here.)
if 1 <> {fn ISPALUSER(@pubid)} and
(1 <> is_member('replmonitor') or is_member('replmonitor') is null)
begin
raiserror(21423, 11, -1, @publication)
return 1
end

--
-- Parameter Check: @article.
-- The @article name must conform to the rules for identifiers.
--

IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_helpmergearticlecolumn')
RETURN (1)
END
EXECUTE @retcode = sys.sp_validname @article
IF @retcode <> 0 or @@ERROR<>0
RETURN (1)

--
-- Parameter Check: @article, @publication.
-- Check to make sure that the article exists in this publication.
--

IF NOT EXISTS (SELECT * FROM dbo.sysmergearticles WHERE pubid = @pubid AND name = @article)
BEGIN
RAISERROR (20027, 11, -1, @article)
RETURN (1)
END

SELECT @columns = columns, @objid=objid FROM dbo.sysmergearticles
WHERE name = @article AND pubid = @pubid

create table #tmp (column_id int, column_name sysname collate database_default, published bit)
select TOP 1 @colid = column_id from sys.columns where object_id = @objid order by column_id ASC
select TOP 1 @colmax = column_id from sys.columns where object_id = @objid order by column_id DESC
while (@colid <= @colmax)
begin
if exists (select * from sys.columns where object_id = @objid and column_id = @colid)
begin
select @colname = name from sys.columns where object_id=@objid and column_id=@colid
exec @retcode = sys.sp_MStestbit @bm=@columns, @coltotest=@colid
if @retcode<>0
select @published=1
else
select @published=0
insert into #tmp values(@colid, @colname, @published)
end
select @colid=@colid + 1
end
select * from #tmp
drop table #tmp

No comments:

Post a Comment

Total Pageviews