/********************************************************************************************************
* ESTE SCRIPT CRIA 9 PROCEDURES, UMA FUNÇÃO E UM JOB QUE SÃO UTILIZADOS PARA REALIZAR A DOCUMENTAÇÃO 	*
* DE UM	SERVIDOR SQL Server 2000.																		*
* 																										*
* Ele também cria um Job que faz o seguinte:															*
* 1. Executa a documentação diariamente as 17:00hs														*
* 2. Salva os script no caminho C:\temp\doc_servidor													*
* 3. Cria o arquivo de output "Documenta_Servidor.log" no caminho C:\temp\doc_servidor					*
*																										*
* Por default todas as procedures (exceto a proc sp_hexadecimal que DEVE ser criada na MASTER)			*
* e função são criadas na base de dados PUBS. No entanto, as mesmas podem ser criadas em qualquer base 	*
* de sua preferência. Basta alterar o nome da base no início do script e dentro da procedure principal.	*
*																										*
* Autor: Nilton Pinheiro																				*
* WebSite: http://www.mcdbabrasil.com.br																*
*																										*
* ATENçÃO: NÃO ESQUEÇAM DE TESTAR ESTE SCRIPT EM UM AMBIENTE DE TESTES ANTES DE COLOCÁ-LO EM PRODUÇÃO.	*
* QUALQUER PROBLEMA RESULTANTE DA EXECUÇÃO DESTE SCRIPT É DE INTEIRA RESPONSABILIDADE DO EXECUTOR.		*
*																										*
* FIQUEM A VONTADE PARA ADAPTAR ESTE SCRIPT ÀS SUAS REAIS NECESSIDADES.									*
*																										*
* Esta versão do script é suportado apenas no SQL SERVER 2000 (qualquer edição)							*
* para obter uma versão compatível com SQL SERVER 2005, visite o website acima.							*
********************************************************************************************************/

-- Se pretende criar as objetos em uma banco diferente de "Pubs", substitua este pelo nome 
-- do banco onde pretende criar. Faça um "Find and Replace - Ctrl+H".

-- Exemplo de execução
--====================
-- Salva todos os scripts de documentação no caminho C:\temp\doc_servidor
--		EXEC master..usp_docservidor 'C:\temp\doc_servidor' 
-- Executada documentação apenas dos logins
--		EXEC Pubs..usp_doclogins 'C:\temp\doc_servidor' 
-- Executada documentação apenas dos usuários existentes nos DBs
--		EXEC Pubs..usp_docusers 'C:\temp\doc_servidor' 

USE master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docservidor]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[usp_docservidor]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_doclogins]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[usp_doclogins]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docusers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[usp_docusers]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docdbs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_docdbs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docdevices]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_docdevices]
GO
if exists (select * from dbo.sysobjects where name = 'fn_substchar')
	DROP FUNCTION [dbo].[fn_substchar]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docjobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_docjobs]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_doclinkedsrv]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_doclinkedsrv]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_docconfig]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_docconfig]
GO
-- Exclui o job caso exista
IF EXISTS (SELECT job_id FROM   msdb.dbo.sysjobs WHERE name = N'Documenta_Servidor')
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Documenta_Servidor' 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_doclogins 				  					*
* Proposito : Documenta Logins do Servidor, é chamada pela		*
* procedure usp_docservidor mas também pode ser usada			*
* separadamente como no exemplo abaixo.							*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_doclogins 'C:\temp\doc_servidor'			*
****************************************************************/
CREATE proc dbo.usp_doclogins
@caminho	varchar(255)
AS
SET NOCOUNT ON
--> Declaracao de Variaveis
declare @str		varchar(2000)
declare @tmpstr  	varchar (500)
declare @err 		int
declare @name    	sysname
declare @xstatus 	int
declare @binpwd  	varbinary (500)
declare @txtpwd  	varchar (500)

--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	TRUNCATE TABLE tempdb..tbdocaux
ELSE
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))
--select * from sysobjects order by crdate desc
--> Inicio do Processo de Documentacao
print '2.1. Inicio do Processo de Documentação do Logins' 
INSERT INTO tempdb..tbdocaux(cmd) values('-- Login do Servidor: ' + @@SERVERNAME)
INSERT INTO tempdb..tbdocaux(cmd) values(' ')
INSERT INTO tempdb..tbdocaux(cmd) values('DECLARE @pwd varbinary  (500)')
INSERT INTO tempdb..tbdocaux(cmd) values(' ')

DECLARE login_curs CURSOR FOR 
SELECT name, xstatus, password FROM master..sysxlogins 
WHERE name <> 'sa' 
AND name <> 'BUILTIN\Administrators'
AND srvid IS NULL

OPEN login_curs 
FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'Não existe logins para ser documentado!'
  INSERT INTO tempdb..tbdocaux(cmd) VALUES('Não existe logins para ser documentado!')
END

WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)	
	insert into tempdb..tbdocaux(cmd) values('-- Cria login ' + @name)	
  BEGIN
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
      END
      ELSE BEGIN -- NT login has access
	SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar (500), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary (500), ' + @txtpwd + ')'
	
	insert into tempdb..tbdocaux(cmd) values(@tmpstr)
        
	SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
    END

    insert into tempdb..tbdocaux(cmd) values( @tmpstr )
    insert into tempdb..tbdocaux(cmd) values(' ')

  END
  FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd
  END
  CLOSE login_curs 
  DEALLOCATE login_curs 
 
--> Gera o Arquivo texto 
print '2.1. Gera Arquivo Texto com os Logins'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\1_Logins_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\Logins.err'
      exec @err = master..xp_cmdshell @str, no_output

print '2.2. Verifica se ocorreu erro no BCP do Logins'
if @err =  0
begin	
	set @str = 'findstr -i Error '+@caminho+'\Logins.err"'
	exec @err = master..xp_cmdshell @str, no_output
	if @err = 0
	begin
		RAISERROR ('Ocorreu erro no BCP da documenração dos Logins',16, 1)WITH LOG
		goto erro	
	end
	else
	begin
		goto sucesso
	end				
end
else
begin	
	RAISERROR ('Ocorreu erro na documenração dos Logins',16, 1)WITH LOG
	goto erro
end
sucesso:    	
return(0)
erro:
return(1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE procedure dbo.usp_docusers
@caminho	varchar(255)
/****************************************************************
* Procedure : usp_docusers 				  	*
* Proposito : Documenta os usuários de cada database, é chamada *
* pela procedure usp_docservidor mas também pode ser usada	*
* separadamente como no exemplo abaixo.				*
* 								*
* Criada por default no Pubs					*
* Ex.: exec Pubs..usp_docusers 'C:\temp\doc_servidor'		*
****************************************************************/
AS
SET NOCOUNT ON

--> Declara variaveis 
declare @nomebanco 	varchar(30)
declare @str 		varchar(500)
declare @grupo		varchar(100)
declare @usuario	varchar(500)
declare @qtdusr		int
declare @totalusr	int
declare @err		int

--> Atribui valor as variaveis 
set @qtdusr   	= 0
set @totalusr 	= 0
set @err 	= 0

--> Inicio do processo 
print '3.1. Inicio do Processo de Documentação dos usuários'

--> Cria as tabelas temporarias
create table #tbAux( ColAux varchar(255) null )

--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	truncate table tempdb..tbdocaux
else
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

--> Seleciona o primeiro banco
   select  @nomebanco = min( name ) from master..sysdatabases 
   where name not in ('tempdb')
   while @nomebanco is not null
   begin
       --> Insere o nome do banco
       insert tempdb..tbdocaux(cmd ) values ('-- Usuarios para a base : ' + @nomebanco)	
       insert tempdb..tbdocaux(cmd ) values ('')	
       select @str = 'USE ' + @nomebanco
       insert tempdb..tbdocaux(cmd ) values ( @str )  
       insert tempdb..tbdocaux(cmd ) values ( 'GO' ) 
        
       --> Seleciona os grupos 
       delete #tbAux
       set @str = 'insert into #tbAux( ColAux ) ' 
       set @str = @str + 'select name from '+@nomebanco+'..sysusers where uid = gid and gid <> 0'
       exec( @str ) 
     
       select  @grupo = min( ColAux ) from #tbAux
       while @grupo is not null        
           begin 
           --> Insere comando para inclusao dos grupos 
           insert tempdb..tbdocaux(cmd ) values ( 'exec sp_addrole '+@grupo ) 
           insert tempdb..tbdocaux(cmd ) values ( 'GO' ) 
           select  @grupo = min( ColAux ) from #tbAux where ColAux > @grupo       
        end 

       --> Inclui usuario no banco 
       delete #tbAux
/*
       set @str = 'select quotename(isnull(lo.loginname,usu.name))+'',''+quotename(usu.name) from  '+@nomebanco+'..sysusers usu '
       set @str = @str + 'left outer join master.dbo.syslogins  lo on usu.sid = lo.sid where usu.uid > 2 and usu.status <> 0'
*/
       set @str = 'select quotename(isnull(lo.loginname,usu.name)) COLLATE SQL_Latin1_General_CP1_CI_AI +'',''+ quotename(usu.name) COLLATE SQL_Latin1_General_CP1_CI_AI from  '+@nomebanco+'..sysusers usu '
       set @str = @str + 'left outer join master.dbo.syslogins  lo on usu.sid = lo.sid where usu.uid > 2 and usu.status <> 0'

       insert into #tbAux( ColAux ) exec( @str ) 
     
       select  @usuario = min( ColAux ) from #tbAux
       while @usuario is not null        
           begin 
           --> Insere comando para conceder permissao de acesso no banco
           insert tempdb..tbdocaux(cmd ) values ( 'exec sp_grantdbaccess '+@usuario ) 
           insert tempdb..tbdocaux(cmd ) values ( 'GO' ) 
	   select @qtdusr = @qtdusr + 1
           select  @usuario = min( ColAux ) from #tbAux where ColAux > @usuario       
        end 

        --> Seleciona/Inclui linha de comando dos usuarios  
        delete #tbAux
/*
        set @str = 'select  quotename(usg.name)+'', ''+quotename(usu.name) from '+@nomebanco+'..sysusers usu left outer join ('+@nomebanco+'..sysmembers mem inner join '+@nomebanco+'..sysusers usg '
        set @str = @str + 'on mem.groupuid = usg.uid) on usu.uid = mem.memberuid where usg.uid is not null and usu.uid > 1'
*/
        set @str = 'select  quotename(usg.name) COLLATE SQL_Latin1_General_CP1_CI_AI +'', ''+quotename(usu.name) COLLATE SQL_Latin1_General_CP1_CI_AI from '+@nomebanco+'..sysusers usu left outer join ('+@nomebanco+'..sysmembers mem inner join '+@nomebanco+'..sysusers usg '
        set @str = @str + 'on mem.groupuid = usg.uid) on usu.uid = mem.memberuid where usg.uid is not null and usu.uid > 1'

        insert into #tbAux( ColAux ) exec( @str ) 

        select  @usuario = min( ColAux ) from #tbAux
        while @usuario is not null        
           begin 
           --> Insere linha de comando para inclusao de usuarios
           insert tempdb..tbdocaux(cmd ) values (  'exec sp_addrolemember '+@usuario ) 
           insert tempdb..tbdocaux(cmd ) values (  'GO' ) 
           select  @usuario = min( ColAux ) from #tbAux where ColAux > @usuario       
        end 

        insert tempdb..tbdocaux(cmd ) values ('---------------------------------------------------------------')  
        insert tempdb..tbdocaux(cmd ) values ('-- Total de Usuarios para a base ' + @nomebanco + ': '+ convert(varchar(6), @qtdusr ))
        insert tempdb..tbdocaux(cmd ) values ('')  
        insert tempdb..tbdocaux(cmd ) values ('')  

        select @totalusr = @totalusr + @qtdusr
        select @qtdusr = 0
        select  @nomebanco = min( name ) from master..sysdatabases 
        where name not in ('tempdb')
        and   name > @nomebanco

   	end
   insert tempdb..tbdocaux(cmd ) values ( '-- Quantidade Total de Usuarios do Servidor ' + 
                                                  substring(@@servername,1,20)+ ': ' + 
                                                  convert(char(5), @totalusr) )
--> Gera o Arquivo texto 
print '3.2. Gera arquivo texto com os usuários'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\4_Users_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+' -e'+@caminho+'\Users.err'

      exec @err = master..xp_cmdshell @str, no_output
       
print '3.3. Verifica se ocorreu erro no BCP dos usuários'
	if @err =  0
	begin	
		set @str = 'findstr -i Error '+@caminho+'\Users.err"'
		exec @err = master..xp_cmdshell @str, no_output
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Usuários',16, 1)WITH LOG
  			goto erro
		end
		else
		begin
			goto sucesso
		end				
	end
	else
	begin	
	 	RAISERROR ('Ocorreu erro na documenração dos Usuários',16, 1)WITH LOG
		goto erro
	end
sucesso:    	
drop table #tbAux
return(0)
erro:
drop table #tbAux
return(1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_docdbs 				  						*
* Proposito : Gera script de attach para todos os database, é	*
* chamada pela procedure usp_docservidor mas também pode ser	*
* usada separadamente como no exemplo abaixo.					*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_docdbs 'C:\temp\doc_servidor'   			*
****************************************************************/
CREATE procedure dbo.usp_docdbs
@caminho	varchar(255)
AS
SET NOCOUNT ON
--> Declaracao de Variaveis
declare @cont 		int
declare @banco 		sysname
declare @str 		varchar(1000)
declare @err		int
declare @bco 		int
declare @def 		int
declare @usuario	sysname
declare @fileid as varchar (200)
declare @filename as varchar (200)


--> Cria as tabelas temporarias
create table #tbAux( FileId smallint, FileName  nvarchar(520) )

--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	TRUNCATE TABLE tempdb..tbdocaux
ELSE
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

--> Inicio do Processo de Documentacao dos Databases
print '4.1. Inicio do Processo de Attach dos Databases' 
select @cont = min(dbid) from master..sysdatabases where name  not in ('master','model','msdb','tempdb','distribution','Pubs')
while  @cont is not null
begin
	set @banco = convert(sysname, db_name(@cont))
	set @str =  '-- Attach do database: '+@banco+' (dbid '+ltrim(str(db_id(@banco)))+')'
	insert tempdb..tbdocaux values(@str)	
	set @str = 'select fileid,filename from '+@banco+'..sysfiles'
	insert into #tbAux exec (@str)
		set @str = 'sp_attach_db '+ @banco +','
		SET rowcount 0 
		SET rowcount 1 
		SELECT @fileid= FileId, @filename= FileName FROM #tbAux 
		while @@rowcount <> 0 
		BEGIN 
		    SET rowcount 0 
		    SET @str =  @str +'''' + RTRIM(@filename)
	            SET @str =  @str + ''',
'
	            DELETE #tbAux WHERE FileId=@fileid
	            SET rowcount 1 
	            SELECT @fileid= FileId, @filename= FileName FROM #tbAux 
	        END
	        SET rowcount 0
		SET @str= LEFT (@str, len(RTRIM(@str))-3)
		SET @str= @str + '
GO'
		INSERT INTO tempdb..tbdocaux Values (@str)
	select @cont = min(dbid) from master..sysdatabases where name not in ('master','model','msdb','tempdb','distribution','Pubs')
	and dbid > @cont
	truncate table #tbAux
END

-- Gera o Arquivo texto 
print '4.2. Gera arquivo texto com attach dos Databases'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\3_Attach_Databases_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\Attach_Databases.err'
      exec @err = master..xp_cmdshell @str, no_output
       
print '4.3. Verifica se ocorreu erro no BCP dos Databases'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\Attach_Databases.err'''
		exec @err = master..xp_cmdshell @str, no_output
	
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Databases',16, 1)WITH LOG
			set @bco = 1
		end
		else
		begin
			--Sucesso 		
			set @bco = 0
		end				
	end
	else
	begin
	 	RAISERROR ('Ocorreu erro na documenração dos Databases',16, 1)WITH LOG
		set @bco = 1
	end

--> Inicio do Processo de Detach dos databases
print '4.1.1 Inicio do Processo de Detach dos Databases' 
truncate table tempdb..tbdocaux

insert tempdb..tbdocaux values('/*==================================================================')
insert tempdb..tbdocaux values('UTILIZE ESTE SCRIPT CASO DESEJE DESATACHAR OS DATABASES DO SERVIDOR.')
insert tempdb..tbdocaux values('POR EXEMPLO, ANTES DE EXECUTAR UM UPGRADE IN-PLACE OU COPIAR OS')
insert tempdb..tbdocaux values('ARQUIVOS DOS BANCOS DE DADOS PARA UM NOVO SERVIDOR.')
insert tempdb..tbdocaux values('====================================================================*/')

select @cont = min(dbid) from master..sysdatabases where name not in ('master','model','msdb','tempdb','distribution','Pubs')
while  @cont is not null
begin
	set @banco = convert(sysname, db_name(@cont))
	set @str =  '-- Detach do database: '+@banco+' (dbid '+ltrim(str(db_id(@banco)))+')'
	insert tempdb..tbdocaux values(@str)	
		set @str = 'sp_detach_db '''+ @banco +''''
		SET @str= @str + '
GO'
		INSERT INTO tempdb..tbdocaux Values (@str)
	select @cont = min(dbid) from master..sysdatabases where name not in ('master','model','msdb','tempdb','distribution','Pubs')
	and dbid > @cont
	truncate table #tbAux
END

-- Gera o Arquivo texto 
print '4.2.1 Gera arquivo texto com Detach dos Databases'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\2_Detach_Databases_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\Detach_Databases.err'
      exec @err = master..xp_cmdshell @str, no_output
       
print '4.3. Verifica se ocorreu erro no BCP dos Databases'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\Detach_Databases.err'''
		exec @err = master..xp_cmdshell @str, no_output
	
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Databases',16, 1)WITH LOG
			set @bco = 1
		end
		else
		begin
			--Sucesso 		
			set @bco = 0
		end				
	end
	else
	begin
	 	RAISERROR ('Ocorreu erro na documenração dos Databases',16, 1)WITH LOG
		set @bco = 1
	end

--> Inicio do Processo de Documentacao dos DEFAULT Databases
print '4.4. Inicio do Processo de Documentação do Default Database dos Usuarios' 
TRUNCATE TABLE tempdb..tbdocaux 
SELECT name,dbid INTO tempdb..tbdoclogins from master..sysxlogins where name is not null
SELECT @usuario = min(name) FROM tempdb..tbdoclogins
while  @usuario is not null
begin
	select @banco = db_name(dbid) from tempdb..tbdoclogins where name = @usuario
	set @str =  'EXEC sp_defaultdb '''+@usuario+''','''+@banco+''''
	insert tempdb..tbdocaux values(@str)
	insert tempdb..tbdocaux values('GO')

	SELECT @usuario = min(name) FROM tempdb..tbdoclogins WHERE name > @usuario	
END
DROP TABLE tempdb..tbdoclogins

print '4.5. Gera arquivo texto dos Default Databases'
	set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\5_Defaultdb_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      	set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\Defaultdb.err'
      	exec @err = master..xp_cmdshell @str, no_output

print '4.6. Verifica se ocorreu erro no BCP dos Default Databases'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\Defaultdb.err'''
		exec @err = master..xp_cmdshell @str, no_output
	
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documentação dos Default Databases',16, 1)WITH LOG
			set @def = 1
           	end
		else
		begin
			-- Sucesso
			set @def = 0
		end
	end
	else	
	begin			
	 	RAISERROR ('Ocorreu erro na documenração dos Default Databases',16, 1)WITH LOG
		set @def = 1
	end

if @bco = 1 or @def = 1
	return(1)
else
begin
	return(0)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_docdevices 				  					*
* Proposito : Gera script dos Devices de Backup existentes no	*
* servidor. É chamada pela procedure usp_docservidor mas também	*
* pode ser usada separadamente como no exemplo abaixo.			*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_docdevices 'C:\temp\doc_servidor'			*
****************************************************************/
CREATE procedure dbo.usp_docdevices
@caminho	varchar(255)
AS 
SET NOCOUNT ON
--> Declaracao de Variaveis
declare @nomefis 	varchar(255)
declare @str 		varchar(255)
declare @dev 		varchar(100)
declare @err		int

print '5.1 Inicio do Processo de Documentação dos Devices de Backup'
--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	truncate table tempdb..tbdocaux
else
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

select @dev = min(name) from master..sysdevices where status = 16
while @dev is not null
begin 
 	select @nomefis = phyname from master..sysdevices where name = @dev
	set @str = 'sp_addumpdevice ''disk'',''' + @dev + ''','''+ @nomefis + '''' 
        insert into tempdb..tbdocaux(cmd) values(@str)
        insert into tempdb..tbdocaux(cmd) values ('GO')
	select @dev = min(name) from master..sysdevices where status = 16 and name > @dev
end

--> Gera o Arquivo texto 
print '5.2. Gera arquivo texto com os Devices de Backup'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\6_DevicesBackup_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\DevicesBackup.err'

      exec @err = master..xp_cmdshell @str, no_output
       
print '5.3. Verifica se ocorreu erro no BCP dos Devices de Backup'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\DevicesBackup.err'''
		exec @err = master..xp_cmdshell @str, no_output
	
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Devices de Backup',16, 1)WITH LOG
  			goto erro
		end
	end
	else
	begin
	 	RAISERROR ('Ocorreu erro na documenração dos Device de Backup',16, 1)WITH LOG
		goto erro
	end
return(0)
erro:
return(1)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.fn_substchar (@string nvarchar(4000))
RETURNS nvarchar(4000)
AS
BEGIN
	declare @str  nvarchar(4000)
	declare @PosIni int

	if charindex(char(39),@string,1) > 0 
	begin
		set @PosIni = 1
		while @PosIni <> 0
		begin
			select @string = substring(@string,1,charindex(char(39),@string,1)-1)+'"'+substring(@string,charindex(char(39),@string,1)+1,len(@string)-charindex(char(39),@string,1))
			select @PosIni = charindex(char(39),@string,1)
		end		
	end
	RETURN ltrim(rtrim((@string)))
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_docjobs 				  						*
* Proposito : Gera script de todos os jobs do servidor			*
* É chamada pela procedure usp_docservidor mas também pode ser	*
* usada separadamente como no exemplo abaixo.					*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_docjobs 'C:\temp\doc_servidor'			*
****************************************************************/
CREATE procedure dbo.usp_docjobs
@caminho	varchar(255)
AS
SET NOCOUNT ON

--> Declara variaveis 
declare @job		char(36)
declare @str 		nvarchar(4000)
declare @err		int
declare @steps		int
declare @sched		int
declare @login 		varchar(20)
declare @jobname	varchar(200)

--> Atribui valor as variaveis 
set @err 	= 0

--> Inicio do processo 
print '6.1. Inicio do Processo de documentação dos Jobs'
--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	truncate table tempdb..tbdocaux
else
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

insert tempdb..tbdocaux(cmd) values( 'Use MSDB
GO' ) 
 
--> Seleciona o primeiro banco
   select  @job = min( cast(job_id as varchar(36)) ) from msdb..sysjobs
   while @job is not null
   begin
	select @login = lg.name from msdb..sysjobs jb join master..syslogins lg on jb.owner_sid = lg.sid
		where jb.job_id = @job
	select  @jobname = dbo.fn_substchar(name) from msdb..sysjobs where job_id = @job

	select @str = 'sp_add_job  @job_name = '+char(39)+@jobname+char(39)+',
		 @enabled =  '+cast(enabled as varchar(10))+',
		 @description =  '+char(39)+description+char(39)+',
		 @start_step_id =  '+cast(start_step_id as varchar(10)) +',
		 @category_id =  '+cast(category_id as varchar(10))+',
		 @owner_login_name =  '+char(39)+@login+char(39)+',
		 @notify_level_eventlog =  '+cast(notify_level_eventlog as varchar(5))+',
		 @notify_level_email =  '+cast(notify_level_email as varchar(5))+',
		 @notify_level_netsend =  '+cast(notify_level_netsend as varchar(5))+',
		 @notify_level_page =  '+cast(notify_level_page as varchar(5)) +',
		 @delete_level =  '+cast(delete_level as varchar(5))
	from msdb..sysjobs where job_id = @job

      	insert into tempdb..tbdocaux(cmd) values( @str+'
GO' ) 

	-->  Adiciona os steps do Job	
	select @steps = min(step_id) from msdb.dbo.sysjobsteps where job_id = @job 
	while @steps is not null
	begin
		select  @str = 'sp_add_jobstep  @job_name = '+char(39)+@jobname+char(39)+',
			 @step_id =  '+cast(step_id  as varchar(5))+',
			 @step_name =  '+char(39)+step_name+char(39)+', 
			 @subsystem = '+char(39)+subsystem+char(39)+',
			 @command =  '+char(39)+dbo.fn_substchar(command)+char(39)+ 
			 CASE 
			    WHEN server IS not NULL THEN ',
			 @additional_parameters =  '+char(39)+cast(additional_parameters as varchar(2000))+char(39)
			    ELSE ''
			 END+',
			 @cmdexec_success_code =  '+cast(cmdexec_success_code as varchar(5))+', 
			 @on_success_action = '+cast(on_success_action  as varchar(5))+',
			 @on_success_step_id =  '+cast(on_success_step_id as varchar(5))+',
			 @on_fail_action =  '+cast(on_fail_action   as varchar(5))+
			 CASE 
			    WHEN server IS not NULL THEN ',
			 @server =  '+server
			    ELSE ''
			 END+',
			 @database_name =  '+char(39)+database_name+char(39)+
			 CASE 
			    WHEN server IS not NULL THEN ',
			 @database_user_name =  '+database_user_name
			    ELSE ''
			 END+',
			 @retry_attempts =  '+cast(retry_attempts as varchar(5))+',
			 @retry_interval =  '+cast(retry_interval as varchar(5))+',
			 @os_run_priority = '+cast(os_run_priority as varchar(5))+',
			 @on_fail_step_id = '+cast(on_fail_step_id as varchar(5))+
			 CASE 
			    WHEN output_file_name IS not NULL THEN ',
			 @output_file_name = '''+output_file_name +''''
			    ELSE ''
			 END+',
			 @flags = '+cast( flags as varchar(5))
			 from msdb..sysjobsteps
			 where @steps = step_id and job_id = @job

        	insert into tempdb..tbdocaux(cmd) values( @str+'
GO' ) 
		select @steps = min(step_id) from msdb..sysjobsteps where job_id = @job  and  step_id  > @steps
	end

	-->  Adiciona os schedules do Job
	select  @sched = min(schedule_id) from msdb..sysjobschedules where job_id = @job
	
	while @sched is not null
	begin
		select @str = 'sp_add_jobschedule  @job_name =  '+char(39)+@jobname+char(39)+', 
			@name =  '+char(39)+name+char(39)+',
			@enabled = '+cast( enabled  as char(01))+',
			@freq_type =  '+cast( freq_type   as varchar(10))+',
			@freq_interval = '+cast(freq_interval as varchar(10))+',
			@freq_subday_type =  '+cast(freq_subday_type as varchar(05))+',
			@freq_subday_interval = '+cast(freq_subday_interval as varchar(05))+',
			@freq_relative_interval = '+cast(freq_relative_interval as varchar(05))+',
			@freq_recurrence_factor = '+ cast(freq_recurrence_factor as varchar(05))+',
			@active_start_date = '+char(39)+cast(active_start_date as varchar(08))+char(39)+',
			@active_end_date = '+char(39)+cast(active_end_date as varchar(08))+char(39)+',
			@active_start_time = '+char(39)+cast(active_start_time as varchar(08))+char(39)+',
			@active_end_time = '+char(39)+cast(active_end_time as varchar(08))+char(39)
		from msdb..sysjobschedules where @sched = schedule_id

        	insert into tempdb..tbdocaux(cmd) values( @str+'
GO' ) 
		select  @sched = min(schedule_id) from msdb..sysjobschedules where job_id = @job and  schedule_id  > @sched

	end
	select @str = 'sp_add_jobserver  @job_name = '+char(39)+@jobname+char(39)+',
	@server_name =  '+char(39)+@@servername+char(39)
       	insert tempdb..tbdocaux(cmd) values( @str+'
GO' ) 
        select  @job = min( cast(job_id as char(36)) ) from msdb..sysjobs
        where cast(job_id as char(36))  > @job
    end

print '6.2. Gera arquivo texto com os Jobs'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\7_Jobs_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\Jobs.err'
      exec @err = master..xp_cmdshell @str, no_output
       
print '6.3. Verificando se ocorreu ERRO no BCP dos Jobs'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\Jobs.err'''
		exec @err = master..xp_cmdshell @str, no_output
		if @err = 0
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Jobs',16, 1)WITH LOG
			goto erro	
		end
		else
		begin
			-- Sucesso
			goto sucesso
		end
	end
	else
	begin	
	 	RAISERROR ('Ocorreu erro na documenração dos Jobs',16, 1)WITH LOG
		goto erro
	end
sucesso:    	
return(0)
erro:
return(1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_doclinkedsrv 				  					*
* Proposito : Gera script de todos os linked servers do servidor*
* É chamada pela procedure usp_docservidor mas também pode ser	*
* usada separadamente como no exemplo abaixo.					*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_doclinkedsrv 'C:\temp\doc_servidor'		*
****************************************************************/
CREATE procedure dbo.usp_doclinkedsrv
@caminho	varchar(255)
AS
SET NOCOUNT ON

--> Declara variaveis 
declare @srv		int
declare @str 		nvarchar(4000)
declare @err		int

--> Atribui valor as variaveis 
set @err 	= 0

--> Inicio do processo 
print '7.1. Inicio do Processo de documentação dos Linked Servers'

--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	truncate table tempdb..tbdocaux
else
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

insert tempdb..tbdocaux(cmd) values( 'USE MASTER'+'
GO' ) 
 
--> Seleciona o primeiro banco
   select  @srv = min( srvid ) from master.dbo.sysservers where srvname <> @@servername
   while @srv is not null
   begin
		IF (SELECT srvproduct FROM master.dbo.sysservers WHERE srvid=@srv)='SQL Server'
			BEGIN
				SELECT @str = 'EXEC sp_addlinkedserver @server = '''+srvname+''''
				FROM master.dbo.sysservers where srvname <> @@servername and srvid = @srv
			END
		ELSE
			BEGIN
				SELECT @str = 'EXEC sp_addlinkedserver @server =  '+char(39)+srvname+char(39)+',
				@srvproduct =  '+char(39)+srvproduct+char(39)+',
				@provider =  '+char(39)+providername+char(39)+
				CASE 
				WHEN datasource IS not NULL THEN ',
				@datasrc =  '+char(39)+datasource+char(39)
				ELSE ''
				END+
				CASE 
				WHEN location IS not NULL THEN ',
				@location =  '+char(39)+location+char(39)
				ELSE ''
				END+
				CASE 
				WHEN providerstring IS not NULL THEN ',
				@provstr =  '+char(39)+providerstring+char(39)
				ELSE ''
				END+
				CASE 
				WHEN catalog IS not NULL THEN ',
				@catalog =  '+char(39)+catalog+char(39)
				ELSE ''
			END
		FROM master.dbo.sysservers where srvname <> @@servername and srvid = @srv
	END
	INSERT tempdb..tbdocaux(cmd) values( @str+'
GO') 

	select  @srv = min( srvid ) from master.dbo.sysservers where srvname <> @@servername
    and srvid > @srv

    end

	set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\8_LinkedServers_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
	set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\LinkedServers.err'
	exec @err = master..xp_cmdshell @str, no_output
      
	print '7.2. Verifica se ocorreu erro no BCP dos Linked Servers'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\LinkedServers.err'''
		exec @err = master..xp_cmdshell @str, no_output
		if @err = 0
		begin
 		 	RAISERROR ('Ocorreu erro no BCP da documenração dos Linked Servers',16, 1)WITH LOG
			goto erro	
		end
		else
		begin
			-- Sucesso
			goto sucesso
		end
	end
	else
	begin	
	 	RAISERROR ('Ocorreu erro da documenração dos Linked Servers',16, 1)WITH LOG
		goto erro
	end
sucesso:    	
return(0)
erro:
return(1)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/****************************************************************
* Procedure : usp_docconfig 				  					*
* Proposito : Gera arquivo texto com o resultado da execução	*
* da sp_configure												*
* 																*
* É chamada pela procedure usp_docservidor mas também			*
* pode ser usada separadamente como no exemplo abaixo.			*
* 																*
* Criada por default no Pubs									*
* Ex.: exec Pubs..usp_docconfig 'C:\temp\doc_servidor'		*
****************************************************************/
CREATE procedure dbo.usp_docconfig
@caminho	varchar(255)
AS 
SET NOCOUNT ON

-- Declaracao de Variaveis
declare @str 		nvarchar(4000)
declare @name		varchar (40)
declare @minimum 	int
declare @maximum 	int
declare @config 	int
declare @run	 	int
declare @err		int

print '8.1 Inicio do Processo de Documentação da sp_configure'

--> Limpa Tabela
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	truncate table tempdb..tbdocaux
else
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

--> Insere linha 
insert tempdb..tbdocaux(cmd ) values ('/* ')
insert tempdb..tbdocaux(cmd ) values ('-- Configuracao atual da sp_configure')
insert tempdb..tbdocaux(cmd ) values (' ')
insert tempdb..tbdocaux(cmd ) values ('name                                minimum     maximum     config_value run_value')
insert tempdb..tbdocaux(cmd ) values ('----------------------------------- ----------- ----------- ------------ ----------- ')

-- Cria tabela auxiliar
IF exists (select name from tempdb..sysobjects where name='tbspconfigure' and xtype='U')
DROP TABLE tempdb..tbspconfigure

CREATE TABLE tempdb..tbspconfigure(names varchar(40), minimum int,	maximum int,
config_value int,run_value int)

-- Carrega a tabela com o conteúdo da sp_configure
INSERT INTO tempdb..tbspconfigure exec sp_configure

-- Verifica se show advanced options = 0. Se for muda para 1
SELECT @name=names, @run=run_value FROM tempdb..tbspconfigure WHERE names='show advanced options'
IF @run=0
BEGIN
	EXEC sp_configure 'show advanced options',1
	RECONFIGURE WITH OVERRIDE

	-- Trunca e carrega a tabela novamente
	DELETE FROM tempdb..tbspconfigure 
	INSERT INTO tempdb..tbspconfigure exec sp_configure	
END

SET rowcount 0 
SET rowcount 1 
SELECT @name=names,@minimum=minimum,@maximum=maximum,@config=config_value, @run=run_value
FROM tempdb..tbspconfigure
while @@rowcount <> 0 
BEGIN 
     SET rowcount 0 
     SET @str =  CONVERT(CHAR(36),@name) + convert(char(12),@minimum) + 	
     convert(char(12),@maximum)+ convert(char(13),@config)+ convert(char(13),@run)
     INSERT tempdb..tbdocaux values(@str)	
     DELETE tempdb..tbspconfigure WHERE names=@name
     set rowcount 1 
     SELECT @name=names,@minimum=minimum,@maximum=maximum,@config=config_value, @run=run_value
     FROM tempdb..tbspconfigure
end 
set rowcount 0 
insert tempdb..tbdocaux(cmd ) values ('*/ ')
insert tempdb..tbdocaux(cmd ) values (' ')
insert tempdb..tbdocaux(cmd ) values ('-- Script para configuracao da sp_configure (valores para run_value)')
insert tempdb..tbdocaux(cmd ) values (' ')

-- Recarrega a tabela com o conteúdo da sp_configure
INSERT INTO tempdb..tbspconfigure exec sp_configure

SET rowcount 0 
SET rowcount 1 
SELECT @name=names, @run=run_value FROM tempdb..tbspconfigure order by names
while @@rowcount <> 0 
BEGIN 
     SET rowcount 0 
     SET @str =  'sp_configure '''+ @name + ''', '+ convert(varchar(10),@run)
     INSERT tempdb..tbdocaux values(@str)	
     INSERT tempdb..tbdocaux values('GO')	
     DELETE tempdb..tbspconfigure WHERE names=@name
     set rowcount 1 
     SELECT @name=names,@run=run_value FROM tempdb..tbspconfigure order by names
end 
set rowcount 0 
DROP TABLE tempdb..tbspconfigure

-- Gera o Arquivo texto 
print '8.2. Gera arquivo texto com o resultado da sp_configure'
      set @str = 'bcp tempdb..tbdocaux out '+@caminho+'\9_sp_configure_'+datename(dw,getdate())+ '_'+ REPLACE(@@servername,'\','')
      set @str = @str +'.sql -c -T -S'+@@servername+ ' -e'+@caminho+'\sp_configure.err'

      exec @err = master..xp_cmdshell @str, no_output
       
print '8.3. Verifica se ocorreu erro no BCP da sp_configure'
	if @err =  0
	begin
		set @str = 'findstr -i Error '+@caminho+'\sp_configure.err'''
		exec @err = master..xp_cmdshell @str, no_output
	
		if @err = 0	
		begin
 		 	RAISERROR ('Ocorreu erro no BCP de documenração da sp_configure',16, 1)WITH LOG
  			goto erro
		end
	end
	else
	begin
	 	RAISERROR ('Ocorreu erro na documenração da sp_configure',16, 1)WITH LOG
		goto erro
	end

return(0)
erro:
return(1)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/************************************************************************
* Procedure Principal: usp_docservidor									*
* Objetivo  : Realizar documentação do servidor							*
*             															*
* Criada por default no Pubs											*
* Ex.: Pubs..usp_docservidor 'C:\temp\doc_servidor'            	       	*
************************************************************************/
-- ATENÇÃO !!
-- Se tiver criado as procedures em uma base <> da Pubs, altere o nome da base também nas linhas abaixo.

CREATE procedure dbo.usp_docservidor
@caminho 	varchar(255) = null
AS
SET NOCOUNT ON

--> Declaracao de Variaveis
declare @lgn 		int
declare @usr 		int
declare @dbs 		int
declare @dev 		int
declare @job 		int
declare @lnk 		int
declare @spconf		int
declare @status		int

-- Verifica se o caminho informado é válido
CREATE TABLE #direxiste (file_exists bit, file_is_dir bit, parent_directory_exists bit)
INSERT INTO #direxiste EXEC master..xp_fileexist @caminho 
IF (SELECT file_is_dir FROM #direxiste) = 0
    BEGIN
	-- O diretório informado não existe
	DROP TABLE #direxiste
	RAISERROR ('O caminho informado para a documentação do servidor não existe! ',16, 1)WITH LOG	
	RETURN	
    END
ELSE
-- Cria uma tabela auxiliar no TEMPDB
IF exists (select name from tempdb..sysobjects where name='tbdocaux' and xtype='U')
	DROP TABLE tempdb..tbdocaux
ELSE
	CREATE TABLE tempdb..tbdocaux (cmd varchar(8000))

print '2. Executando Documentacao dos *** Logins ***'
exec @lgn = master.dbo.usp_doclogins @caminho

print '3. Executando Documentacao dos *** Usuários ***'
exec @usr = master.dbo.usp_docusers @caminho

print '4. Executando Documentacao dos *** Banco de Dados ***'
exec @dbs = master.dbo.usp_docdbs @caminho

print '5. Executando Documentacao dos *** Devices de Backup ***'
exec @dev = master.dbo.usp_docdevices @caminho

print '6. Executando Documentacao dos *** Jobs ***'
exec @job = master.dbo.usp_docjobs @caminho

print '7. Executando Documentacao dos *** Linked Servers ***'
exec @lnk = master.dbo.usp_doclinkedsrv @caminho

print '8. Executando Documentacao da *** sp_configure ***'
exec @spconf = master.dbo.usp_docconfig @caminho

DROP TABLE tempdb..tbdocaux 

print '9. Verifica se ocorreu erro no processo de documentação'
if @lgn = 0 and @usr = 0 and @dbs = 0 and @dev = 0 and @job = 0 and @lnk = 0 and @spconf = 0
begin
	-- Processo Realizado com Sucesso
	select @status = 0
	select @status as Sucesso_no_Processo
	PRINT ''
	PRINT'**** DOCUMENTAÇÃO DO SERVIDOR REALIZADA COM SUCESSO!! ****'
	return(@status)
end
else
begin
	-- Ocorreu Erro
	select @status = 1
	select @status as Erro_no_Processo
	PRINT ''
	PRINT '**** OCORRERAM ERROS DURANTE O PROCESSO DE DOCUMENTAÇÃO DO SERVIDOR!! ****'
	return(@status)
end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

USE master
GO
/****************************************************************
* Script para migrar logins entre servidores com SQL 2000 		*
*																*
* Obs: Esta proc é chamada pela procedure de documentação de	*
* logins (usp_doclogins)										* 
****************************************************************/

-- Esta procedure deve ser criada no banco de dados MASTER

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_hexadecimal]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_hexadecimal]
GO

CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(500),
    @hexvalue varchar(500) OUTPUT
AS
DECLARE @charvalue varchar(500)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****************************************************************
* Script para criar o job "Documenta_Servidor" que executa a	* 
* procedure de  documentação do servidor.						*
*																*
* O Job faz o seguinte:											*
* 1. Executa o job diariamente as 17:00hs						*
* 2. Salva os script no caminho C:\temp\doc_servidor			*
* 3. Cria o arquivo de output Documenta_Servidor.log no caminho	*
* C:\temp\doc_servidor											*
*																*
* Obs: Se preferir, altere o caminho para um de sua preferência	*
****************************************************************/
BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- Delete the job with the same name (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Documenta_Servidor')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
              FROM    msdb.dbo.sysjobservers 
              WHERE   (job_id = @JobID) AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import job ''Documenta_Servidor'' since there is already a multi-server job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Documenta_Servidor' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Documenta_Servidor', @owner_login_name = N'sa', @description = N'Realiza documentação do servidor', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Documenta_Servidor', @command = N'EXEC master..usp_docservidor ''C:\temp\doc_servidor''', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'C:\temp\doc_servidor\Documenta_Servidor.log', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job schedules
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Documenta_Servidor', @enabled = 1, @freq_type = 4, @active_start_date = 20050906, @active_start_time = 170000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:
	PRINT 'Um Job foi criado para executar a procedure de documentacao diariamente as 17:00hs'