June 7, 2012

sp_replgetparsedddlcmd (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_replgetparsedddlcmd(nvarchar @FirstToken
, nvarchar @objectType
, nvarchar @dbname
, nvarchar @owner
, nvarchar @objname
, nvarchar @targetobject)

MetaData:

 --   
-- Name: sp_replgetparsedddlcmd
--
-- Description: This helper sproc strips out the first part
-- of DDL cmd, up to the point right after object name.
--
--
-- Parameters:
-- @ddlcmd nvarchar(max)
-- ,@FirstToken sysname
-- ,@objectType sysname -- comlete form: e.g. procedure/function/tigger
-- ,@dbname sysname -- not quoted
-- ,@owner sysname -- not quoted
-- ,@objname sysname -- not quoted
-- ,@targetobject nvarchar(512)-- applies to alter trigger only
--
-- Returns: nvarchar(max)
--
-- Notes: this is an internal helper function which assumes
-- incoming @ddlcmd is always valid, it strips out the first
-- part of ddl so we can reconstruct with alternate
-- destination table/owner if so desired, it also helps to
-- to avoid blandly sending DDL with fully qualified table
-- name including publisher database:
-- e.g.
-- sp_replgetparsedddlcmd
-- N'table pubs.dbo.authors add newcol1 int'
-- ,'alter'
-- ,'table'
-- ,'pubs'
-- ,'dbo'
-- ,'authors'
-- should return: N'add newcol1 int'
--
-- Security: not exposed to public
--
create procedure sys.sp_replgetparsedddlcmd
@ddlcmd nvarchar(max) OUTPUT
,@FirstToken sysname
,@objectType sysname -- comlete form: e.g. procedure/function/tigger
,@dbname sysname -- not quoted
,@owner sysname -- not quoted
,@objname sysname -- not quoted
,@targetobject nvarchar(512)-- applies to alter trigger only
as
begin
declare @left_quote bigint
,@right_quote bigint
,@first_space tinyint
,@ddlcmd_len bigint
,@ddloffset nvarchar(max)
,@trigger_dbname sysname
,@trigger_owner sysname
,@trigger_objname sysname

-- start with striping off ALTER at the begining
set @ddloffset = ltrim(right(@ddlcmd, len(@ddlcmd) - len(@FirstToken)))

-- strip out any possible comments between alter and next token
set @ddloffset = sys.fn_replremoveleadingcomments(@ddloffset)
set @ddlcmd_len = len(@ddloffset)

-- now strip out objectType token,
-- remember, this function is only used by DDL trigger where we know @ddlcmd coming in is valid
-- watch out for space after the second token, e.g. alter proc instead of alter procedure
-- watch out for comments, e.g. alter proc-- .. -- myproc instead of alter procedure
set @first_space = patindex('% %', @ddloffset)
set @left_quote = patindex('%-- %', @ddloffset)
if (@first_space > 0 and @first_space < len(@objectType))
or (@left_quote > 0 and @left_quote < len(@objectType))
begin
if (@first_space > 0) and (@left_quote > 0)
begin
if (@left_quote > @first_space)
set @left_quote = @first_space
end
else if (@first_space > 0) and (@left_quote = 0)
set @left_quote = @first_space
end
else
set @left_quote = len(@objectType) + 1
set @ddloffset = ltrim(substring(@ddloffset, @left_quote, @ddlcmd_len - @left_quote + 1))

-- strip out any possible comments between @ObjectType token and object name
set @ddloffset = sys.fn_replremoveleadingcomments(@ddloffset)

-- now strip out object name
-- deal with the following possibilities: w or w/o quotes
-- [db].[owner].[obj]
set @ddloffset = sys.fn_replremovefullobj(@ddloffset, @dbname, @owner, @objname)

-- might as well strip out any possible comments between object name and definition
set @ddloffset = sys.fn_replremoveleadingcomments(@ddloffset)

-- alter trigger trigger_name on [db].[owner].[obj] as .... has the same issue, parse to the point before as
-- so we can substitute with alter trigger trigger_name on [dest_owner].[dest_obj] as
if UPPER(@objectType) = N'TRIGGER' and @targetobject is not NULL and len(@targetobject) > 0
begin
-- remove leading white space char
declare @pos int
select @pos=charindex(N'on ', @ddloffset)
set @ddloffset = substring(@ddloffset, @pos, len(@ddloffset)-@pos+1)

set @ddloffset = ltrim(right(@ddloffset, len(@ddloffset) - len(N'on ')))

set @trigger_dbname = isnull(parsename(@targetobject, 3), @dbname)
set @trigger_owner = isnull(parsename(@targetobject, 2), @owner)
set @trigger_objname = isnull(parsename(@targetobject, 1), @objname)

set @ddloffset = sys.fn_replremovefullobj(@ddloffset, @trigger_dbname, @trigger_owner, @trigger_objname)
end
set @ddlcmd=@ddloffset
return 0
end

No comments:

Post a Comment

Total Pageviews