Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- 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
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))
	if (@first_space > 0) and (@left_quote > 0)
		if (@left_quote > @first_space)
			set @left_quote = @first_space
	else if (@first_space > 0) and (@left_quote = 0)
		set @left_quote = @first_space
	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
	-- 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)
set @ddlcmd=@ddloffset
return 0

Last revision 2008RTM
See also

  sp_MSdrop_replcom (Procedure)
sp_MSmerge_ddldispatcher (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash