April 30, 2012

sp_IHScriptIdxFile (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_IHScriptIdxFile(int @article_id)

MetaData:

   
--
-- Name:
-- sp_IHScriptIdxFile
--
-- Description:
-- Script snaphot IDX file contents
--
-- Security:
-- Public (for use by snapshot agent)
--
-- Returns:
-- Success or failure
-- Temp table (#proctext) with commands
--
-- Owner:
-- <current owner>

CREATE PROCEDURE sys.sp_IHScriptIdxFile
(
@article_id int
)
AS
BEGIN
DECLARE @retcode int
DECLARE @scriptConstraints bit
DECLARE @schemaOption int
DECLARE @pubtype int
DECLARE @useClustered bit
DECLARE @useUnique bit

SET NOCOUNT ON

-- security check, db_owner
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)

-- create temp table for command fragments
create table #proctext
(
seq int identity NOT NULL,
procedure_text nvarchar(4000) collate database_default
)

-- Get article/publication info
SELECT @schemaOption = CONVERT(INT, fn_replprepadbinary8(iha.schema_option)),
@pubtype = ihp.repl_freq
FROM dbo.IHarticles iha,
dbo.IHpublications ihp
WHERE ihp.pubid = iha.publication_id
AND iha.article_id = @article_id

-- Decide if CLUSTERED PK is requested
IF ((@schemaOption & 0x10) <> 0)
BEGIN
SET @useClustered = 1
END
ELSE
BEGIN
SET @useClustered = 0
END

-- Script constraints
-- Check to see if 0x8000 set for ALTER TABLE syntax
IF ((@schemaOption & 0x8000) <> 0)
BEGIN
-- Script primary key constraints
-- Check to see if 0x80 set for PRIMARY KEY replication
IF (((@schemaOption & 0x80) <> 0) OR (@pubtype = 0))
BEGIN
-- Generate primary key constraints
exec @retcode = sys.sp_IHscriptprimarykey
@article_id = @article_id,
@useAlterTable = 1,
@useClustered = @useClustered

IF @retcode != 0 OR @@ERROR != 0
BEGIN
SET @retcode = 1
GOTO RETURNSCRIPT
END
END
END

-- Script non-clustered indexes
-- Check to see if 0x40 for NON-CLUSTERED INDEXES
IF ((@schemaOption & 0x40) <> 0)
BEGIN
IF (((@schemaOption & 0x4000) <> 0) AND (@schemaOption & 0x8000) <> 0)
BEGIN
-- Script UNIQUE as constraints if ALTER TABLE is used
SET @useUnique = 1
END
ELSE
BEGIN
-- Don't script UNIQUE since they would have been
-- scripted as constraints
SET @useUnique = 0
END


-- Generate indexes
exec @retcode = sys.sp_IHscriptindexes
@article_id = @article_id,
@useAlterTable = 1,
@useUnique = @useUnique

IF @retcode != 0 OR @@ERROR != 0
BEGIN
SET @retcode = 1
GOTO RETURNSCRIPT
END
END

RETURNSCRIPT:
-- Return script fragments
select procedure_text
from #proctext
order by seq asc

drop table #proctext

RETURN @retcode
END

No comments:

Post a Comment

Total Pageviews