Manually Rebuild the state_lineages Table (SQL Server)

Summary

**This article is ArcSDE version 9.2 specific**

Warning:
The information presented in this doc is internal only and should not be distributed outside of ESRI.

Procedure

Code:
-- Rebuild State Lineages -- update both states and lineages tables.

-- Run the following script as the SDE user. 
-- It requires creating a backup of the original state_lineages table,
-- dropping indexes on the states table 
-- and then validating the procedure was successful.

-- Step 1
set nocount on
-- Check for inconsistency in states table.
if (select count(*) from sde.sde_states where parent_state_id NOT IN (SELECT state_id from sde.sde_states)) > 0
begin
	print 'Inconsistent states table, must be repaired prior to executing routine...'
	return
end

-- Backup sde_state_lineages table
if (object_id('sde_state_lineages_bk') is not null)
begin
	print 'Dropping Existing sde_state_lineages_bk backup table...'
	Drop table sde_state_lineages_bk
end
go
SELECT * into sde_state_lineages_bk FROM sde_state_lineages;
TRUNCATE TABLE sde_state_lineages;

-- backup sde_states table
if (object_id('sde_states_bk') is not null)
begin
	print 'Dropping Existing sde_states_bk backup table...'
	Drop table sde_states_bk
end
go
SELECT * into sde_states_bk FROM sde_states;

-- We need to drop the existing unique key constraint on the states table to allow us to modify lineage names.
ALTER TABLE sde_states DROP CONSTRAINT states_uk;

Declare @parent_prop_count	int;
Declare @parent_prop_1		int;

Declare	state_list CURSOR
for
	SELECT state_id, parent_state_id
	FROM sde_states
	WHERE state_id > 0
	ORDER BY state_id;

Declare @missing_state int;
Declare @current_state int;
Declare @parent_id int;
Declare @prev_lineage int;
set @missing_state = 0;
set @current_state = 0;

BEGIN
	INSERT INTO sde_state_lineages VALUES (0,0);
	
	Open state_list
	fetch state_list into @current_state, @parent_id
	while (@@fetch_status=0) 
	begin

		IF @parent_id = 0
		Begin
			UPDATE sde_states SET lineage_name = @current_state WHERE state_id = @current_state;
			INSERT INTO sde_state_lineages VALUES (@current_state, 0);
			INSERT INTO sde_state_lineages VALUES (@current_state, @current_state);
		End
		ELSE
		begin

			set @parent_prop_count = 
			(
				SELECT count(state_id)
				FROM sde_states 
				WHERE parent_state_id = @parent_id
			)

			set @parent_prop_1 = 
			(
				SELECT top 1 state_id
				FROM sde_states 
				WHERE parent_state_id = @parent_id
				ORDER BY state_id
			)

			set @prev_lineage = (SELECT lineage_name FROM sde_states WHERE state_id = @parent_id);
      
			IF (@parent_prop_count = 1) OR (@parent_prop_1 = @current_state)
			begin
				UPDATE sde_states SET lineage_name = @prev_lineage WHERE state_id = @current_state;
				INSERT INTO sde_state_lineages VALUES (@prev_lineage, @current_state);
			End
			ELSE
			Begin
				UPDATE sde_states SET lineage_name = @current_state WHERE state_id = @current_state;
				INSERT INTO state_lineages 
					SELECT @current_state, lineage_id 
					FROM state_lineages 
					WHERE	lineage_name = @prev_lineage
							AND 
							lineage_id <= @parent_id 
					ORDER BY lineage_id;
				INSERT INTO sde_state_lineages VALUES (@current_state, @current_state);
			END;
		END;
		
		fetch state_list into @current_state, @parent_id
	END;
END;
close state_list
Deallocate state_list
-- Replace the unique index on the states table, this is very important because it's the mechanism which is
-- used to determine if a state needs a new lineage when it is created.
ALTER TABLE sde_states add CONSTRAINT [states_uk] UNIQUE NONCLUSTERED 
(
	[parent_state_id] ASC,
	[lineage_name] ASC
)
---------------------------------------------------------------------------------------------------
-- Step 2
-- Function to return the ansester count of a state
if (object_id('TW_GetAncestorCount') is not null)
begin
	print 'Dropping Existing procedure TW_GetAnsesterCount...'
	Drop function TW_GetAncestorCount
end
go

create function TW_GetAncestorCount(@StateID int)
returns int
As
Begin
	Declare @StCount int;
	with stateTab (State_id, parent_state_id, lvl, sortcol)
	as
	(
		select State_id, parent_state_id, 0, cast(State_id as varbinary(max))
		from sde.sde_states
		where state_id = @StateId

		Union All

		select S1.State_id, S1.parent_state_id, S2.lvl+1, cast(sortcol + cast(S1.State_id as BINARY(4)) as varbinary(max))
		from sde.sde_states as S1
		Join stateTab as S2
		on (S2.parent_State_id = S1.State_id) 
		where not((s1.parent_state_id = 0) AND (s1.state_id = 0))
	)
	select @StCount = (select count(*)+1 from stateTab)
	return @StCount
End
go
---------------------------------------------------------------------------------------------------
-- Step 3
-- The following procedure is used to validate each state's heirarchy and its corresponding state_lineage
-- by comparing the length of each state's lineage. If they are not the same, then there was an error
-- when building the state_lineage index.

	Declare	@num_states		int;
	Declare	@num_lineages	int;
	Declare @Stateid		int;
	Declare @LineageName	int;

DECLARE state_list cursor
for
   SELECT state_id, lineage_name
   FROM sde_states
   WHERE state_id > 0
   ORDER BY state_id;

Open state_list
fetch state_list into @stateid, @LineageName
while (@@fetch_status=0) 
begin

	set @num_states = sde.TW_GetAncestorCount(@stateid)
	set @num_lineages = (
							SELECT COUNT(*)
							FROM sde_state_lineages
							WHERE lineage_name = @LineageName
							AND lineage_id <= @stateid
						)

    IF @num_lineages <> @num_states
		print 'State ' + str(@stateid) + ' lineage is corrupt...'

	fetch state_list into @stateid, @LineageName
END;
close state_list
Deallocate state_list
print 'If No other messages were printed, validation is complete!'