4.primarykeys-update_v001.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

-- create tmp table #qx_maxkeys
create table #qx_maxkeys( tablename varchar(255) unique, keyvalue int )
go

-- select all tables 
DECLARE @id int
DECLARE @tbl varchar(255)
DECLARE @col varchar(255)
DECLARE @stm nvarchar(4000)

DECLARE cur CURSOR FOR
select 
	so.id, so.name, sc.name 
from 
	sysobjects so, syscolumns sc 
where 
	so.xtype = 'U' and
	sc.id = so.id and
	sc.typestat = 1 and sc.type = 56 and sc.colid = 1
order by so.name
OPEN cur

-- fill table #qx_maxkeys
FETCH NEXT FROM cur INTO @id, @tbl, @col
WHILE (@@FETCH_STATUS = 0)
BEGIN
	SELECT @stm ='INSERT INTO #qx_maxkeys SELECT lower(''' + @tbl + ''') AS tablename, MAX(' + @col + ') as keyvalue FROM dbo.' + @tbl 

	--PRINT 'Select table: ' + @tbl
	--PRINT 'Sql: ' + @stm

	EXEC( @stm )
	if @@error <> 0
	begin
		raiserror ('Error inserting objects from %s into #qx_maxkeys, search procedure aborted', 16, 1, @tbl)
	end

	FETCH NEXT FROM cur INTO @id, @tbl, @col
END
CLOSE cur
DEALLOCATE cur
go
DECLARE @tbl varchar(255)
DECLARE @maxkey int
DECLARE @curkey int
DECLARE @increment int

DECLARE cur2 CURSOR FOR
select 
	tablename, keyvalue
from 
	#qx_maxkeys 
OPEN cur2

-- select from #qx_maxkeys
FETCH NEXT FROM cur2 INTO @tbl, @maxkey
WHILE (@@FETCH_STATUS = 0)
BEGIN
	select @curkey = ISNULL((select keyvalue from dbo.qx_keys where lower(tablename) = lower(@tbl)), 0)
	PRINT 'Check table: ' + @tbl + 
		', key: ' + cast( @maxkey as varchar(255) ) + 
		', cur key: ' + cast( @curkey as varchar(255) )

	SELECT @increment = (@maxkey - @curkey)

	IF @increment > 0
	BEGIN
		PRINT 'Set up max key to ' + cast( @increment as varchar(255) )
		EXEC dbo.QX_NEXT_KEY @tbl, @increment, @curkey
	END

	FETCH NEXT FROM cur2 INTO @tbl, @maxkey
END
CLOSE cur2
DEALLOCATE cur2
go

-- drop tmp table
drop table #qx_maxkeys
go