drop table #tmpLostInHeaders
drop table #tmpLostHeadersInJournal
drop table #tmpLostTablesInJournal
drop table #tmpLostDocsFromRA
drop table #tmpDubleIddocH
drop table #tmpDubleIddocJr
drop table #tmpDubleDateTimeIddoc
drop table #tmpDubleIdRefs
drop table #tmpHasNoParentID
drop table #tmpIddocIsNotDTI
drop table #tmpHasNoParentID
drop table #tmpClosedIsMark

create table #tmpLostInHeaders (iddoc char(9),iddocdef int, docno char(20))
create table #tmpLostHeadersInJournal (iddoc char(9),iddocdef int)
create table #tmpLostTablesInJournal (iddoc char(9),iddocdef int)
create table #tmpLostDocsFromRA (iddoc char(9),iddocdef int)
create table #tmpDubleIddocH (iddoc char(9),iddocdef int)
create table #tmpDubleIdRefs (id char(9),RefTypeID int)
create table #tmpHasNoParentID (id char(9),RefTypeID int)

DECLARE @iddocdef char(9)
DECLARE @QueryText varchar(4000)
DECLARE @rowCount int

DEALLOCATE cdh
DECLARE cdh CURSOR FOR select replace(name,'DH','') as idtype from sysobjects where xtype = 'u' and left(name,2)='dh'
OPEN cdh

Set @QueryText = ''
WHILE 1=1 
BEGIN
	FETCH FROM cdh INTO @iddocdef
	IF @@fetch_status=-1
		BREAK
	IF @@fetch_status=-2
		CONTINUE
	-- проверка наличия записей журнала которых нет в шапке
	Set @QueryText = 'insert into #tmpLostInHeaders 
						select iddoc,iddocdef,docno 
						from _1sjourn (nolock) 
						where iddocdef = '+@iddocdef+' 
						and iddoc not in (select iddoc from dh'+@iddocdef+' (nolock))'
 
	exec(@QueryText)

	-- проверка наличия записей шапки которых нет в журнале	
	Set @QueryText = 'insert into #tmpLostHeadersInJournal 
						select iddoc,'+@iddocdef+' 
						from dh'+@iddocdef+' (nolock) 
						where iddoc not in (select iddoc from _1sjourn (nolock))'
	exec(@QueryText)

	-- проверка наличия дублей iddoc в шапках
	Set @QueryText = 'insert into #tmpDubleIddocH 
						select iddoc,min('+@iddocdef+') 
						from dh'+@iddocdef+' (nolock) 
						group by iddoc having count(iddoc) > 1 '
	exec(@QueryText)


END
close cdh

-- проверка наличия записей табличной части которых нет в журнале
DEALLOCATE cdt
DECLARE cdt CURSOR FOR select replace(name,'DT','') as idtype from sysobjects where xtype = 'u' and left(name,2)='dt'
OPEN cdt

Set @QueryText = ''

WHILE 1=1 
BEGIN
	FETCH FROM cdt INTO @iddocdef
	IF @@fetch_status=-1
		BREAK
	IF @@fetch_status=-2
		CONTINUE
	Set @QueryText = 'insert into #tmpLostHeadersInJournal 
						select iddoc,'+@iddocdef+' 
						from dt'+@iddocdef+' (nolock) 
						where iddoc not in (select iddoc from _1sjourn (nolock))'
	exec(@QueryText)
END
close cdt

-- проверка наличия записей в регистре без записей в журнале
DEALLOCATE ra_lst
DECLARE ra_lst CURSOR FOR select replace(name,'ra','') as idtype from sysobjects where xtype = 'u' and left(name,2)='ra'
close ra_lst
OPEN ra_lst

Set @QueryText = ''

WHILE 1=1 BEGIN
	FETCH FROM ra_lst INTO @iddocdef
	IF @@fetch_status=-1
		BREAK
	IF @@fetch_status=-2
  			CONTINUE
	Set @QueryText = 'insert into #tmpLostDocsFromRA 
						select iddoc,'+@iddocdef+'
						from ra'+@iddocdef+' (nolock) 
						where iddoc not in (select iddoc from _1sjourn (nolock))'
	exec(@QueryText)
END
close ra_lst

-- получаем дубли iddoc в журнале
select iddoc into #tmpDubleIddocJr from _1sjourn (nolock) group by iddoc having count(iddoc) > 1 
-- получаем дубли DATE_TIME_IDDOC в журнале
select DATE_TIME_IDDOC into #tmpDubleDateTimeIddoc from _1sjourn (nolock) group by DATE_TIME_IDDOC having count(DATE_TIME_IDDOC) > 1 
-- получаем iddoc у которых right(date_time_iddoc,9) <> iddoc
select iddoc into #tmpIddocIsNotDTI from _1sjourn (nolock) where right(date_time_iddoc,9) <> iddoc
-- получаем список документов у которых статус не равен 0 и стоит пометка на удаление
select iddoc into #tmpClosedIsMark from _1sjourn where closed <>0 and ismark=1


DEALLOCATE refs
DECLARE refs CURSOR FOR select replace(name,'sc','') as idtype from sysobjects where xtype = 'u' and left(name,2)='sc'
OPEN refs

Set @QueryText = ''
WHILE 1=1 
BEGIN
	FETCH FROM refs INTO @iddocdef
	IF @@fetch_status=-1
		BREAK
	IF @@fetch_status=-2
		CONTINUE
	
	-- проверка наличия дублей id в справочниках
	Set @QueryText = 'insert into #tmpDubleIdRefs 
						select id,min('+@iddocdef+') 
						from sc'+@iddocdef+' (nolock) 
						group by id having count(id) > 1 '
	exec(@QueryText)

	-- проверка наличия несуществующих родителей
	Set @QueryText = 'insert into #tmpHasNoParentID 
						select id,'+@iddocdef+'
						from sc'+@iddocdef+' (nolock) 
						where parentID<> ''     0   '' and parentid not in (select id from sc'+@iddocdef+' (nolock) ) '
	exec(@QueryText)


END
close refs

select * from   #tmpLostInHeaders
select * from   #tmpLostHeadersInJournal
select * from   #tmpLostTablesInJournal
select * from   #tmpLostDocsFromRA
select * from   #tmpDubleIddocH
select * from   #tmpDubleIddocJr
select * from   #tmpDubleDateTimeIddoc
select * from   #tmpDubleIdRefs
select * from   #tmpHasNoParentID
select * from   #tmpIddocIsNotDTI
select * from   #tmpHasNoParentID
select * from   #tmpClosedIsMark