/******************************************************************************/
/* BANCO DE DADOS : MSSQL */
/* VERSÃO DO BANCO DE DADOS : 7.0/2000/2005 */
/* VERSÃO DA BASE DE DADOS : FULL */
/* DATA DE CRIAÇÃO : 29/03/2007 */
/* ULTIMA ATUALIZAÇÃO : 15/12/2009 */
/* ANALISTA RESPONSÁVEL : FABIO FUMIO */
/* OBJETIVO : GERAR RELACIONAMENTOS ENTRE AS TABELAS */
/******************************************************************************/
/*********************************************************/
/* TABELA :ZSYSCONSTRAINTS */
/* SP GERADORA DE SCRIPT (DROP) :SP_GERASCRIPTDROPFK */
/* SP GERADORA DE SCRIPT (ADD) :SP_GERASCRIPTADDFK */
/*********************************************************/
-----------------------------------------------------------
-- CRIAÇÃO DA TABELA
-----------------------------------------------------------
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'U'
AND NAME = 'ZSYSCONSTRAINTS')
BEGIN
DROP TABLE ZSYSCONSTRAINTS
END
GO
CREATE TABLE ZSYSCONSTRAINTS (
CONSTID INT,
NOMERELAC VARCHAR(150),
TABELAMAE VARCHAR(100),
TABELAFILHA VARCHAR(100),
CAMPOTABMAE VARCHAR(255),
CAMPOTABFILHA VARCHAR(255),
VERSAOBASE VARCHAR(10))
GO
-----------------------------------------------------------
-- GERAÇÃO DOS RELACIONAMENTOS - POPULANDO TABELA
-----------------------------------------------------------
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_RELACIONAMENTOS CURSOR FOR
SELECT DADOSRELAC.NAME AS NOMERELACIONAMENTO, ORIGEM.NAME AS TABELAMAE, DESTINO.NAME AS TABELAFILHA,
CAMPOORIGEM.NAME AS CAMPOTABMAE, CAMPODESTINO.NAME AS CAMPOTABFILHA,
FOREIGNK.CONSTID, FOREIGNK.KEYNO
FROM SYSOBJECTS TABELA, -- PARA PESQUISA
SYSCONSTRAINTS RELAC, -- JOIN ENTRE A TABELA E OS DADOS DO RELACIONAMENTO,
SYSOBJECTS DADOSRELAC, -- DADOS DO RELACIONAMENTO,
SYSFOREIGNKEYS FOREIGNK, -- DADOS DA FOREIGN KEYS,
SYSOBJECTS ORIGEM, -- TABELA ORIGEM DO RELACIONAMENTO
SYSOBJECTS DESTINO, -- TABELA DESTINO DO RELACIONAMENTO
SYSCOLUMNS CAMPOORIGEM, -- CAMPO ORIGEM DO RELACIONAMENTO
SYSCOLUMNS CAMPODESTINO -- CAMPO DESTINO DO RELACIONAMENTO
WHERE TABELA.XTYPE = 'U'
AND TABELA.ID = RELAC.ID
AND RELAC.CONSTID = DADOSRELAC.ID
AND RELAC.CONSTID = FOREIGNK.CONSTID
AND FOREIGNK.FKEYID = ORIGEM.ID
AND FOREIGNK.RKEYID = DESTINO.ID
AND FOREIGNK.FKEY = CAMPOORIGEM.COLID
AND FOREIGNK.FKEYID = CAMPOORIGEM.ID
AND FOREIGNK.RKEY = CAMPODESTINO.COLID
AND FOREIGNK.RKEYID = CAMPODESTINO.ID
ORDER BY FOREIGNK.CONSTID, FOREIGNK.KEYNO
OPEN CR_RELACIONAMENTOS
FETCH NEXT FROM CR_RELACIONAMENTOS INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID, @KEYNO
SET @VERSAOBASE = (SELECT VERSAOBASE FROM GPARAMS)
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT *
FROM ZSYSCONSTRAINTS
WHERE CONSTID = @CONSTID)
BEGIN
INSERT INTO ZSYSCONSTRAINTS (CONSTID, NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, VERSAOBASE)
VALUES (@CONSTID, @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @VERSAOBASE)
END
ELSE
BEGIN
UPDATE ZSYSCONSTRAINTS SET CAMPOTABMAE = CAMPOTABMAE+', '+@CAMPOTABMAE,
CAMPOTABFILHA = CAMPOTABFILHA+', '+@CAMPOTABFILHA
WHERE CONSTID = @CONSTID
END
FETCH NEXT FROM CR_RELACIONAMENTOS INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID, @KEYNO
END
CLOSE CR_RELACIONAMENTOS
DEALLOCATE CR_RELACIONAMENTOS
GO
-----------------------------------------------------------
-- CRIAÇÃO DAS PROCEDURES DE GERAÇÃO DE SCRIPT
-----------------------------------------------------------
-- Gera Script de Remoção de Constraints
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'P'
AND NAME = 'SP_GERASCRIPTDROPFK')
BEGIN
DROP PROCEDURE SP_GERASCRIPTDROPFK
END
GO
CREATE PROCEDURE SP_GERASCRIPTDROPFK AS
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_GERADROP CURSOR FOR
SELECT NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, CONSTID, VERSAOBASE
FROM ZSYSCONSTRAINTS
ORDER BY TABELAMAE
OPEN CR_GERADROP
FETCH NEXT FROM CR_GERADROP INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- '+@NOMERELAC+' ------ '+@VERSAOBASE
PRINT 'ALTER TABLE '+@TABELAMAE+' DROP CONSTRAINT '+@NOMERELAC
PRINT 'GO'
PRINT ' '
FETCH NEXT FROM CR_GERADROP INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
END
CLOSE CR_GERADROP
DEALLOCATE CR_GERADROP
GO
-- Gera Script de Adição de Constraints
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'P'
AND NAME = 'SP_GERASCRIPTADDFK')
BEGIN
DROP PROCEDURE SP_GERASCRIPTADDFK
END
GO
CREATE PROCEDURE SP_GERASCRIPTADDFK AS
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_GERAADD CURSOR FOR
SELECT NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, CONSTID, VERSAOBASE
FROM ZSYSCONSTRAINTS
ORDER BY TABELAMAE
OPEN CR_GERAADD
FETCH NEXT FROM CR_GERAADD INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- '+@NOMERELAC+' ------ '+@VERSAOBASE
PRINT 'ALTER TABLE '+@TABELAMAE+' ADD CONSTRAINT '+@NOMERELAC+' FOREIGN KEY ('+@CAMPOTABMAE+') REFERENCES '+@TABELAFILHA+'('+@CAMPOTABFILHA+')'
PRINT 'GO'
PRINT ' '
FETCH NEXT FROM CR_GERAADD INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
END
CLOSE CR_GERAADD
DEALLOCATE CR_GERAADD
GO
-----------------------------------------------------------
-----------------------------------------------------------
/*
-- LISTA TABELAS QUE SE RELACIONAM COM A TABELA EM QUESTÃO, CONSEQUENTEMENTE NECESSITARÃO DE DE/PARA
SELECT TABELAMAE, CAMPOTABMAE
FROM ZSYSCONSTRAINTS
WHERE TABELAFILHA = 'GCCUSTO'
ORDER BY TABELAMAE
GO
-- GERA ESTRUTURA DE DE/PARA
SELECT 'UPDATE '+TABELAMAE+' SET '+SUBSTRING(CAMPOTABMAE,PATINDEX('%,%',CAMPOTABMAE)+1,LEN(CAMPOTABMAE))+' = #<CODIGO_NOVO># '+
' WHERE '+SUBSTRING(CAMPOTABMAE,1,PATINDEX('%,%',CAMPOTABMAE)-1)+' = #<COLIGADA>#'+
' AND'+SUBSTRING(CAMPOTABMAE,PATINDEX('%,%',CAMPOTABMAE)+1,LEN(CAMPOTABMAE))+' = #<CODIGO_ANTIGO>#'+CHAR(10)+'GO'
FROM ZSYSCONSTRAINTS
WHERE TABELAFILHA = 'GCCUSTO'
ORDER BY TABELAMAE
GO
*/
sábado, 29 de agosto de 2015
RM - Dias Úteis
Dias Uteis (SQL)
-DROP FUNCTION DIASUTEISMES
--CREATE FUNCTION DIASUTEISMES (@MES INT, @ANO INT, @CODCOLIGADA DCODCOLIGADA, @CHAPA DCHAPA)
--RETURNS INT
--AS BEGIN
DECLARE @CODCOLIGADA DCODCOLIGADA
DECLARE @CHAPA DCHAPA
DECLARE @MES INT
DECLARE @ANO INT
DECLARE @MESPROX INT
DECLARE @ANOPROX INT
DECLARE @DIATEMP INT
DECLARE @DATATEMP DATETIME
DECLARE @STATUSDATA DATETIME
DECLARE @FERIADO INT
DECLARE @COMPDESC INT
DECLARE @DIASUTEIS INT
DECLARE @DATASTRING VARCHAR(10)
SET @MES = 10
SET @ANO = 2003
SET @CODCOLIGADA = 1
SET @CHAPA = '931.00595'
-----------------------------------------------------------------------------------------
-- PROXIMO MÊS E ANO
-----------------------------------------------------------------------------------------
IF @MES = 12
BEGIN
SET @MESPROX = 1
SET @ANOPROX = @ANO+1
END
ELSE
BEGIN
SET @MESPROX = @MES+1
SET @ANOPROX = @ANO
END
PRINT 'MÊS E ANO PARA QUAL SERÁ COMPRADO O BENEFÍCIO'
PRINT 'PRÓXIMO MÊS: '+CAST(@MESPROX AS VARCHAR)
PRINT 'PRÓXIMO ANO: '+CAST(@ANOPROX AS VARCHAR)
PRINT '----------------------------------------'
-----------------------------------------------------------------------------------------
-- DIA COMPENSADO E DIA DE DESCANSO (SÁBADO E DOMINGO)
-----------------------------------------------------------------------------------------
-- ULTIMO DIA DO MES PROXIMO (SEJA 31, 30 OU 28)
SET @DATASTRING = (SELECT CAST(@MESPROX AS VARCHAR) + '/1/'+ CAST(@ANOPROX AS VARCHAR))
SET @DATATEMP = (SELECT DATEADD(MONTH,1,(CAST(@DATASTRING AS DATETIME))))-1
PRINT 'DATA LIMITE DA COMPRA DO BENEFÍCIO : '+ CAST(@DATATEMP AS VARCHAR)
PRINT '----------------------------------------'
-- CÁLCULO DE SÁBADOS E DOMINGOS
SET @DIATEMP = 0
SET @COMPDESC = 0
WHILE @DIATEMP < DATEPART(DD, @DATATEMP)
BEGIN
SET @DATASTRING = (SELECT CAST(@MESPROX AS VARCHAR) + '/'+ CAST(DATEPART(DD,@DIATEMP)AS VARCHAR) + '/'+CAST(@ANOPROX AS VARCHAR))
--PRINT 'DATA STRING 2: ' + @DATASTRING
SET @STATUSDATA = CAST(@DATASTRING AS DATETIME)
--PRINT 'DATA DATETIME 2: ' + CAST(@STATUSDATA AS VARCHAR)
--PRINT '----------------------------------------'
IF (DATENAME(WEEKDAY,@STATUSDATA) = 'Saturday') OR (DATENAME(WEEKDAY,@STATUSDATA) = 'Sunday')
BEGIN
SET @COMPDESC = @COMPDESC+1
END
SET @DIATEMP = @DIATEMP+1
END
-----------------------------------------------------------------------------------------
-- FERIADOS
-----------------------------------------------------------------------------------------
SET @FERIADO = 0
SET @FERIADO = (SELECT COUNT(*)
FROM GFERIADO
WHERE CODCALENDARIO = (SELECT CODCALENDARIO
FROM PSECAO
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODIGO = (SELECT CODSECAO
FROM PFUNC
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA))
AND DATEPART(MM,DIAFERIADO) = @MESPROX
AND DATEPART(YYYY,DIAFERIADO) = @ANOPROX)
-----------------------------------------------------------------------------------------
-- DIAS UTEIS
-----------------------------------------------------------------------------------------
SET @DIATEMP = 0
SET @DIATEMP = CAST(DATEPART(DD,@DATATEMP)AS INT)
PRINT 'QTD DE DIAS DO MES DE COMPRA BENEFICIO: ' + CAST(DATEPART(DD, @DATATEMP) AS VARCHAR)
PRINT 'QTD DE DIAS DE FERIADO DE COMPRA BENEFICIO: ' + CAST(@FERIADO AS VARCHAR)
PRINT 'QTD DE DIAS DE COMPENSADOS DE COMPRA DE BENEFICIO: ' + CAST(@COMPDESC AS VARCHAR)
SET @DIASUTEIS = (@DIATEMP - @FERIADO - @COMPDESC)
PRINT 'QTD DE DIAS UTEIS DO MES DE COMPRA DE BENEFICIO: ' + CAST(@DIASUTEIS AS VARCHAR)
--RETURN @DIASUTEIS
--END
-DROP FUNCTION DIASUTEISMES
--CREATE FUNCTION DIASUTEISMES (@MES INT, @ANO INT, @CODCOLIGADA DCODCOLIGADA, @CHAPA DCHAPA)
--RETURNS INT
--AS BEGIN
DECLARE @CODCOLIGADA DCODCOLIGADA
DECLARE @CHAPA DCHAPA
DECLARE @MES INT
DECLARE @ANO INT
DECLARE @MESPROX INT
DECLARE @ANOPROX INT
DECLARE @DIATEMP INT
DECLARE @DATATEMP DATETIME
DECLARE @STATUSDATA DATETIME
DECLARE @FERIADO INT
DECLARE @COMPDESC INT
DECLARE @DIASUTEIS INT
DECLARE @DATASTRING VARCHAR(10)
SET @MES = 10
SET @ANO = 2003
SET @CODCOLIGADA = 1
SET @CHAPA = '931.00595'
-----------------------------------------------------------------------------------------
-- PROXIMO MÊS E ANO
-----------------------------------------------------------------------------------------
IF @MES = 12
BEGIN
SET @MESPROX = 1
SET @ANOPROX = @ANO+1
END
ELSE
BEGIN
SET @MESPROX = @MES+1
SET @ANOPROX = @ANO
END
PRINT 'MÊS E ANO PARA QUAL SERÁ COMPRADO O BENEFÍCIO'
PRINT 'PRÓXIMO MÊS: '+CAST(@MESPROX AS VARCHAR)
PRINT 'PRÓXIMO ANO: '+CAST(@ANOPROX AS VARCHAR)
PRINT '----------------------------------------'
-----------------------------------------------------------------------------------------
-- DIA COMPENSADO E DIA DE DESCANSO (SÁBADO E DOMINGO)
-----------------------------------------------------------------------------------------
-- ULTIMO DIA DO MES PROXIMO (SEJA 31, 30 OU 28)
SET @DATASTRING = (SELECT CAST(@MESPROX AS VARCHAR) + '/1/'+ CAST(@ANOPROX AS VARCHAR))
SET @DATATEMP = (SELECT DATEADD(MONTH,1,(CAST(@DATASTRING AS DATETIME))))-1
PRINT 'DATA LIMITE DA COMPRA DO BENEFÍCIO : '+ CAST(@DATATEMP AS VARCHAR)
PRINT '----------------------------------------'
-- CÁLCULO DE SÁBADOS E DOMINGOS
SET @DIATEMP = 0
SET @COMPDESC = 0
WHILE @DIATEMP < DATEPART(DD, @DATATEMP)
BEGIN
SET @DATASTRING = (SELECT CAST(@MESPROX AS VARCHAR) + '/'+ CAST(DATEPART(DD,@DIATEMP)AS VARCHAR) + '/'+CAST(@ANOPROX AS VARCHAR))
--PRINT 'DATA STRING 2: ' + @DATASTRING
SET @STATUSDATA = CAST(@DATASTRING AS DATETIME)
--PRINT 'DATA DATETIME 2: ' + CAST(@STATUSDATA AS VARCHAR)
--PRINT '----------------------------------------'
IF (DATENAME(WEEKDAY,@STATUSDATA) = 'Saturday') OR (DATENAME(WEEKDAY,@STATUSDATA) = 'Sunday')
BEGIN
SET @COMPDESC = @COMPDESC+1
END
SET @DIATEMP = @DIATEMP+1
END
-----------------------------------------------------------------------------------------
-- FERIADOS
-----------------------------------------------------------------------------------------
SET @FERIADO = 0
SET @FERIADO = (SELECT COUNT(*)
FROM GFERIADO
WHERE CODCALENDARIO = (SELECT CODCALENDARIO
FROM PSECAO
WHERE CODCOLIGADA = @CODCOLIGADA
AND CODIGO = (SELECT CODSECAO
FROM PFUNC
WHERE CODCOLIGADA = @CODCOLIGADA
AND CHAPA = @CHAPA))
AND DATEPART(MM,DIAFERIADO) = @MESPROX
AND DATEPART(YYYY,DIAFERIADO) = @ANOPROX)
-----------------------------------------------------------------------------------------
-- DIAS UTEIS
-----------------------------------------------------------------------------------------
SET @DIATEMP = 0
SET @DIATEMP = CAST(DATEPART(DD,@DATATEMP)AS INT)
PRINT 'QTD DE DIAS DO MES DE COMPRA BENEFICIO: ' + CAST(DATEPART(DD, @DATATEMP) AS VARCHAR)
PRINT 'QTD DE DIAS DE FERIADO DE COMPRA BENEFICIO: ' + CAST(@FERIADO AS VARCHAR)
PRINT 'QTD DE DIAS DE COMPENSADOS DE COMPRA DE BENEFICIO: ' + CAST(@COMPDESC AS VARCHAR)
SET @DIASUTEIS = (@DIATEMP - @FERIADO - @COMPDESC)
PRINT 'QTD DE DIAS UTEIS DO MES DE COMPRA DE BENEFICIO: ' + CAST(@DIASUTEIS AS VARCHAR)
--RETURN @DIASUTEIS
--END
RM - Coligada Corrente
select ultimacoligada as coligadacorrente
from gusuario
where codusuario = (select username
from glogin
where computername = (select distinct hostname
from master..sysprocesses
where spid = (select @@spid))
and datepart(yyyy,logintime) = (select distinct datepart(yyyy, getdate()) from gcoligada)
and datepart(mm,logintime) = (select distinct datepart(mm, getdate()) from gcoligada)
and datepart(dd,logintime) = (select distinct datepart(dd, getdate()) from gcoligada))
from gusuario
where codusuario = (select username
from glogin
where computername = (select distinct hostname
from master..sysprocesses
where spid = (select @@spid))
and datepart(yyyy,logintime) = (select distinct datepart(yyyy, getdate()) from gcoligada)
and datepart(mm,logintime) = (select distinct datepart(mm, getdate()) from gcoligada)
and datepart(dd,logintime) = (select distinct datepart(dd, getdate()) from gcoligada))
SQL - Redução de LOG
Para diminuir o tamanho do log da base de dados do cliente, siga os passos abaixo:
1º) Peça ao cliente para fazer um backup da base de dados
2º) Execute: DBCC SHRINKFILE (<log> ,<tamanho> ,truncateonly )
Onde: <log> , é o nome lógico do device de log.
<tamanho> , é o tamanho para o qual você quer reduzir o arquivo, (será reduzido ao valor máximo que o banco permite caso o tamanho seja muito pequeno).
3º) Execute: DUMP TRAN <banco de dados> WITH NO_LOG
Onde: <banco de dados> é o nome do banco de dados.
Ex:
DBCC SHRINKFILE (exemplo_log ,10 ,truncateonly)
DUMP TRAN EXEMPLO WITH NO_LOG
caso queira realizar a redução no máximo que o banco permite, retire o tamanho:
DBCC SHRINKFILE (exemplo_log , truncateonly)
1º) Peça ao cliente para fazer um backup da base de dados
2º) Execute: DBCC SHRINKFILE (<log> ,<tamanho> ,truncateonly )
Onde: <log> , é o nome lógico do device de log.
<tamanho> , é o tamanho para o qual você quer reduzir o arquivo, (será reduzido ao valor máximo que o banco permite caso o tamanho seja muito pequeno).
3º) Execute: DUMP TRAN <banco de dados> WITH NO_LOG
Onde: <banco de dados> é o nome do banco de dados.
Ex:
DBCC SHRINKFILE (exemplo_log ,10 ,truncateonly)
DUMP TRAN EXEMPLO WITH NO_LOG
caso queira realizar a redução no máximo que o banco permite, retire o tamanho:
DBCC SHRINKFILE (exemplo_log , truncateonly)
SQL - Left Outer Join
select a.codcolgerencial,a.codgerencial
from crateiolc a left outer join cgerencia b
on
a.codcolgerencial = b.codcoligada and
a.codgerencial = b.codconta
where
b.codcoligada is NULL and
b.codconta is NULL
group by
a.codcolgerencial,a.codgerencial,b.codcoligada,b.codconta
order by b.codconta
from crateiolc a left outer join cgerencia b
on
a.codcolgerencial = b.codcoligada and
a.codgerencial = b.codconta
where
b.codcoligada is NULL and
b.codconta is NULL
group by
a.codcolgerencial,a.codgerencial,b.codcoligada,b.codconta
order by b.codconta
SQL - Manutencao2.sql
set nocount on
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @database varchar(100)
Print 'Espaco Alocado no database'
exec sp_spaceused
declare teste cursor for
SELECT distinct TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES
open teste
fetch next from teste into @database
while (@@fetch_status=0)
begin
EXEC sp_dboption @database, 'trunc. log on chkpt.', 'TRUE'
EXEC sp_dboption @database, 'auto create statistics', 'FALSE'
EXEC sp_dboption @database, 'auto update statistics', 'FALSE'
EXEC sp_dboption @database, 'ANSI null default', 'TRUE'
print 'Verificando estrututura no Banco '+ @database
set @comando1 ='dbcc checkdb ('+@database+')'
exec (@comando1)
print '---------------------------------------------'
print ' '
print 'Verificando espaco alocado no Banco'+@database
set @comando2= 'dbcc checkalloc ('+@database+')'
exec (@comando2)
print '---------------------------------------------'
print ' '
print 'Alocacao '+ @database
set @comando3= 'dbcc newalloc ('+@database+')'
exec (@comando3)
print ' '
fetch next from teste into @database
end
close teste
deallocate teste
Print 'Final da Manuntencao'
print '*******************************************************************************************'
print '**********************************************************************************'
print '***************************************************************'
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @database varchar(100)
Print 'Espaco Alocado no database'
exec sp_spaceused
declare teste cursor for
SELECT distinct TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES
open teste
fetch next from teste into @database
while (@@fetch_status=0)
begin
EXEC sp_dboption @database, 'trunc. log on chkpt.', 'TRUE'
EXEC sp_dboption @database, 'auto create statistics', 'FALSE'
EXEC sp_dboption @database, 'auto update statistics', 'FALSE'
EXEC sp_dboption @database, 'ANSI null default', 'TRUE'
print 'Verificando estrututura no Banco '+ @database
set @comando1 ='dbcc checkdb ('+@database+')'
exec (@comando1)
print '---------------------------------------------'
print ' '
print 'Verificando espaco alocado no Banco'+@database
set @comando2= 'dbcc checkalloc ('+@database+')'
exec (@comando2)
print '---------------------------------------------'
print ' '
print 'Alocacao '+ @database
set @comando3= 'dbcc newalloc ('+@database+')'
exec (@comando3)
print ' '
fetch next from teste into @database
end
close teste
deallocate teste
Print 'Final da Manuntencao'
print '*******************************************************************************************'
print '**********************************************************************************'
print '***************************************************************'
SQL - Manutenção.sql
set nocount on
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @tabela varchar (100)
declare @database varchar (225)
declare base cursor for
SELECT distinct TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES
open base
fetch next from base into @database
declare tabelas cursor for
select name from sysobjects where type='U' order by name
open tabelas
fetch next from tabelas into @tabela
while @@fetch_status=0
begin
print 'Reindexando os indices da Tabela '+ @tabela
set @comando = 'dbcc dbreindex ('+@tabela+')'
exec (@comando)
print '---------------------------------------------'
print ' '
print 'Verificando estrututura da tabela '+ @tabela
set @comando1 ='dbcc checktable ('+@tabela+')'
exec (@comando1)
print '---------------------------------------------'
print ' '
print 'Verificando espaco alocado na tabela'+@tabela
set @comando2= 'dbcc updateusage ('+@database+','+@tabela+')'
exec (@comando2)
print '---------------------------------------------'
print ' '
fetch next from tabelas into @tabela
end
Print 'Final da Manuntencao'
print '*******************************************************************************************'
print '**********************************************************************************'
print '***************************************************************'
close base
close tabelas
deallocate tabelas
deallocate base
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @tabela varchar (100)
declare @database varchar (225)
declare base cursor for
SELECT distinct TABLE_CATALOG FROM INFORMATION_SCHEMA.TABLES
open base
fetch next from base into @database
declare tabelas cursor for
select name from sysobjects where type='U' order by name
open tabelas
fetch next from tabelas into @tabela
while @@fetch_status=0
begin
print 'Reindexando os indices da Tabela '+ @tabela
set @comando = 'dbcc dbreindex ('+@tabela+')'
exec (@comando)
print '---------------------------------------------'
print ' '
print 'Verificando estrututura da tabela '+ @tabela
set @comando1 ='dbcc checktable ('+@tabela+')'
exec (@comando1)
print '---------------------------------------------'
print ' '
print 'Verificando espaco alocado na tabela'+@tabela
set @comando2= 'dbcc updateusage ('+@database+','+@tabela+')'
exec (@comando2)
print '---------------------------------------------'
print ' '
fetch next from tabelas into @tabela
end
Print 'Final da Manuntencao'
print '*******************************************************************************************'
print '**********************************************************************************'
print '***************************************************************'
close base
close tabelas
deallocate tabelas
deallocate base
SQL / RM - Erros C#
-- Script para remoção dos erros do C# como por exemplo
-- Couldn't perform the edit because another user changed the record.
-- ATENÇÃO: ESTE NÃO DEVE SER EXECUTADO SE O BANCO FIZER BACKUP DE LOG
set nocount on
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @database varchar(100)
declare @datab varchar(50)
set @datab = (select distinct table_catalog from information_schema.tables)
if (select count(*) from master.dbo.sysprocesses where dbid in ( select dbid from master.dbo.sysdatabases where name = @datab )) <= 1
begin
print ' '
print 'Acertando permissões do banco.'
exec sp_dboption @datab, 'single', 'true'
exec sp_dboption @datab, 'trunc. log on chkpt.', 'true'
exec sp_dboption @datab, 'auto create statistics', 'false'
exec sp_dboption @datab, 'auto update statistics', 'false'
exec sp_dboption @datab, 'ansi null default', 'true'
print ' '
print 'Verificando base de dados.'
set @comando1 ='dbcc checkdb ('+@datab+')'
exec (@comando1)
print ' '
print 'Acertando fragmentação na base de dados.'
set @comando3= 'dbcc newalloc ('+@datab+')'
exec (@comando3)
declare @tabela varchar (100)
declare @comando5 varchar (255)
declare tabela cursor for
select name from sysobjects where type = 'u' order by name
open tabela
fetch next from tabela into @tabela
while @@fetch_status = 0
begin
print ''
print 'Verificando tabela '+@tabela
print ''
set @comando5 = 'dbcc checktable ('+@tabela +', repair_allow_data_loss)'
exec (@comando5)
set @comando = 'dbcc dbreindex ('+@tabela+')'
exec (@comando5)
set @comando5= 'dbcc checkconstraints ('+@tabela+')'
exec (@comando5)
fetch next from tabela into @tabela
end
close tabela
deallocate tabela
exec sp_dboption @datab, 'single', 'false'
declare @tab_nome varchar(255)
declare @tab_statistic varchar(255)
declare @sql_str varchar(255)
declare @db_nome varchar(255)
select @db_nome=name from master..sysdatabases where dbid=(select dbid from master..sysprocesses where spid=@@spid)
checkpoint
declare cur cursor for
select object_name(id),name from sysindexes where name like '_wa_sys%'
set nocount on
open cur
fetch next from cur into @tab_nome,@tab_statistic
while(@@fetch_status=0)
begin
set @sql_str='drop statistics ' + @tab_nome + '.' + @tab_statistic
exec (@sql_str)
fetch next from cur into @tab_nome,@tab_statistic
end
close cur
deallocate cur
exec sp_updatestats
print '*******************************************************************************************'
print 'FINAL DA MANUNTENCAO'
print '*******************************************************************************************'
end
else
begin
print '****** A T E N Ç Â O *******'
print 'A BASE ESTÁ SENDO USADA. ESTE SCRIPT SÓ PODE SER EXECUTADO EM MONOUSUÁRIO !!!'
print '***********************************************************************************************************************'
print 'Seguintes estações/número de conexões estão acessado a base de dados:'
declare @tab varchar(20)
declare @num varchar(2)
declare tabs cursor for select hostname,count(*) from master.dbo.sysprocesses where dbid in ( select dbid from master.dbo.sysdatabases where name = @datab ) group by hostname
open tabs
fetch next from tabs into @tab,@num
while(@@fetch_status=0)
begin
print @tab +'/'+@num
fetch next from tabs into @tab,@num
end
close tabs
deallocate tabs
end
-- Couldn't perform the edit because another user changed the record.
-- ATENÇÃO: ESTE NÃO DEVE SER EXECUTADO SE O BANCO FIZER BACKUP DE LOG
set nocount on
declare @comando varchar(255)
declare @comando1 varchar(255)
declare @comando2 varchar(255)
declare @comando3 varchar(255)
declare @comando4 varchar(255)
declare @database varchar(100)
declare @datab varchar(50)
set @datab = (select distinct table_catalog from information_schema.tables)
if (select count(*) from master.dbo.sysprocesses where dbid in ( select dbid from master.dbo.sysdatabases where name = @datab )) <= 1
begin
print ' '
print 'Acertando permissões do banco.'
exec sp_dboption @datab, 'single', 'true'
exec sp_dboption @datab, 'trunc. log on chkpt.', 'true'
exec sp_dboption @datab, 'auto create statistics', 'false'
exec sp_dboption @datab, 'auto update statistics', 'false'
exec sp_dboption @datab, 'ansi null default', 'true'
print ' '
print 'Verificando base de dados.'
set @comando1 ='dbcc checkdb ('+@datab+')'
exec (@comando1)
print ' '
print 'Acertando fragmentação na base de dados.'
set @comando3= 'dbcc newalloc ('+@datab+')'
exec (@comando3)
declare @tabela varchar (100)
declare @comando5 varchar (255)
declare tabela cursor for
select name from sysobjects where type = 'u' order by name
open tabela
fetch next from tabela into @tabela
while @@fetch_status = 0
begin
print ''
print 'Verificando tabela '+@tabela
print ''
set @comando5 = 'dbcc checktable ('+@tabela +', repair_allow_data_loss)'
exec (@comando5)
set @comando = 'dbcc dbreindex ('+@tabela+')'
exec (@comando5)
set @comando5= 'dbcc checkconstraints ('+@tabela+')'
exec (@comando5)
fetch next from tabela into @tabela
end
close tabela
deallocate tabela
exec sp_dboption @datab, 'single', 'false'
declare @tab_nome varchar(255)
declare @tab_statistic varchar(255)
declare @sql_str varchar(255)
declare @db_nome varchar(255)
select @db_nome=name from master..sysdatabases where dbid=(select dbid from master..sysprocesses where spid=@@spid)
checkpoint
declare cur cursor for
select object_name(id),name from sysindexes where name like '_wa_sys%'
set nocount on
open cur
fetch next from cur into @tab_nome,@tab_statistic
while(@@fetch_status=0)
begin
set @sql_str='drop statistics ' + @tab_nome + '.' + @tab_statistic
exec (@sql_str)
fetch next from cur into @tab_nome,@tab_statistic
end
close cur
deallocate cur
exec sp_updatestats
print '*******************************************************************************************'
print 'FINAL DA MANUNTENCAO'
print '*******************************************************************************************'
end
else
begin
print '****** A T E N Ç Â O *******'
print 'A BASE ESTÁ SENDO USADA. ESTE SCRIPT SÓ PODE SER EXECUTADO EM MONOUSUÁRIO !!!'
print '***********************************************************************************************************************'
print 'Seguintes estações/número de conexões estão acessado a base de dados:'
declare @tab varchar(20)
declare @num varchar(2)
declare tabs cursor for select hostname,count(*) from master.dbo.sysprocesses where dbid in ( select dbid from master.dbo.sysdatabases where name = @datab ) group by hostname
open tabs
fetch next from tabs into @tab,@num
while(@@fetch_status=0)
begin
print @tab +'/'+@num
fetch next from tabs into @tab,@num
end
close tabs
deallocate tabs
end
SQL - Perdeu o LDF
I called Microsoft and here was there solution that worked. To restore a database with only single MDF and no transaction log:
Rename current_database.mdf to current_database.mdf .old Create New Database with same name as old unavailable database Open SQLServer Properites, Server Settings tab
Set Server Properites to "ALLOW MODIFICATIONS TO BE MADE DIRECTLY TO THE SYSTEM"
Run this Query
update sysdatabases
set status = 32768
where name='database_name'
Stop/Start SQL Server
Database should appear in "Emergency Mode"
Then IMPORT Database and all objects into a NEW database Drop the Database which is in EMERGENCY MODE Rename NEW database to desired database name
Rename current_database.mdf to current_database.mdf .old Create New Database with same name as old unavailable database Open SQLServer Properites, Server Settings tab
Set Server Properites to "ALLOW MODIFICATIONS TO BE MADE DIRECTLY TO THE SYSTEM"
Run this Query
update sysdatabases
set status = 32768
where name='database_name'
Stop/Start SQL Server
Database should appear in "Emergency Mode"
Then IMPORT Database and all objects into a NEW database Drop the Database which is in EMERGENCY MODE Rename NEW database to desired database name
SQL - Importação de Arquivo Texto
Prezados,
para a importação de arquivos textos em uma determinada tabela de Banco de Dados:
SQL Server:
Devemos habilitar o seguinte parâmetro no Query Analyzer:
sp_dboption <Nome da Database>, 'select into/bulkcopy', 'true'
Comando para importação:
BULK INSERT <Nome da Database>..<Nome da Tabela> FROM '<Endereço do arquivo texto>'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '#',
ROWTERMINATOR = '\n'
)
** O campos dentro do arquivo texto deverão ter o mesmo tamanho do campo correspondente a ser importado, e deverá estar separado pelo caracter "#".
Oracle:
O Oracle possui alguns tipos de importação de arquivos textos, por exemplo:
1) Importação com um único arquivo de dados e de controle:
Arquivo de dados e controle, conteúdo:
LOAD DATA
INFILE *
APPEND INTO TABLE <Nome da Tabela> Ex: TabelaTemp
FIELDS TERMINATED BY '$'
<(Campos a serem atualizados)> Ex: (Codcoligada,Chapa)
BEGINDATA
<Dados a serem importados, separados pelo caracter "$"> Ex:1$00001
Após criado este arquivo, devemos no prompt do MS-DOS no diretório de instalação do Oracle\Bin, os arquivos "sqlldr.exe ou sqlldr80.exe" e executar o seguinte comando:
SQLLDR USERID=<USER/PASSWORD) Ex:RM/RM
CONTROL = <Caminho do arquivo criado acima> Ex:c:\temp\repres.txt
2) Importação com os arquivos de controles e de dados separados:
Arquivo de controle:
LOAD DATA
INFILE <Caminho do "Arquivo de Dados"> Ex: "C:\Windows\Temp\Teste.txt"
APPEND
INTO TABLE <Nome da Tabela> Ex: TabelaTemp
FIELDS TERMINMATED BY "," OPITIONALLY ENCLOSED BY ' " '
<Campos da tabela a serem populados>
(campo0, campo1, campo2, campo3, campo4 DATE(20) "DD-Month-YYYY",
campo5 CHAR TERMINATED BY ':')
Após criado este arquivo, devemos no prompt do MS-DOS no diretório de instalação do Oracle\Bin, os arquivos "sqlldr.exe ou sqlldr80.exe" e executar o seguinte comando:
SQLLDR USERID=<USER/PASSWORD) Ex:RM/RM
CONTROL = <Caminho do "arquivo de controle" criado acima> Ex:c:\temp\controle.ctl
** A possibilidade de utilizar o arquivo de controle separado do arquivo de dados, possibilita a formatação dos dados antes da importação na tabela, nem o trabalho de tratarmos as informações no arquivo texto, como no exemplo acima.
para a importação de arquivos textos em uma determinada tabela de Banco de Dados:
SQL Server:
Devemos habilitar o seguinte parâmetro no Query Analyzer:
sp_dboption <Nome da Database>, 'select into/bulkcopy', 'true'
Comando para importação:
BULK INSERT <Nome da Database>..<Nome da Tabela> FROM '<Endereço do arquivo texto>'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = '#',
ROWTERMINATOR = '\n'
)
** O campos dentro do arquivo texto deverão ter o mesmo tamanho do campo correspondente a ser importado, e deverá estar separado pelo caracter "#".
Oracle:
O Oracle possui alguns tipos de importação de arquivos textos, por exemplo:
1) Importação com um único arquivo de dados e de controle:
Arquivo de dados e controle, conteúdo:
LOAD DATA
INFILE *
APPEND INTO TABLE <Nome da Tabela> Ex: TabelaTemp
FIELDS TERMINATED BY '$'
<(Campos a serem atualizados)> Ex: (Codcoligada,Chapa)
BEGINDATA
<Dados a serem importados, separados pelo caracter "$"> Ex:1$00001
Após criado este arquivo, devemos no prompt do MS-DOS no diretório de instalação do Oracle\Bin, os arquivos "sqlldr.exe ou sqlldr80.exe" e executar o seguinte comando:
SQLLDR USERID=<USER/PASSWORD) Ex:RM/RM
CONTROL = <Caminho do arquivo criado acima> Ex:c:\temp\repres.txt
2) Importação com os arquivos de controles e de dados separados:
Arquivo de controle:
LOAD DATA
INFILE <Caminho do "Arquivo de Dados"> Ex: "C:\Windows\Temp\Teste.txt"
APPEND
INTO TABLE <Nome da Tabela> Ex: TabelaTemp
FIELDS TERMINMATED BY "," OPITIONALLY ENCLOSED BY ' " '
<Campos da tabela a serem populados>
(campo0, campo1, campo2, campo3, campo4 DATE(20) "DD-Month-YYYY",
campo5 CHAR TERMINATED BY ':')
Após criado este arquivo, devemos no prompt do MS-DOS no diretório de instalação do Oracle\Bin, os arquivos "sqlldr.exe ou sqlldr80.exe" e executar o seguinte comando:
SQLLDR USERID=<USER/PASSWORD) Ex:RM/RM
CONTROL = <Caminho do "arquivo de controle" criado acima> Ex:c:\temp\controle.ctl
** A possibilidade de utilizar o arquivo de controle separado do arquivo de dados, possibilita a formatação dos dados antes da importação na tabela, nem o trabalho de tratarmos as informações no arquivo texto, como no exemplo acima.
SQL - DBReindex
DECLARE @COMANDO VARCHAR(255)
DECLARE @TABELA VARCHAR(100)
DECLARE CUR_TABLES CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME
OPEN CUR_TABLES
FETCH NEXT FROM CUR_TABLES INTO @TABELA
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Atualizando indices da tabela '+@TABELA
SELECT @COMANDO = (SELECT 'DBCC DBREINDEX ('+@TABELA+')')
EXEC ( @COMANDO )
FETCH NEXT FROM CUR_TABLES INTO @TABELA
END
CLOSE CUR_TABLES
DEALLOCATE CUR_TABLES
SELECT @COMANDO = (SELECT 'DBCC UPDATEUSAGE ('+(select db_name())+')')
EXEC ( @COMANDO )
DECLARE @TABELA VARCHAR(100)
DECLARE CUR_TABLES CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME
OPEN CUR_TABLES
FETCH NEXT FROM CUR_TABLES INTO @TABELA
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Atualizando indices da tabela '+@TABELA
SELECT @COMANDO = (SELECT 'DBCC DBREINDEX ('+@TABELA+')')
EXEC ( @COMANDO )
FETCH NEXT FROM CUR_TABLES INTO @TABELA
END
CLOSE CUR_TABLES
DEALLOCATE CUR_TABLES
SELECT @COMANDO = (SELECT 'DBCC UPDATEUSAGE ('+(select db_name())+')')
EXEC ( @COMANDO )
SQL - Decrypt 2
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50))
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d nvarchar(4000), @i int, @t bigint
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
EXECUTE (@b)
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
SET @i=1
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
BEGIN
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
EXECUTE ('drop PROCEDURE '+ @objName)
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
execute( @d)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE DECRYPTSP2K (@objName varchar(50))
AS
DECLARE @a nvarchar(4000), @b nvarchar(4000), @c nvarchar(4000), @d nvarchar(4000), @i int, @t bigint
SET @a=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='ALTER PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
EXECUTE (@b)
SET @c=(SELECT ctext FROM syscomments WHERE id = object_id(@objName))
SET @b='CREATE PROCEDURE '+ @objName +' WITH ENCRYPTION AS '+REPLICATE('-', 4000-62)
SET @i=1
SET @d = replicate(N'A', (datalength(@a) / 2))
--loop
WHILE @i<=datalength(@a)/2
BEGIN
SET @d = stuff(@d, @i, 1,
NCHAR(UNICODE(substring(@a, @i, 1)) ^
(UNICODE(substring(@b, @i, 1)) ^
UNICODE(substring(@c, @i, 1)))))
SET @i=@i+1
END
EXECUTE ('drop PROCEDURE '+ @objName)
SET @d=REPLACE((@d),'WITH ENCRYPTION', '')
SET @d=REPLACE((@d),'With Encryption', '')
SET @d=REPLACE((@d),'with encryption', '')
IF CHARINDEX('WITH ENCRYPTION',UPPER(@d) )>0
SET @d=REPLACE(UPPER(@d),'WITH ENCRYPTION', '')
execute( @d)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL - Decrypt
if exists (select * from sysobjects where id = object_id('dbo.spDecryptSP') and sysstat & 0xf = 4)
drop procedure dbo.spDecryptSP
GO
Create procedure dbo.spDecryptSP
@spName varchar(50),
@Replace bit = 0
As
/*
Developer: Jonathan Spinks
Date: 24/07/03
Description: Decrypts SQL 2000 stored procedures
Inputs:
@spName = the name of the stored procedure you wish to decrypt
@Replace = 0 or 1. 0 if you wish to display the unencrypted sp
1 if you wish to replace the encrypted with the unencrypted sp
If this procedure returns "<@spName> is to large to decrypt"
this stored procedure can be modified to decrypt it.
Your starting point is to check out how many rows are returned
in the syscomments table for the stored procedures object_id.
If you just wish to display the sp then ensure that your result
pane is turned to 'Results in Text'.
In Query analyser (2k) Query => 'Results in Text'.
If your returned information looks 'cut short' first check
that your i_SQL query window is returning the entire string.
In Query analyser (2k) Tools => Options, Results tab,
Maximum characters per column = 8000.
Happy decrypting
Original idea: shoeboy <shoeboy@adequacy.org>
Adapted by: Joseph Gama Copyright © 1999-2002 SecurityFocus
Adapted Source: http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Enhanced by: Jonathan Spinks Copyright © 2003 I.S. Software Developments. WASH
*/
DECLARE
@a1 nvarchar(4000), @b1 nvarchar(4000), @c1 nvarchar(4000), @d1 nvarchar(4000),
@a2 nvarchar(4000), @b2 nvarchar(4000), @c2 nvarchar(4000), @d2 nvarchar(4000),
@a3 nvarchar(4000), @b3 nvarchar(4000), @c3 nvarchar(4000), @d3 nvarchar(4000),
@a4 nvarchar(4000), @b4 nvarchar(4000), @c4 nvarchar(4000), @d4 nvarchar(4000),
@a5 nvarchar(4000), @b5 nvarchar(4000), @c5 nvarchar(4000), @d5 nvarchar(4000),
@a6 nvarchar(4000), @b6 nvarchar(4000), @c6 nvarchar(4000), @d6 nvarchar(4000),
@a7 nvarchar(4000), @b7 nvarchar(4000), @c7 nvarchar(4000), @d7 nvarchar(4000),
@a8 nvarchar(4000), @b8 nvarchar(4000), @c8 nvarchar(4000), @d8 nvarchar(4000),
@a9 nvarchar(4000), @b9 nvarchar(4000), @c9 nvarchar(4000), @d9 nvarchar(4000),
@a0 nvarchar(4000), @b0 nvarchar(4000), @c0 nvarchar(4000), @d0 nvarchar(4000),
@Perm nvarchar(4000),
@i int
if not exists(SELECT * FROM syscomments WHERE id = object_id(@spName))
Begin
print @spName +' cannot be found'
return
End
if exists(SELECT * FROM syscomments WHERE id = object_id(@spName) and encrypted = 0)
Begin
print @spName +' is not encrypted'
return
End
if (SELECT count(*) FROM syscomments WHERE id = object_id(@spName)) > 10
Begin
print @spName +' is to large to decrypt'
return
End
-- Get a list of the current permissions on the encrypted stored procedure
declare curPerm cursor fast_forward for
select '['+ u.name +']' as name, p.actadd, p.actmod
from dbo.syspermissions p inner join dbo.sysusers u
On p.grantee = u.uid
where p.id = object_id(@spName)
open curPerm
Set @Perm = ''
while 1 = 1
begin
declare @name sysname,
@actadd smallint,
@actmod smallint
fetch next from curPerm into @name, @actadd, @actmod
if @@fetch_status <> 0
break
-- For each permission in the list construct a GRANT or DENY command
if @actadd & 32 = 32
Set @Perm = @Perm +'GRANT EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
else if @actmod & 32 = 32
Set @Perm = @Perm +'DENY EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
end
close curPerm
deallocate curPerm
-- Get encrypted stored procedure
SELECT @a1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @a2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @a3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @a4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @a5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @a6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @a7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @a8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @a9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @a0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
-- Create blank stored procedure
SET @b1='ALTER PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
SET @b2=REPLICATE(N'-', len(@a2))
SET @b3=REPLICATE(N'-', len(@a3))
SET @b4=REPLICATE(N'-', len(@a4))
SET @b5=REPLICATE(N'-', len(@a5))
SET @b6=REPLICATE(N'-', len(@a6))
SET @b7=REPLICATE(N'-', len(@a7))
SET @b8=REPLICATE(N'-', len(@a8))
SET @b9=REPLICATE(N'-', len(@a9))
SET @b0=REPLICATE(N'-', len(@a0))
-- Wrap in transaction so original stored procedure can be restored
Begin transaction ReplaceSP
Execute (@b1 + @b2 + @b3 + @b4 + @b5 + @b6 + @b7 + @b8 + @b9 + @b0)
-- Get blank encrypted stored procedure
SELECT @c1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @c2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @c3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @c4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @c5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @c6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @c7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @c8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @c9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @c0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
If @Replace = 0
Rollback Transaction ReplaceSP
Else
Commit Transaction ReplaceSP
SET @b1='CREATE PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
-- initalise the output variables
Set @d1 = ''
Set @d2 = ''
Set @d3 = ''
Set @d4 = ''
Set @d5 = ''
Set @d6 = ''
Set @d7 = ''
Set @d8 = ''
Set @d9 = ''
Set @d0 = ''
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a1)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d1 = @d1 + NCHAR(UNICODE(substring(@a1, @i, 1)) ^ (UNICODE(substring(@b1, @i, 1)) ^ UNICODE(substring(@c1, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a2)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d2 = @d2 + NCHAR(UNICODE(substring(@a2, @i, 1)) ^ (UNICODE(substring(@b2, @i, 1)) ^ UNICODE(substring(@c2, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a3)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d3 = @d3 + NCHAR(UNICODE(substring(@a3, @i, 1)) ^ (UNICODE(substring(@b3, @i, 1)) ^ UNICODE(substring(@c3, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a4)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d4 = @d4 + NCHAR(UNICODE(substring(@a4, @i, 1)) ^ (UNICODE(substring(@b4, @i, 1)) ^ UNICODE(substring(@c4, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a5)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d5 = @d5 + NCHAR(UNICODE(substring(@a5, @i, 1)) ^ (UNICODE(substring(@b5, @i, 1)) ^ UNICODE(substring(@c5, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a6)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d6 = @d6 + NCHAR(UNICODE(substring(@a6, @i, 1)) ^ (UNICODE(substring(@b6, @i, 1)) ^ UNICODE(substring(@c6, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a7)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d7 = @d7 + NCHAR(UNICODE(substring(@a7, @i, 1)) ^ (UNICODE(substring(@b7, @i, 1)) ^ UNICODE(substring(@c7, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a8)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d8 = @d8 + NCHAR(UNICODE(substring(@a8, @i, 1)) ^ (UNICODE(substring(@b8, @i, 1)) ^ UNICODE(substring(@c8, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a9)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d9 = @d9 + NCHAR(UNICODE(substring(@a9, @i, 1)) ^ (UNICODE(substring(@b9, @i, 1)) ^ UNICODE(substring(@c9, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a0)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d0 = @d0 + NCHAR(UNICODE(substring(@a0, @i, 1)) ^ (UNICODE(substring(@b0, @i, 1)) ^ UNICODE(substring(@c0, @i, 1))))
SET @i=@i+1
END
if @Replace = 0
Begin
-- Output the unencrypted stored procedure to the screen
select @d1 as 'Unencrypted Stored Procedure'
select @d2
select @d3
select @d4
select @d5
select @d6
select @d7
select @d8
select @d9
select @d0
-- Output any permissions that were on the encrypted stored procedure
Select @Perm as 'Permissions'
End
Else
Begin
-- Drop the encrypted stored procedure
Exec('Drop Procedure '+ @spName)
-- Remove the 'WITH ENCRYPTION' command from the stored procedure
Set @d1 = Replace(@d1, 'WITH ENCRYPTION', '')
-- Create the unencrypted stored procedure
Exec(@d1 + @d2 + @d3 + @d4 + @d5 + @d6 + @d7 + @d8 + @d9 + @d0)
-- Apply any permissions that were on the encrypted stored procedure
Exec(@Perm)
End
Go
drop procedure dbo.spDecryptSP
GO
Create procedure dbo.spDecryptSP
@spName varchar(50),
@Replace bit = 0
As
/*
Developer: Jonathan Spinks
Date: 24/07/03
Description: Decrypts SQL 2000 stored procedures
Inputs:
@spName = the name of the stored procedure you wish to decrypt
@Replace = 0 or 1. 0 if you wish to display the unencrypted sp
1 if you wish to replace the encrypted with the unencrypted sp
If this procedure returns "<@spName> is to large to decrypt"
this stored procedure can be modified to decrypt it.
Your starting point is to check out how many rows are returned
in the syscomments table for the stored procedures object_id.
If you just wish to display the sp then ensure that your result
pane is turned to 'Results in Text'.
In Query analyser (2k) Query => 'Results in Text'.
If your returned information looks 'cut short' first check
that your i_SQL query window is returning the entire string.
In Query analyser (2k) Tools => Options, Results tab,
Maximum characters per column = 8000.
Happy decrypting
Original idea: shoeboy <shoeboy@adequacy.org>
Adapted by: Joseph Gama Copyright © 1999-2002 SecurityFocus
Adapted Source: http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=505&lngWId=5
Enhanced by: Jonathan Spinks Copyright © 2003 I.S. Software Developments. WASH
*/
DECLARE
@a1 nvarchar(4000), @b1 nvarchar(4000), @c1 nvarchar(4000), @d1 nvarchar(4000),
@a2 nvarchar(4000), @b2 nvarchar(4000), @c2 nvarchar(4000), @d2 nvarchar(4000),
@a3 nvarchar(4000), @b3 nvarchar(4000), @c3 nvarchar(4000), @d3 nvarchar(4000),
@a4 nvarchar(4000), @b4 nvarchar(4000), @c4 nvarchar(4000), @d4 nvarchar(4000),
@a5 nvarchar(4000), @b5 nvarchar(4000), @c5 nvarchar(4000), @d5 nvarchar(4000),
@a6 nvarchar(4000), @b6 nvarchar(4000), @c6 nvarchar(4000), @d6 nvarchar(4000),
@a7 nvarchar(4000), @b7 nvarchar(4000), @c7 nvarchar(4000), @d7 nvarchar(4000),
@a8 nvarchar(4000), @b8 nvarchar(4000), @c8 nvarchar(4000), @d8 nvarchar(4000),
@a9 nvarchar(4000), @b9 nvarchar(4000), @c9 nvarchar(4000), @d9 nvarchar(4000),
@a0 nvarchar(4000), @b0 nvarchar(4000), @c0 nvarchar(4000), @d0 nvarchar(4000),
@Perm nvarchar(4000),
@i int
if not exists(SELECT * FROM syscomments WHERE id = object_id(@spName))
Begin
print @spName +' cannot be found'
return
End
if exists(SELECT * FROM syscomments WHERE id = object_id(@spName) and encrypted = 0)
Begin
print @spName +' is not encrypted'
return
End
if (SELECT count(*) FROM syscomments WHERE id = object_id(@spName)) > 10
Begin
print @spName +' is to large to decrypt'
return
End
-- Get a list of the current permissions on the encrypted stored procedure
declare curPerm cursor fast_forward for
select '['+ u.name +']' as name, p.actadd, p.actmod
from dbo.syspermissions p inner join dbo.sysusers u
On p.grantee = u.uid
where p.id = object_id(@spName)
open curPerm
Set @Perm = ''
while 1 = 1
begin
declare @name sysname,
@actadd smallint,
@actmod smallint
fetch next from curPerm into @name, @actadd, @actmod
if @@fetch_status <> 0
break
-- For each permission in the list construct a GRANT or DENY command
if @actadd & 32 = 32
Set @Perm = @Perm +'GRANT EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
else if @actmod & 32 = 32
Set @Perm = @Perm +'DENY EXECUTE ON '+ @spName +' TO '+ @name +char(13)+char(10)+'Go'+char(13)+char(10)
end
close curPerm
deallocate curPerm
-- Get encrypted stored procedure
SELECT @a1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @a2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @a3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @a4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @a5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @a6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @a7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @a8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @a9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @a0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
-- Create blank stored procedure
SET @b1='ALTER PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
SET @b2=REPLICATE(N'-', len(@a2))
SET @b3=REPLICATE(N'-', len(@a3))
SET @b4=REPLICATE(N'-', len(@a4))
SET @b5=REPLICATE(N'-', len(@a5))
SET @b6=REPLICATE(N'-', len(@a6))
SET @b7=REPLICATE(N'-', len(@a7))
SET @b8=REPLICATE(N'-', len(@a8))
SET @b9=REPLICATE(N'-', len(@a9))
SET @b0=REPLICATE(N'-', len(@a0))
-- Wrap in transaction so original stored procedure can be restored
Begin transaction ReplaceSP
Execute (@b1 + @b2 + @b3 + @b4 + @b5 + @b6 + @b7 + @b8 + @b9 + @b0)
-- Get blank encrypted stored procedure
SELECT @c1=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 1
SELECT @c2=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 2
SELECT @c3=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 3
SELECT @c4=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 4
SELECT @c5=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 5
SELECT @c6=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 6
SELECT @c7=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 7
SELECT @c8=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 8
SELECT @c9=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 9
SELECT @c0=ctext FROM syscomments WHERE id = object_id(@spName) and colid = 10
If @Replace = 0
Rollback Transaction ReplaceSP
Else
Commit Transaction ReplaceSP
SET @b1='CREATE PROCEDURE '+ @spName +' WITH ENCRYPTION AS '+ REPLICATE('-', Len(@a1))
-- initalise the output variables
Set @d1 = ''
Set @d2 = ''
Set @d3 = ''
Set @d4 = ''
Set @d5 = ''
Set @d6 = ''
Set @d7 = ''
Set @d8 = ''
Set @d9 = ''
Set @d0 = ''
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a1)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d1 = @d1 + NCHAR(UNICODE(substring(@a1, @i, 1)) ^ (UNICODE(substring(@b1, @i, 1)) ^ UNICODE(substring(@c1, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a2)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d2 = @d2 + NCHAR(UNICODE(substring(@a2, @i, 1)) ^ (UNICODE(substring(@b2, @i, 1)) ^ UNICODE(substring(@c2, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a3)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d3 = @d3 + NCHAR(UNICODE(substring(@a3, @i, 1)) ^ (UNICODE(substring(@b3, @i, 1)) ^ UNICODE(substring(@c3, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a4)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d4 = @d4 + NCHAR(UNICODE(substring(@a4, @i, 1)) ^ (UNICODE(substring(@b4, @i, 1)) ^ UNICODE(substring(@c4, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a5)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d5 = @d5 + NCHAR(UNICODE(substring(@a5, @i, 1)) ^ (UNICODE(substring(@b5, @i, 1)) ^ UNICODE(substring(@c5, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a6)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d6 = @d6 + NCHAR(UNICODE(substring(@a6, @i, 1)) ^ (UNICODE(substring(@b6, @i, 1)) ^ UNICODE(substring(@c6, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a7)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d7 = @d7 + NCHAR(UNICODE(substring(@a7, @i, 1)) ^ (UNICODE(substring(@b7, @i, 1)) ^ UNICODE(substring(@c7, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a8)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d8 = @d8 + NCHAR(UNICODE(substring(@a8, @i, 1)) ^ (UNICODE(substring(@b8, @i, 1)) ^ UNICODE(substring(@c8, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a9)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d9 = @d9 + NCHAR(UNICODE(substring(@a9, @i, 1)) ^ (UNICODE(substring(@b9, @i, 1)) ^ UNICODE(substring(@c9, @i, 1))))
SET @i=@i+1
END
-- Set the counter to one
Set @i = 1
WHILE @i < datalength(@a0)/2 + 1
BEGIN
-- (unencrypted original) = (encrypted original) XOR (unencrypted blank) XOR (encrypted blank)
SET @d0 = @d0 + NCHAR(UNICODE(substring(@a0, @i, 1)) ^ (UNICODE(substring(@b0, @i, 1)) ^ UNICODE(substring(@c0, @i, 1))))
SET @i=@i+1
END
if @Replace = 0
Begin
-- Output the unencrypted stored procedure to the screen
select @d1 as 'Unencrypted Stored Procedure'
select @d2
select @d3
select @d4
select @d5
select @d6
select @d7
select @d8
select @d9
select @d0
-- Output any permissions that were on the encrypted stored procedure
Select @Perm as 'Permissions'
End
Else
Begin
-- Drop the encrypted stored procedure
Exec('Drop Procedure '+ @spName)
-- Remove the 'WITH ENCRYPTION' command from the stored procedure
Set @d1 = Replace(@d1, 'WITH ENCRYPTION', '')
-- Create the unencrypted stored procedure
Exec(@d1 + @d2 + @d3 + @d4 + @d5 + @d6 + @d7 + @d8 + @d9 + @d0)
-- Apply any permissions that were on the encrypted stored procedure
Exec(@Perm)
End
Go
SQL - Deleta Estatisticas
declare @tab_nome varchar(255)
declare @tab_statistic varchar(255)
declare @sql_str varchar(255)
declare @db_nome varchar(255)
select @db_nome=name from master..sysdatabases where dbid=(select dbid from master..sysprocesses where spid=@@SPID)
set @sql_str= 'sp_dboption ''' + @db_nome + ''',''auto create statistics'',''false'''
exec (@sql_str)
set @sql_str= 'sp_dboption ''' + @db_nome + ''',''auto update statistics'',''false'''
exec (@sql_str)
checkpoint
declare cur cursor for SELECT object_name(id),name FROM SYSINDEXES WHERE NAME LIKE '_WA_Sys%'
set nocount on
open cur
fetch next from cur into @tab_nome,@tab_statistic
while(@@fetch_status=0)
begin
set @sql_str='drop statistics ' + @tab_nome + '.' + @tab_statistic
print @sql_str
exec (@sql_str)
fetch next from cur into @tab_nome,@tab_statistic
end
close cur
deallocate cur
set nocount off
declare @tab_statistic varchar(255)
declare @sql_str varchar(255)
declare @db_nome varchar(255)
select @db_nome=name from master..sysdatabases where dbid=(select dbid from master..sysprocesses where spid=@@SPID)
set @sql_str= 'sp_dboption ''' + @db_nome + ''',''auto create statistics'',''false'''
exec (@sql_str)
set @sql_str= 'sp_dboption ''' + @db_nome + ''',''auto update statistics'',''false'''
exec (@sql_str)
checkpoint
declare cur cursor for SELECT object_name(id),name FROM SYSINDEXES WHERE NAME LIKE '_WA_Sys%'
set nocount on
open cur
fetch next from cur into @tab_nome,@tab_statistic
while(@@fetch_status=0)
begin
set @sql_str='drop statistics ' + @tab_nome + '.' + @tab_statistic
print @sql_str
exec (@sql_str)
fetch next from cur into @tab_nome,@tab_statistic
end
close cur
deallocate cur
set nocount off
SQL - Performance
Dicas de Performance no Servidor do SQL Server
1. Servidor Dedicado
Como hoje em dia há uma grande quantidade de programas do tipo Client/Server (inclusive o SQL Server) e o ideal seria dedicarmos uma máquina somente para rodar o serviço de banco de dados. Programas servidores de Web, FTP, e-mail, proxy, DNS, DHCP, Impressão, Source Safe, Telnet, Firewall, etc.., devem estar preferencialmente rodando em outra máquina que não seja a do banco de dados, pois estes serviços estarão competindo pelos mesmos recursos de hardware com o serviço do banco de dados.
Eu sei, eu sei, nem sempre temos o luxo de possuir uma máquina que seja dedicada ao banco, mas sempre que possível procure limitar ao máximo o número de serviços que atendem a solicitação de usuários rodando ao mesmo tempo, principalmente em servidores de produção.
Também é importante dizer que todo o acesso via rede ao servidor de banco de dados deve ser extremamente limitado. Algumas empresas somente ligam o cabo de rede do servidor de banco de dados diretamente ao servidor de aplicação restringindo ainda mais o acesso ao servidor de banco de dados. Nem acesso a Internet é aconselhável se colocar em um servidor de banco de dados dedicado.
2. Anti-Vírus
Bom, para começar, devemos tocar cuidado com qualquer programa que está rodando ma mesma máquina que o servidor. Muitos programas parecem inofensivos a primeira vista, mas podem ter certeza quem eles vão roubar ciclos do processador quando estiverem rodando.
Os Anti-vírus são um caso a parte. Com a crescente ameaça destas pragas virtuais é muito difícil ficar livre de se pegar um vírus. Eu recomendo que se tenha instalado um bom anti-vírus no servidor de banco de dados mas procurem evitar ao máximo que este anti-vírus fique rodando como um serviço, pois neste caso a cada arquivo aberto pelo sistema operacional, uma varredura é feita degradando muito a performance e competindo com o banco de dados no acesso ao disco rígido da máquina, que é uma operação efetuada várias vezes pelo o banco de dados.
Veja bem pessoal, eu não estou dizendo que não devemos proteger nosso servidor com um anti-vírus. Mas também não podemos deixar que este programa não essencial atrapalhe a performance do banco de dados. É somente uma questão de conhecer um pouco sobre o anti-vírus escolhido e procurar desabilitar alguns recursos do mesmo que podem causar perda de performance no servidor.
3. Programas tipo Client
Este tópico me lembra uma ocasião em que fui verificar um problema na máquina de um amigo. Na máquina dele tinha tanto programa rodando ao mesmo tempo minimizado que a barra de tarefas (system tray) do Windows dele estava preenchida com ícones até a metade da tela! E olha que ele estava rodando com uma resolução pequena e com um monitor de 17 polegadas!
Falando sério agora, um servidor de banco de dados é uma máquina que não deve ser utilizada para brincadeira. Programas como ICQ, Morpheus, Kazaa, Real Audio, tradutor inglês-português, descanso de tela, driver do mouse ou teclado especial, etc..., devem ser COMPLETAMENTE eliminados do servidor. Geralmente estes programas são carregados no momento da inicialização do servidor (boot) e podem ser desabilitados de diversas maneiras. Eu costumo dizer que na barra de tarefas do Windows, ao lado do relógio só o mostrador de tráfego de rede e o gerenciador de tarefas (task manager), com a medição da performance, devem ser vistos.
Também é bom dar uma verificada em programas client como o do proxy, do firewall, do DNS, etc. Alguns destes programas realmente são necessários, mas deve-se fazer uma boa revisão no que deve estar instalado e rodando no servidor.
Eu particularmente procuro instalar o mínimo de programas no servidor. Nem o Office eu recomendo a instalação (mesmo por que ele consome muito espaço na HD) e também nenhum programa client de e-mail.
Resumindo: se você quiser um computador para rodar aquele server do Counter Strike, mesmo que seja somente durante o almoço, é melhor procurar outra máquina que não a do banco de dados!
4. Serviços
Também é extremamente importante uma boa olhada nos serviços que estão rodando no servidor. Podemos obter a lista dos serviços através do Painel de Controle (Control Panel), Ferramentas Administrativas (Administrative Tools) e no ícone Serviços (Services).
Se preocupe somente com aqueles marcados para a inicialização automática. Abaixo mostro alguns serviços (o nome deles está em português) que podem ser desabilitados, poupando memória. Mas atenção: sempre que foi fazer uma modificação no estado de um serviço procure verificar quais os efeitos colaterais que esta ação causará!
* Agendador de tarefas – Geralmente utilizado para a execução de programas como o Defrag ou o ScanDisk periodicamente. Pode ser desabilitado sem problemas.
* Spooler de impressão – Se você não deseja imprimir a partir desta estação, desabilite este serviço.
* Localizador de computadores – Geralmente não precisamos deste serviço que serve somente para localizar computadores na rede.
* Serviço RunAs – Somente habilite este serviço se for utilizar o utilitário de console RunAs. Se você nem sabe o que é isso, desabilite este serviço.
* Windows Installer – Confesso que este serviço as vezes me irrita! Em um servidor de banco de dados dedicado, onde raramente são instalados novos softwares, podemos desabilitar este serviço para não receber aquelas irritantes janelas de aviso de instalação...
* Gerenciador de discos lógicos e Serviço administrativo do gerenciador de disco lógico – Estes dois serviços servem somente para a formatação/alteração de uma partição dos discos rígidos do servidor. Como estes serviços somente são utilizados uma vez na vida e outra na morte, podemos desabilitá-los.
Aqui quero fazer um aviso importante: geralmente mexer em alguns serviços do Windows que não sabemos para que servem pode ser um mau negócio. Principalmente se for um serviço essencial... Por isso, todo cuidado é pouco! E sempre que um serviço que está rodando lhe parecer estranho, se informe pois pode ser até algo colocado por um Hacker!
Todos os serviços que forem desabilitados podem ser habilitados normalmente e vice e versa.
5. Verificando os processos.
Podemos ver quais processos estão rodando atualmente no Windows, assim como quanto de memória e de processador está sendo gasto em cada processo, através do Gerenciador de Tarefas (task manager) na aba processos. Para a galera que vem do Linux, é quase a mesma coisa que o comando ps. É muito importante sabermos o que está rodando no momento na máquina e ficarmos ligados nos processos, inclusive alterando a prioridade ou matar o processo, caso este esteja travado. Geralmente se um processo ficar ocupando muito do processador por um bom período de tempo, é provável que ele esteja “travado” e onerando a performance de todo o sistema.
6. Recursos do servidor
Abaixo eu mostro algumas dicas sobre como otimizar o uso de recursos da máquina servidora pelo Windows:
* Resolução da Máquina
O servidor de banco de dados não é uma máquina que deve ser utilizada diretamente na estação (geralmente o DBA faz administração remota, via Terminal Services ou VNC). Logo, não há razão para utilizar uma resolução boa, tanto de cor como de tamanho. Aconselho 800x600 com 16 cores, no máximo, para conservar memória.
* Compartilhamento
Procure evitar também muitos compartilhamentos de recursos. Se possível, remova os compartilhamentos padrão (como o C$ ou D$) como medida de segurança.
* Som
Elimine qualquer aplicativo/driver que necessite de som do servidor. Na maioria dos casos, nem há uma placa de som no servidor.
* Ícones no DeskTop
Diminua ao máximo a quantidade de ícones no Desktop, pois cada um deles ocupa a memória de vídeo e espaço em disco
* Proteção de Tela
Como não devemos utilizar a estação do servidor diretamente para acessá-lo (salvo em casos extremos), podemos desabilitar a proteção de tela. Uma boa idéia também é desligar o monitor e acabar economizando energia. É interessante também nem logarmos a estação, uma vez que o serviço do banco de dados pode ser configurado para a inicialização automática.
* Opções de Desempenho
Existe uma opção no Windows que dá prioridade aos programas que estão funcionando em background, como é o caso do SQL Server. Para habilitá-la vá em propriedades do sistema, escolha a aba avançado e clique no botão Opções de Desempenho. Escolha o check box Serviços em Segundo plano.
Fiquem atentos também ao valor estabelecido para a memória virtual do Windows.
* Internet e E-mail
Como já mencionado anteriormente, procure evitar que o servidor de banco de dados possua acesso à Internet e ao servidor de e-mail, para evitar uma brecha de segurança e para que não haja risco de contaminação com vírus via e-mail.
* IP Fixo
Como o servidor de banco de dados raramente deve ser reinicializado (a não ser em casos extremamente necessários) procure colocar um IP fixo no mesmo, evitando assim que o servidor faça um acesso para a obtenção de um IP dinâmico ao servidor de DHCP.
7. Dispositivos
Sempre que possível, procure não conectar nenhum dispositivo que não seja realmente necessário para o funcionamento do servidor, seja ele via porta serial, paralela, USB ou interno. Lembre-se também que a cada novo dispositivo plugado no computador um driver deve ser carregado na memória, consumindo assim mais recursos. Abaixo eu mostro uma lista de dispositivos que não são recomendados em um servidor de banco de dados:
* Placa de Som (Quem gostaria de ouvir música no servidor de banco de dados?)
* Placa de Vídeo 3D
* Scanner
* Zip Drive
* Impressora
* Câmera Digital
* Gravador de CD
* DVD
Por outro lado, é extremamente interessante que o servidor possua um leitor de CD comum (para instalação do software) e um dispositivo para backup de grande quantidade como um leitor de fita DAT, por exemplo. Isto sem contar a essencial placa de rede.
Dica retirada do site
http://www.imasters.com.br
1. Servidor Dedicado
Como hoje em dia há uma grande quantidade de programas do tipo Client/Server (inclusive o SQL Server) e o ideal seria dedicarmos uma máquina somente para rodar o serviço de banco de dados. Programas servidores de Web, FTP, e-mail, proxy, DNS, DHCP, Impressão, Source Safe, Telnet, Firewall, etc.., devem estar preferencialmente rodando em outra máquina que não seja a do banco de dados, pois estes serviços estarão competindo pelos mesmos recursos de hardware com o serviço do banco de dados.
Eu sei, eu sei, nem sempre temos o luxo de possuir uma máquina que seja dedicada ao banco, mas sempre que possível procure limitar ao máximo o número de serviços que atendem a solicitação de usuários rodando ao mesmo tempo, principalmente em servidores de produção.
Também é importante dizer que todo o acesso via rede ao servidor de banco de dados deve ser extremamente limitado. Algumas empresas somente ligam o cabo de rede do servidor de banco de dados diretamente ao servidor de aplicação restringindo ainda mais o acesso ao servidor de banco de dados. Nem acesso a Internet é aconselhável se colocar em um servidor de banco de dados dedicado.
2. Anti-Vírus
Bom, para começar, devemos tocar cuidado com qualquer programa que está rodando ma mesma máquina que o servidor. Muitos programas parecem inofensivos a primeira vista, mas podem ter certeza quem eles vão roubar ciclos do processador quando estiverem rodando.
Os Anti-vírus são um caso a parte. Com a crescente ameaça destas pragas virtuais é muito difícil ficar livre de se pegar um vírus. Eu recomendo que se tenha instalado um bom anti-vírus no servidor de banco de dados mas procurem evitar ao máximo que este anti-vírus fique rodando como um serviço, pois neste caso a cada arquivo aberto pelo sistema operacional, uma varredura é feita degradando muito a performance e competindo com o banco de dados no acesso ao disco rígido da máquina, que é uma operação efetuada várias vezes pelo o banco de dados.
Veja bem pessoal, eu não estou dizendo que não devemos proteger nosso servidor com um anti-vírus. Mas também não podemos deixar que este programa não essencial atrapalhe a performance do banco de dados. É somente uma questão de conhecer um pouco sobre o anti-vírus escolhido e procurar desabilitar alguns recursos do mesmo que podem causar perda de performance no servidor.
3. Programas tipo Client
Este tópico me lembra uma ocasião em que fui verificar um problema na máquina de um amigo. Na máquina dele tinha tanto programa rodando ao mesmo tempo minimizado que a barra de tarefas (system tray) do Windows dele estava preenchida com ícones até a metade da tela! E olha que ele estava rodando com uma resolução pequena e com um monitor de 17 polegadas!
Falando sério agora, um servidor de banco de dados é uma máquina que não deve ser utilizada para brincadeira. Programas como ICQ, Morpheus, Kazaa, Real Audio, tradutor inglês-português, descanso de tela, driver do mouse ou teclado especial, etc..., devem ser COMPLETAMENTE eliminados do servidor. Geralmente estes programas são carregados no momento da inicialização do servidor (boot) e podem ser desabilitados de diversas maneiras. Eu costumo dizer que na barra de tarefas do Windows, ao lado do relógio só o mostrador de tráfego de rede e o gerenciador de tarefas (task manager), com a medição da performance, devem ser vistos.
Também é bom dar uma verificada em programas client como o do proxy, do firewall, do DNS, etc. Alguns destes programas realmente são necessários, mas deve-se fazer uma boa revisão no que deve estar instalado e rodando no servidor.
Eu particularmente procuro instalar o mínimo de programas no servidor. Nem o Office eu recomendo a instalação (mesmo por que ele consome muito espaço na HD) e também nenhum programa client de e-mail.
Resumindo: se você quiser um computador para rodar aquele server do Counter Strike, mesmo que seja somente durante o almoço, é melhor procurar outra máquina que não a do banco de dados!
4. Serviços
Também é extremamente importante uma boa olhada nos serviços que estão rodando no servidor. Podemos obter a lista dos serviços através do Painel de Controle (Control Panel), Ferramentas Administrativas (Administrative Tools) e no ícone Serviços (Services).
Se preocupe somente com aqueles marcados para a inicialização automática. Abaixo mostro alguns serviços (o nome deles está em português) que podem ser desabilitados, poupando memória. Mas atenção: sempre que foi fazer uma modificação no estado de um serviço procure verificar quais os efeitos colaterais que esta ação causará!
* Agendador de tarefas – Geralmente utilizado para a execução de programas como o Defrag ou o ScanDisk periodicamente. Pode ser desabilitado sem problemas.
* Spooler de impressão – Se você não deseja imprimir a partir desta estação, desabilite este serviço.
* Localizador de computadores – Geralmente não precisamos deste serviço que serve somente para localizar computadores na rede.
* Serviço RunAs – Somente habilite este serviço se for utilizar o utilitário de console RunAs. Se você nem sabe o que é isso, desabilite este serviço.
* Windows Installer – Confesso que este serviço as vezes me irrita! Em um servidor de banco de dados dedicado, onde raramente são instalados novos softwares, podemos desabilitar este serviço para não receber aquelas irritantes janelas de aviso de instalação...
* Gerenciador de discos lógicos e Serviço administrativo do gerenciador de disco lógico – Estes dois serviços servem somente para a formatação/alteração de uma partição dos discos rígidos do servidor. Como estes serviços somente são utilizados uma vez na vida e outra na morte, podemos desabilitá-los.
Aqui quero fazer um aviso importante: geralmente mexer em alguns serviços do Windows que não sabemos para que servem pode ser um mau negócio. Principalmente se for um serviço essencial... Por isso, todo cuidado é pouco! E sempre que um serviço que está rodando lhe parecer estranho, se informe pois pode ser até algo colocado por um Hacker!
Todos os serviços que forem desabilitados podem ser habilitados normalmente e vice e versa.
5. Verificando os processos.
Podemos ver quais processos estão rodando atualmente no Windows, assim como quanto de memória e de processador está sendo gasto em cada processo, através do Gerenciador de Tarefas (task manager) na aba processos. Para a galera que vem do Linux, é quase a mesma coisa que o comando ps. É muito importante sabermos o que está rodando no momento na máquina e ficarmos ligados nos processos, inclusive alterando a prioridade ou matar o processo, caso este esteja travado. Geralmente se um processo ficar ocupando muito do processador por um bom período de tempo, é provável que ele esteja “travado” e onerando a performance de todo o sistema.
6. Recursos do servidor
Abaixo eu mostro algumas dicas sobre como otimizar o uso de recursos da máquina servidora pelo Windows:
* Resolução da Máquina
O servidor de banco de dados não é uma máquina que deve ser utilizada diretamente na estação (geralmente o DBA faz administração remota, via Terminal Services ou VNC). Logo, não há razão para utilizar uma resolução boa, tanto de cor como de tamanho. Aconselho 800x600 com 16 cores, no máximo, para conservar memória.
* Compartilhamento
Procure evitar também muitos compartilhamentos de recursos. Se possível, remova os compartilhamentos padrão (como o C$ ou D$) como medida de segurança.
* Som
Elimine qualquer aplicativo/driver que necessite de som do servidor. Na maioria dos casos, nem há uma placa de som no servidor.
* Ícones no DeskTop
Diminua ao máximo a quantidade de ícones no Desktop, pois cada um deles ocupa a memória de vídeo e espaço em disco
* Proteção de Tela
Como não devemos utilizar a estação do servidor diretamente para acessá-lo (salvo em casos extremos), podemos desabilitar a proteção de tela. Uma boa idéia também é desligar o monitor e acabar economizando energia. É interessante também nem logarmos a estação, uma vez que o serviço do banco de dados pode ser configurado para a inicialização automática.
* Opções de Desempenho
Existe uma opção no Windows que dá prioridade aos programas que estão funcionando em background, como é o caso do SQL Server. Para habilitá-la vá em propriedades do sistema, escolha a aba avançado e clique no botão Opções de Desempenho. Escolha o check box Serviços em Segundo plano.
Fiquem atentos também ao valor estabelecido para a memória virtual do Windows.
* Internet e E-mail
Como já mencionado anteriormente, procure evitar que o servidor de banco de dados possua acesso à Internet e ao servidor de e-mail, para evitar uma brecha de segurança e para que não haja risco de contaminação com vírus via e-mail.
* IP Fixo
Como o servidor de banco de dados raramente deve ser reinicializado (a não ser em casos extremamente necessários) procure colocar um IP fixo no mesmo, evitando assim que o servidor faça um acesso para a obtenção de um IP dinâmico ao servidor de DHCP.
7. Dispositivos
Sempre que possível, procure não conectar nenhum dispositivo que não seja realmente necessário para o funcionamento do servidor, seja ele via porta serial, paralela, USB ou interno. Lembre-se também que a cada novo dispositivo plugado no computador um driver deve ser carregado na memória, consumindo assim mais recursos. Abaixo eu mostro uma lista de dispositivos que não são recomendados em um servidor de banco de dados:
* Placa de Som (Quem gostaria de ouvir música no servidor de banco de dados?)
* Placa de Vídeo 3D
* Scanner
* Zip Drive
* Impressora
* Câmera Digital
* Gravador de CD
* DVD
Por outro lado, é extremamente interessante que o servidor possua um leitor de CD comum (para instalação do software) e um dispositivo para backup de grande quantidade como um leitor de fita DAT, por exemplo. Isto sem contar a essencial placa de rede.
Dica retirada do site
http://www.imasters.com.br
SQL - Dicionário de Dados
select SUBSTRING (TABLE_NAME,1,40) TABLE_NAME, SUBSTRING (COLUMN_NAME,1,40) COLUMN_NAME, IS_NULLABLE,
CASE DATA_TYPE
WHEN 'varchar' then 'varchar(' + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN 'varchar' then 'char(' + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ')'
else SUBSTRING(DATA_TYPE,1,25)
END AS DATA_TYPE, gcampos.DESCRICAO
from information_schema.columns c, gcampos
where gcampos.tabela = c.TABLE_NAME
and gcampos.coluna = c.COLUMN_NAME
AND C.TABLE_NAME = 'FCFODEF'
order by C.TABLE_NAME,C.ORDINAL_POSITION
CASE DATA_TYPE
WHEN 'varchar' then 'varchar(' + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN 'varchar' then 'char(' + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ')'
else SUBSTRING(DATA_TYPE,1,25)
END AS DATA_TYPE, gcampos.DESCRICAO
from information_schema.columns c, gcampos
where gcampos.tabela = c.TABLE_NAME
and gcampos.coluna = c.COLUMN_NAME
AND C.TABLE_NAME = 'FCFODEF'
order by C.TABLE_NAME,C.ORDINAL_POSITION
SQL - Functions (modelo)
CREATE Function AchaDescricaoConta(var_codcoligada number,
var_contafolha varchar,
var_codigointegracao varchar)
return varchar
as
VAR_CONTACONTABIL varchar2(40);
VAR_DESCRICAO varchar2(40);
begin
select contasaldus into VAR_CONTACONTABIL
from pcontas
where codcoligada=var_codcoligada
and codcolconta=var_codcoligada
and grpconta=var_contafolha
and integrcfunc=var_codigointegracao;
select descricao into VAR_DESCRICAO
from cconta
where codcoligada=var_codcoligada
and codconta=VAR_CONTACONTABIL ;
return(VAR_DESCRICAO);
end;
var_contafolha varchar,
var_codigointegracao varchar)
return varchar
as
VAR_CONTACONTABIL varchar2(40);
VAR_DESCRICAO varchar2(40);
begin
select contasaldus into VAR_CONTACONTABIL
from pcontas
where codcoligada=var_codcoligada
and codcolconta=var_codcoligada
and grpconta=var_contafolha
and integrcfunc=var_codigointegracao;
select descricao into VAR_DESCRICAO
from cconta
where codcoligada=var_codcoligada
and codconta=VAR_CONTACONTABIL ;
return(VAR_DESCRICAO);
end;
SQL - Cursor (modelo)
DECLARE @nome_da_tabela varchar(40)
DECLARE meu_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' and table_name like 'P%'
OPEN meu_cursor
FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('delete FROM ' + @nome_da_tabela)
FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela
END
CLOSE meu_cursor
DEALLOCATE meu_cursor
GO
DECLARE meu_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' and table_name like 'P%'
OPEN meu_cursor
FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('delete FROM ' + @nome_da_tabela)
FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela
END
CLOSE meu_cursor
DEALLOCATE meu_cursor
GO
SQL - Execute Plain
Para gerar "Execute Plain" em formato devemos:
1) Executar o Query Analyzer;
2) Executar a linha de comando:
set showplan_text on
go
select *
from pfunc (query de exemplo)
go
set showplan_text off
go
1) Executar o Query Analyzer;
2) Executar a linha de comando:
set showplan_text on
go
select *
from pfunc (query de exemplo)
go
set showplan_text off
go
SQL - Lock 2005
Artigos: Entenda e utilize Row Versioning no SQL Server 2005
Postado em Segunda-feira, 27 de Novembro de 2006 (7:00:00) por niltonpinheiro
RFernandes enviou "Um dos grandes paradigmas do SQL Server 2000 é como aumentar a concorrência sem que ocorra locks desnecessários? Para isso existem os isolation levels, porém nem sempre são efecientes e nos atendem completamente. Neste artigo demonstro como aumentar a concorrência utilizando o novo recurso do SQL Server 2005, o Row Versioning.
O SQL Server 2005 implementa o recurso de “row versioning”, que consiste em gerar uma versão da “linha” antes de sua alteração, permitindo assim que outras transações que estejam acessando a mesma “linha” enxerguem a versão anterior. Evitando assim um “lock wait” e aumentando a concorrência, pois a versão não é retirada até que a transação que a está alterando realize um COMMIT.
O row versioning pode ser implementado de duas formas:
READ_COMMITTED_SNAPSHOT:
Esta opção é ativada no nível banco de dados e quando ativa faz com que todas as transações em modo default READ_COMMITED, acessem o “row versioning”.
Quando habilitamos esta opção no database ele passa a versionar todas as “linhas” que por ventura venham a ser alteradas e todas as transações que necessitam acessar esta “linha” farão acesso ao “row versioning”, até que a transação que iniciou uma alteração realize o commit.
Para que vocês possam entender melhor, vamos a um exemplo utilizando o database AdventureWorks. Se preferir você pode utilizar qualquer outro banco de dados.
O primeiro passo é habilitar o “row versioning”, no modo READ_COMMITTED_SNAPSHOT. Para isso basta executar o script abaixo.
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
Vamos criar uma tabela para realizarmos os testes:
CREATE TABLE Teste (idx int Identity(1,1), valor int);
Agora vamos inserir algumas linhas nesta tabela:
INSERT INTO Teste VALUES (10);
INSERT INTO Teste VALUES (20);
INSERT INTO Teste VALUES (30);
Agora, ainda na sessão corrente execute o script abaixo para efetuar um UPDATE no registro de valor 10. Note que o UPDATE altera a “linha” que estava com valor 10 para 11.
BEGIN TRANSACTION
UPDATE Teste SET valor = 11
WHERE valor = 10
IF @@ERROR > 0
--ROLLBACK TRANSACTION
ELSE
--COMMIT TRANSACTION
Após a execução do UPDATE, ainda na mesma sessão, vamos consultar os dados:
SELECT * FROM Teste
No resultado podemos observar que a “linha 1” teve o valor alterado de 10 para 11, mesmo sem realizarmos um COMMIT.
Agora, abra uma nova sessão e execute a mesma consulta. Note que a consulta retorna que a “linha 1” continua com valor 10. Isso ocorre porque a transação que disparou o UPDATE anterior ainda não realizou um COMMIT. Sendo assim, esta sessão está acessando o row versioning. Observe também que diferente do que aconteceria no SQL Server 2000, sua sessão não ficou bloqueada, aguardando pela conclusão do UPDATE.
Vantagem
Uma das grandes vantagens da utilização do READ_COMMITTED_SNAPSHOT é que não precisamos declarar nenhum isolation level no nível de sessão para que as sessões utilizem o “row versioning”.
Desvantagem
Muitas vezes pode não ser interessante que uma transação em modo default READ_COMMITTED, consulte um valor antigo, pois este pode vir a gerar futuras inconsistências.
ALLOW_SNAPSHOT_ISOLATION:
Esta opção também é ativada no nível de banco de dados mas diferente da opção anterior, requer que todas as sessões que querem fazer uso do row versioning iniciem a sessão com o comando SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Isso limita as transações que farão acesso ao “row versioning”, controlando melhor a integridade da transação e também o uso do TEMPDB.
O exemplo abaixo habilita o “row versioning”, no modo ALLOW_SNAPSHOT_ISOLATION.
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
Agora vamos utilizar à mesma tabela que criamos no exemplo anterior. Observem a utilização do SET TRANSACTION ISOLATION LEVEL SNAPSHOT no início do script.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Teste SET valor = 21
WHERE valor = 20
IF @@ERROR > 0
--ROLLBACK TRANSACTION
ELSE
--COMMIT TRANSACTION
Após a execução do UPDATE, nesta mesma sessão, vamos consultar a tabela para verificar como ficou a “linha” afetada.
SELECT * FROM Teste
Como podemos observar na figura abaixo, para esta sessão o valor 20 foi alterado para 21, conforme solicitamos, porém ainda não realizamos um COMMIT.
Agora vamos abrir uma nova sessão e efetuar um novo SELECT na tabela. Observem novamente a utilização do
SET TRANSACTION ISOLATION LEVEL SNAPSHOT no início do script.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM Teste;
Note que para esta sessão o valor continua como 20, isso porque ao efetuar o SELECT esta sessão utilizou o row versioning. Outro ponto de observação é que a sessão não ficou bloqueada pela sessão que está executando o UPDATE, como aconteceria se estivéssemos usando o SQL Server 2000.
Caso não tivéssemos declarado o isolation level snapshot no inicio do script, aí sim a sessão entraria em estado de “lock wait” e ficaria aguardando pela conclusão do UPDATE (o COMMIT) da sessão 1. Tendo assim o mesmo comportamento que no SQL Server 2000.
Abaixo temos uma tabela (retirada do Books Online do SQL Server 2005) que sumariza as diferenças entre READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION.
Vantagem
Com a utilização do ALLOW_SNAPSHOT_ISOLATION temos um melhor controle de cada transação, ou seja, podemos decidir qual transação usará ou não o row versioning.
Importante
Os tipos de “row versioning”, podem trabalhar de forma individual ou juntos, ou seja, podemos habilitar READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION no mesmo database.
O “row versioning” utiliza o Tempdb para fazer o versionamento. Porém para peuqenas transações o SQL Server 2005 salva a versão no buffer pool, não sendo necessário escrever no Tempdb e evitando assim I/O overhead desnecessário, para acessá-lo. De qualquer forma, caso pretenda trabalhar com row version é preciso ficar atento à utilização do Tempdb.
Para maiores informações sobre row versioning, consulte o tópico row versioning [SQL Server] no Books Online do SQL Server 20005.
Um Abraço e até a próxima!
Rodrigo Fernandes (RFernandes)
Postado em Segunda-feira, 27 de Novembro de 2006 (7:00:00) por niltonpinheiro
RFernandes enviou "Um dos grandes paradigmas do SQL Server 2000 é como aumentar a concorrência sem que ocorra locks desnecessários? Para isso existem os isolation levels, porém nem sempre são efecientes e nos atendem completamente. Neste artigo demonstro como aumentar a concorrência utilizando o novo recurso do SQL Server 2005, o Row Versioning.
O SQL Server 2005 implementa o recurso de “row versioning”, que consiste em gerar uma versão da “linha” antes de sua alteração, permitindo assim que outras transações que estejam acessando a mesma “linha” enxerguem a versão anterior. Evitando assim um “lock wait” e aumentando a concorrência, pois a versão não é retirada até que a transação que a está alterando realize um COMMIT.
O row versioning pode ser implementado de duas formas:
READ_COMMITTED_SNAPSHOT:
Esta opção é ativada no nível banco de dados e quando ativa faz com que todas as transações em modo default READ_COMMITED, acessem o “row versioning”.
Quando habilitamos esta opção no database ele passa a versionar todas as “linhas” que por ventura venham a ser alteradas e todas as transações que necessitam acessar esta “linha” farão acesso ao “row versioning”, até que a transação que iniciou uma alteração realize o commit.
Para que vocês possam entender melhor, vamos a um exemplo utilizando o database AdventureWorks. Se preferir você pode utilizar qualquer outro banco de dados.
O primeiro passo é habilitar o “row versioning”, no modo READ_COMMITTED_SNAPSHOT. Para isso basta executar o script abaixo.
ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON;
Vamos criar uma tabela para realizarmos os testes:
CREATE TABLE Teste (idx int Identity(1,1), valor int);
Agora vamos inserir algumas linhas nesta tabela:
INSERT INTO Teste VALUES (10);
INSERT INTO Teste VALUES (20);
INSERT INTO Teste VALUES (30);
Agora, ainda na sessão corrente execute o script abaixo para efetuar um UPDATE no registro de valor 10. Note que o UPDATE altera a “linha” que estava com valor 10 para 11.
BEGIN TRANSACTION
UPDATE Teste SET valor = 11
WHERE valor = 10
IF @@ERROR > 0
--ROLLBACK TRANSACTION
ELSE
--COMMIT TRANSACTION
Após a execução do UPDATE, ainda na mesma sessão, vamos consultar os dados:
SELECT * FROM Teste
No resultado podemos observar que a “linha 1” teve o valor alterado de 10 para 11, mesmo sem realizarmos um COMMIT.
Agora, abra uma nova sessão e execute a mesma consulta. Note que a consulta retorna que a “linha 1” continua com valor 10. Isso ocorre porque a transação que disparou o UPDATE anterior ainda não realizou um COMMIT. Sendo assim, esta sessão está acessando o row versioning. Observe também que diferente do que aconteceria no SQL Server 2000, sua sessão não ficou bloqueada, aguardando pela conclusão do UPDATE.
Vantagem
Uma das grandes vantagens da utilização do READ_COMMITTED_SNAPSHOT é que não precisamos declarar nenhum isolation level no nível de sessão para que as sessões utilizem o “row versioning”.
Desvantagem
Muitas vezes pode não ser interessante que uma transação em modo default READ_COMMITTED, consulte um valor antigo, pois este pode vir a gerar futuras inconsistências.
ALLOW_SNAPSHOT_ISOLATION:
Esta opção também é ativada no nível de banco de dados mas diferente da opção anterior, requer que todas as sessões que querem fazer uso do row versioning iniciem a sessão com o comando SET TRANSACTION ISOLATION LEVEL SNAPSHOT. Isso limita as transações que farão acesso ao “row versioning”, controlando melhor a integridade da transação e também o uso do TEMPDB.
O exemplo abaixo habilita o “row versioning”, no modo ALLOW_SNAPSHOT_ISOLATION.
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON;
Agora vamos utilizar à mesma tabela que criamos no exemplo anterior. Observem a utilização do SET TRANSACTION ISOLATION LEVEL SNAPSHOT no início do script.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
UPDATE Teste SET valor = 21
WHERE valor = 20
IF @@ERROR > 0
--ROLLBACK TRANSACTION
ELSE
--COMMIT TRANSACTION
Após a execução do UPDATE, nesta mesma sessão, vamos consultar a tabela para verificar como ficou a “linha” afetada.
SELECT * FROM Teste
Como podemos observar na figura abaixo, para esta sessão o valor 20 foi alterado para 21, conforme solicitamos, porém ainda não realizamos um COMMIT.
Agora vamos abrir uma nova sessão e efetuar um novo SELECT na tabela. Observem novamente a utilização do
SET TRANSACTION ISOLATION LEVEL SNAPSHOT no início do script.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM Teste;
Note que para esta sessão o valor continua como 20, isso porque ao efetuar o SELECT esta sessão utilizou o row versioning. Outro ponto de observação é que a sessão não ficou bloqueada pela sessão que está executando o UPDATE, como aconteceria se estivéssemos usando o SQL Server 2000.
Caso não tivéssemos declarado o isolation level snapshot no inicio do script, aí sim a sessão entraria em estado de “lock wait” e ficaria aguardando pela conclusão do UPDATE (o COMMIT) da sessão 1. Tendo assim o mesmo comportamento que no SQL Server 2000.
Abaixo temos uma tabela (retirada do Books Online do SQL Server 2005) que sumariza as diferenças entre READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION.
Vantagem
Com a utilização do ALLOW_SNAPSHOT_ISOLATION temos um melhor controle de cada transação, ou seja, podemos decidir qual transação usará ou não o row versioning.
Importante
Os tipos de “row versioning”, podem trabalhar de forma individual ou juntos, ou seja, podemos habilitar READ_COMMITTED_SNAPSHOT e ALLOW_SNAPSHOT_ISOLATION no mesmo database.
O “row versioning” utiliza o Tempdb para fazer o versionamento. Porém para peuqenas transações o SQL Server 2005 salva a versão no buffer pool, não sendo necessário escrever no Tempdb e evitando assim I/O overhead desnecessário, para acessá-lo. De qualquer forma, caso pretenda trabalhar com row version é preciso ficar atento à utilização do Tempdb.
Para maiores informações sobre row versioning, consulte o tópico row versioning [SQL Server] no Books Online do SQL Server 20005.
Um Abraço e até a próxima!
Rodrigo Fernandes (RFernandes)
SQL - Lista Trigger
select TB.name 'table', TR.name 'trigger'
from sysobjects TR, sysobjects TB
where tr.type = 'TR'
and TR.parent_obj = TB.ID
from sysobjects TR, sysobjects TB
where tr.type = 'TR'
and TR.parent_obj = TB.ID
SQL - Foreign Key (Exemplo)
/****** Object: Stored Procedure dbo.Habilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Habilita_ForeignKey') and sysstat & 0xf = 4)
drop procedure dbo.Habilita_ForeignKey
GO
/****** Object: Stored Procedure dbo.Habilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Desabilita_ForeignKey') and sysstat & 0xf = 4)
drop procedure dbo.Desabilita_ForeignKey
GO
/****** Object: Stored Procedure dbo.Habilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
CREATE PROCEDURE Habilita_ForeignKey
AS
DECLARE @tabelaname varchar(100)
DECLARE @tabelaid int
DECLARE @constraintname varchar(100)
DECLARE @constraintcabecalho varchar(100)
DECLARE tabela_cursor CURSOR FOR select o.name,o.id
from sysobjects o
where o.type in ('U') order by o.name
OPEN tabela_cursor
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE constraint_cursor CURSOR FOR select o.name
from sysconstraints c, sysobjects o
where c.id = @tabelaid and o.id = c.constid and o.type in ('F')
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraintname
WHILE (@@fetch_status <> -1) BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @constraintcabecalho = 'Habilitando constraint ' + RTRIM(UPPER(@constraintname)) + ' da tabela ' + @tabelaname
PRINT @constraintcabecalho
exec ('ALTER TABLE ' + @tabelaname + ' CHECK CONSTRAINT ' + @constraintname)
PRINT ' '
END
FETCH NEXT FROM constraint_cursor INTO @constraintname
END
DEALLOCATE constraint_cursor
END
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
END
DEALLOCATE tabela_cursor
PRINT ' '
PRINT ' '
PRINT 'Todas a foreigns keys foram habilitadas'
GO
/****** Object: Stored Procedure dbo.Desabilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
CREATE PROCEDURE Desabilita_ForeignKey
AS
DECLARE @tabelaname varchar(100)
DECLARE @tabelaid int
DECLARE @constraintname varchar(100)
DECLARE @constraintcabecalho varchar(100)
DECLARE tabela_cursor CURSOR FOR select o.name,o.id
from sysobjects o
where o.type in ('U') order by o.name
OPEN tabela_cursor
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE constraint_cursor CURSOR FOR select o.name
from sysconstraints c, sysobjects o
where c.id = @tabelaid and o.id = c.constid and o.type in ('F')
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraintname
WHILE (@@fetch_status <> -1) BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @constraintcabecalho = 'Desabilitando constraint ' + RTRIM(UPPER(@constraintname)) + ' da tabela ' + @tabelaname
PRINT @constraintcabecalho
EXEC ('ALTER TABLE ' + @tabelaname + ' NOCHECK CONSTRAINT ' + @constraintname)
PRINT ' '
END
FETCH NEXT FROM constraint_cursor INTO @constraintname
END
DEALLOCATE constraint_cursor
END
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
END
DEALLOCATE tabela_cursor
PRINT ' '
PRINT ' '
PRINT 'Todas a foreigns keys foram desabilitadas'
GO
if exists (select * from sysobjects where id = object_id('dbo.Habilita_ForeignKey') and sysstat & 0xf = 4)
drop procedure dbo.Habilita_ForeignKey
GO
/****** Object: Stored Procedure dbo.Habilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.Desabilita_ForeignKey') and sysstat & 0xf = 4)
drop procedure dbo.Desabilita_ForeignKey
GO
/****** Object: Stored Procedure dbo.Habilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
CREATE PROCEDURE Habilita_ForeignKey
AS
DECLARE @tabelaname varchar(100)
DECLARE @tabelaid int
DECLARE @constraintname varchar(100)
DECLARE @constraintcabecalho varchar(100)
DECLARE tabela_cursor CURSOR FOR select o.name,o.id
from sysobjects o
where o.type in ('U') order by o.name
OPEN tabela_cursor
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE constraint_cursor CURSOR FOR select o.name
from sysconstraints c, sysobjects o
where c.id = @tabelaid and o.id = c.constid and o.type in ('F')
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraintname
WHILE (@@fetch_status <> -1) BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @constraintcabecalho = 'Habilitando constraint ' + RTRIM(UPPER(@constraintname)) + ' da tabela ' + @tabelaname
PRINT @constraintcabecalho
exec ('ALTER TABLE ' + @tabelaname + ' CHECK CONSTRAINT ' + @constraintname)
PRINT ' '
END
FETCH NEXT FROM constraint_cursor INTO @constraintname
END
DEALLOCATE constraint_cursor
END
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
END
DEALLOCATE tabela_cursor
PRINT ' '
PRINT ' '
PRINT 'Todas a foreigns keys foram habilitadas'
GO
/****** Object: Stored Procedure dbo.Desabilita_ForeignKey Script Date: 2/2/00 12:47:17 PM ******/
CREATE PROCEDURE Desabilita_ForeignKey
AS
DECLARE @tabelaname varchar(100)
DECLARE @tabelaid int
DECLARE @constraintname varchar(100)
DECLARE @constraintcabecalho varchar(100)
DECLARE tabela_cursor CURSOR FOR select o.name,o.id
from sysobjects o
where o.type in ('U') order by o.name
OPEN tabela_cursor
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE constraint_cursor CURSOR FOR select o.name
from sysconstraints c, sysobjects o
where c.id = @tabelaid and o.id = c.constid and o.type in ('F')
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @constraintname
WHILE (@@fetch_status <> -1) BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @constraintcabecalho = 'Desabilitando constraint ' + RTRIM(UPPER(@constraintname)) + ' da tabela ' + @tabelaname
PRINT @constraintcabecalho
EXEC ('ALTER TABLE ' + @tabelaname + ' NOCHECK CONSTRAINT ' + @constraintname)
PRINT ' '
END
FETCH NEXT FROM constraint_cursor INTO @constraintname
END
DEALLOCATE constraint_cursor
END
FETCH NEXT FROM tabela_cursor INTO @tabelaname,@tabelaid
END
DEALLOCATE tabela_cursor
PRINT ' '
PRINT ' '
PRINT 'Todas a foreigns keys foram desabilitadas'
GO
SQL / RM - Base Vazia
Prezados,
Quando se for no cliente para criar uma base vazia, ou fazer manutenção no SGDB (Sistema Gerenciador de Banco de Dados) SQL 7.0 ou SQL 2000, não podemos esquecer que analisar e conferir todos os parâmetros de configuração.
Para criação de uma base de dados ou correção da mesma, sugerimos que realizem a seguinte conferência na base de dados do Corpore:
Verifique nas propriedades do banco de dados, na guia Data Files e Transaction Log se está parametrizado para crescer a base automaticamente sem limitação de tamanho;
Na guia options...
SQL 7.0 - Marcar somente as opções Ansi Null Default, Truncate log on checkpoint, Auto Close e Auto Shirink;
SQL 2000 - No Recovery/Model colocar como Simple (somente se não realiza backup de log); e marcar somente as opções Ansi Null Default, Auto Close e Auto Shrink;
Confira se existe espaço suficiente para o crescimento da base no drive em que a mesma está localizada;
Quando se for no cliente para criar uma base vazia, ou fazer manutenção no SGDB (Sistema Gerenciador de Banco de Dados) SQL 7.0 ou SQL 2000, não podemos esquecer que analisar e conferir todos os parâmetros de configuração.
Para criação de uma base de dados ou correção da mesma, sugerimos que realizem a seguinte conferência na base de dados do Corpore:
Verifique nas propriedades do banco de dados, na guia Data Files e Transaction Log se está parametrizado para crescer a base automaticamente sem limitação de tamanho;
Na guia options...
SQL 7.0 - Marcar somente as opções Ansi Null Default, Truncate log on checkpoint, Auto Close e Auto Shirink;
SQL 2000 - No Recovery/Model colocar como Simple (somente se não realiza backup de log); e marcar somente as opções Ansi Null Default, Auto Close e Auto Shrink;
Confira se existe espaço suficiente para o crescimento da base no drive em que a mesma está localizada;
SQL \ RM - Criptograma
Pessoal,
Conforme solicitado várias vezes a RM Sistemas, alguns clientes solicitam que informações importantes, tais como: valores e datas sejam mascaradas, ou seja, anuladas. O principal intuito é a preservação e sigilo de informações cadastradas na base de dados. Sendo assim, criamos o procedimento abaixo que fará todo o processo, sendo que haverá necessidade apenas de pré-determinar quais tabelas e quais informações deverão ser anuladas.
veja exemplo abaixo:
Dentro do cursor criado na estrutura da procedure, filtrei as seguintes informações a serem anuladas:
P% => Limitei apenas nas tabelas que começam com a letra P, neste caso tabelas do RM-Labore
SALA% => Cujo campos começam com SALA ou
VALOR% => cujo campos começam com VALOR ou
DATA% => cujo campos começam com DATA
Obs.: Dentro deste cursor o usuário poderá definir vários filtros de acordo com a necessidade.
DECLARE @TABELA VARCHAR(20)
DECLARE @CAMPO VARCHAR(20)
DECLARE @COMANDO VARCHAR(255)
DECLARE INFO CURSOR FOR
SELECT T.NAME,C.NAME FROM SYSOBJECTS T, SYSCOLUMNS C
WHERE T.ID=C.ID AND T.NAME LIKE 'P%' AND (C.NAME LIKE 'SALA%' OR C.NAME LIKE 'VALOR%'OR C.NAME LIKE 'DATA%')
OPEN INFO
FETCH NEXT FROM INFO INTO @TABELA,@CAMPO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COMANDO = 'UPDATE ' + @TABELA + ' SET ' + ''+@CAMPO+'' + ' = NULL '
EXEC ( @COMANDO)
PRINT 'O campo '+@campo+' da tabela '+@tabela +' foi anulado.'
FETCH NEXT FROM INFO INTO @TABELA,@CAMPO
END
CLOSE INFO
DEALLOCATE INFO
Conforme solicitado várias vezes a RM Sistemas, alguns clientes solicitam que informações importantes, tais como: valores e datas sejam mascaradas, ou seja, anuladas. O principal intuito é a preservação e sigilo de informações cadastradas na base de dados. Sendo assim, criamos o procedimento abaixo que fará todo o processo, sendo que haverá necessidade apenas de pré-determinar quais tabelas e quais informações deverão ser anuladas.
veja exemplo abaixo:
Dentro do cursor criado na estrutura da procedure, filtrei as seguintes informações a serem anuladas:
P% => Limitei apenas nas tabelas que começam com a letra P, neste caso tabelas do RM-Labore
SALA% => Cujo campos começam com SALA ou
VALOR% => cujo campos começam com VALOR ou
DATA% => cujo campos começam com DATA
Obs.: Dentro deste cursor o usuário poderá definir vários filtros de acordo com a necessidade.
DECLARE @TABELA VARCHAR(20)
DECLARE @CAMPO VARCHAR(20)
DECLARE @COMANDO VARCHAR(255)
DECLARE INFO CURSOR FOR
SELECT T.NAME,C.NAME FROM SYSOBJECTS T, SYSCOLUMNS C
WHERE T.ID=C.ID AND T.NAME LIKE 'P%' AND (C.NAME LIKE 'SALA%' OR C.NAME LIKE 'VALOR%'OR C.NAME LIKE 'DATA%')
OPEN INFO
FETCH NEXT FROM INFO INTO @TABELA,@CAMPO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @COMANDO = 'UPDATE ' + @TABELA + ' SET ' + ''+@CAMPO+'' + ' = NULL '
EXEC ( @COMANDO)
PRINT 'O campo '+@campo+' da tabela '+@tabela +' foi anulado.'
FETCH NEXT FROM INFO INTO @TABELA,@CAMPO
END
CLOSE INFO
DEALLOCATE INFO
SQL - Case (Exemplo)
(SELECT 'diasemana' =
CASE
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 1 THEN 'Domingo'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 2 THEN 'Segunda'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 3 THEN 'Terça'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 4 THEN 'Quarta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 5 THEN 'Quinta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 6 THEN 'Sexta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 7 THEN 'Sábado'
END) DIASEMANA,
CASE
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 1 THEN 'Domingo'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 2 THEN 'Segunda'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 3 THEN 'Terça'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 4 THEN 'Quarta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 5 THEN 'Quinta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 6 THEN 'Sexta'
WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 7 THEN 'Sábado'
END) DIASEMANA,
SQL - Backup Automático
Para a realização de um backup automático pelo SQL:
- Entrar no Entreprise Manager do SQL (Iniciar - Programas - Microsoft SQL Server 7.0 - Entreprise Manager)
- Na database que deseja o backup automático, clicar com o botão direito, e selecionar a opção "All Tasks - Backup Database".
- Defina, o caminho em que será gravado o backup, e defina a opção Overwrite. Ex: A opção "Append to media", anexa o novo backup a um backup já existente, ao contrário da opção "Overwrite existing media", que sobregrava o backup atual no arquivo selecionado.
- Marque a opção "Schedule", e em seguida clique no botão de "Edição de Schedule".
- Na tela "Edit Schedule", marque a opção "Recurring", e clique no botão "Change".
- Na tela "Edit Recurring Job Schedule", na opção "Occurs", marque a opção "Daily". Na opção "Daily Frequency", vamos marcar a opção "Occurs every", e definiremos de quanto em quanto tempo (Horas ou Minutos), o sistema realizará o backup. Ex: 2 Hours. (O sistema realziará o backup num intervalo de 2 horas)
A opção Starting At e Ending At, determinará o período em que o sistema trabalhará para realizar o backup, por exemplo "Starting At - 00:00" e "Ending At - 23:59".
A opção "Start Date", determinará a partir de qual data o sistema passará a efetuar o backup. E a opção "End Date", a data final em que o sistema deixará de realizar o backup automático. É recomendado que deixe a opção "No end date" habilitado para que o sistema não faça consistência na data final.
- Entrar no Entreprise Manager do SQL (Iniciar - Programas - Microsoft SQL Server 7.0 - Entreprise Manager)
- Na database que deseja o backup automático, clicar com o botão direito, e selecionar a opção "All Tasks - Backup Database".
- Defina, o caminho em que será gravado o backup, e defina a opção Overwrite. Ex: A opção "Append to media", anexa o novo backup a um backup já existente, ao contrário da opção "Overwrite existing media", que sobregrava o backup atual no arquivo selecionado.
- Marque a opção "Schedule", e em seguida clique no botão de "Edição de Schedule".
- Na tela "Edit Schedule", marque a opção "Recurring", e clique no botão "Change".
- Na tela "Edit Recurring Job Schedule", na opção "Occurs", marque a opção "Daily". Na opção "Daily Frequency", vamos marcar a opção "Occurs every", e definiremos de quanto em quanto tempo (Horas ou Minutos), o sistema realizará o backup. Ex: 2 Hours. (O sistema realziará o backup num intervalo de 2 horas)
A opção Starting At e Ending At, determinará o período em que o sistema trabalhará para realizar o backup, por exemplo "Starting At - 00:00" e "Ending At - 23:59".
A opção "Start Date", determinará a partir de qual data o sistema passará a efetuar o backup. E a opção "End Date", a data final em que o sistema deixará de realizar o backup automático. É recomendado que deixe a opção "No end date" habilitado para que o sistema não faça consistência na data final.
SQL - RM Chronus (Exemplo) - Arno
delete from funchor
declare @codcoligada int
declare @chapa varchar(20)
declare @codsituacao varchar(20)
--set @chapa ='0276626'
DECLARE CURSOR_CHAPA CURSOR FOR
SELECT CODCOLIGADA,CHAPA,CODSITUACAO FROM PFUNC WHERE CODSITUACAO IN ('T','P','E','M','F','I')
and chapa ='0461376' --('0263186','0266619)
OPEN CURSOR_CHAPA
FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
WHILE (@@FETCH_STATUS<>-1)
BEGIN
delete from calendario
declare @codhorario varchar(10)
declare @chapa2 varchar(20)
declare @contador int
declare @nrodiashor int
declare @indiniciohorcont int
declare @inicioindice int
declare cursor_dias cursor for
SELECT B.CODCOLIGADA,B.CHAPA,B.CODHORARIO,B.INDINICIOHOR,DATEDIFF(DD,d.iniciopermes,GETDATE())
FROM PFUNC B,AHORARIO C,aparam d
WHERE B.codcoligada=@CODCOLIGADA and B.CHAPA=@CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
and b.codcoligada=d.codcoligada
open cursor_dias
fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
set @contador=@nrodiashor
select @indiniciohorcont=@inicioindice
while @contador<>-1
begin
--drop table calendario
--create table calendario (codcoligada int,chapa varchar(20),data datetime,codhorario varchar(10),indice int,semana varchar(10), constraint pkcalendario primary key (codcoligada,data,codhorario,indice,semana))
insert into calendario
select @codcoligada,@chapa2,
convert(datetime,convert(varchar,datepart(yyyy,getdate()))+'/'+
convert(varchar,datepart(mm,getdate()))+'/'+
convert(varchar,datepart(dd,getdate())))-@contador,@codhorario,@indiniciohorcont,
datename(dw,getdate()-@contador)
/*contadores*/
if @indiniciohorcont>=(SELECT MAX(INDICE) FROM ABATHOR WHERE CODHORARIO=@CODHORARIO)
begin
select @indiniciohorcont=(select distinct min(indice)
from abathor where codhorario=@codhorario)
end
else select @indiniciohorcont=@indiniciohorcont+1
select @contador=@contador-1
fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
end
deallocate cursor_dias
IF @CODSITUACAO = 'F'
BEGIN
delete from func
declare @codcoligada2 int
declare @codhorario2 varchar(10)
declare @chapa3 varchar(20)
declare @contador2 int
declare @nrodiasaft1 int
declare @dtinicio1 datetime
declare cursor_comparacao1 cursor for
SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
when dtfIMGOZO IS null
then datediff(dd,DTINIGOZO,getdate())
else datediff(dd,dtiniGOZO,dtfiMGOZO)
end ,dtiniGOZO
FROM PFHSTFER A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@codcoligada and A.CHAPA=@chapa
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINIGOZO=(SELECT MAX(DTINIGOZO) FROM PFHSTFER WHERE codcoligada=@codcoligada AND CHAPA=@chapa)
open cursor_comparacao1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
set @contador2=@nrodiasaft1
while @contador2<>-1
begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key (codcoligada,chapa,codhorario,data))
--delete from func
insert into func
select @codcoligada2,@chapa3,@codhorario2,@dtinicio1+@contador2
select @contador2=@contador2-1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
end
deallocate cursor_comparacao1
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where a.codcoligada=@codcoligada
and a.codcoligada=b.codcoligada
and a.chapa=b.chapa
and a.codhorario=b.codhorario
and a.data=b.data
and c.codcoligada=b.codcoligada
and c.codhorario=b.codhorario
and c.indice=b.indice
and a.codcoligada=d.codcoligada
and a.chapa=d.chapa
and c.tipo in (0)
and b.semana <> 'Sunday'
and b.data not in (select gferiado.diaferiado from gferiado)
and b.data not in
(select data from aafhtfun,aparam where
aafhtfun.codcoligada=@codcoligada and aafhtfun.chapa=@chapa
and data between iniciopermes and getdate())
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data
END
ELSE
if @codsituacao in ('T','P','E','M')
/*SEGUNDA PARTE - COMPARACAO*/
begin
delete from func
declare @codcoligada1 int
declare @codhorario1 varchar(10)
declare @chapa1 varchar(20)
declare @contador1 int
declare @nrodiasaft int
declare @dtinicio datetime
declare cursor_comparacao cursor for
SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
when dtfinal IS null
then datediff(dd,DTINICIO,getdate())
else datediff(dd,dtinicio,dtfinal)
end ,dtinicio
FROM PFHSTAFT A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@CODCOLIGADA and A.CHAPA=@CHAPA
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINICIO=(SELECT MAX(DTINICIO) FROM PFHSTAFT WHERE CODCOLIGADA=@CODCOLIGADA AND CHAPA=@CHAPA)
open cursor_comparacao
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
set @contador1=@nrodiasaft
while @contador1<>-1
begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key (codcoligada,chapa,codhorario,data))
insert into func
select @codcoligada1,@chapa1,@codhorario1,@dtinicio+@contador1
select @contador1=@contador1-1
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
end
deallocate cursor_comparacao
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where a.codcoligada=@codcoligada
and a.codcoligada=b.codcoligada
and a.chapa=b.chapa
and a.codhorario=b.codhorario
and a.data=b.data
and c.codcoligada=b.codcoligada
and c.codhorario=b.codhorario
and c.indice=b.indice
and a.codcoligada=d.codcoligada
and a.chapa=d.chapa
and c.tipo in (0)--(c.tipo<>1 and c.tipo<>3)-- and c.tipo<>4)
and b.semana <> 'Sunday'
and b.data not in (select gferiado.diaferiado from gferiado)
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data
end
FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
END
DEALLOCATE CURSOR_CHAPA
/*select * from gferiado where diaferiado='2003-07-04'
select * from abathor where codhorario='20'*/
/*select *
from FUNCHOR where data between '2003-06-16' and '2003-07-15' order by chapa , data
*/
/*
select * from abatfunam WHERE CHAPA = '0334078'
select a.codcoligada,a.chapa from pfunc a where exists
(select 1 from aafhtfun b where a.codcoligada=b.codcoligada and a.chapa=b.chapa)
and codsituacao = 'P'
select * from gferiado
order by diaferiado
select * from abathor where CODHORARIO='30' order by INDICE
*/
declare @codcoligada int
declare @chapa varchar(20)
declare @codsituacao varchar(20)
--set @chapa ='0276626'
DECLARE CURSOR_CHAPA CURSOR FOR
SELECT CODCOLIGADA,CHAPA,CODSITUACAO FROM PFUNC WHERE CODSITUACAO IN ('T','P','E','M','F','I')
and chapa ='0461376' --('0263186','0266619)
OPEN CURSOR_CHAPA
FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
WHILE (@@FETCH_STATUS<>-1)
BEGIN
delete from calendario
declare @codhorario varchar(10)
declare @chapa2 varchar(20)
declare @contador int
declare @nrodiashor int
declare @indiniciohorcont int
declare @inicioindice int
declare cursor_dias cursor for
SELECT B.CODCOLIGADA,B.CHAPA,B.CODHORARIO,B.INDINICIOHOR,DATEDIFF(DD,d.iniciopermes,GETDATE())
FROM PFUNC B,AHORARIO C,aparam d
WHERE B.codcoligada=@CODCOLIGADA and B.CHAPA=@CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
and b.codcoligada=d.codcoligada
open cursor_dias
fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
set @contador=@nrodiashor
select @indiniciohorcont=@inicioindice
while @contador<>-1
begin
--drop table calendario
--create table calendario (codcoligada int,chapa varchar(20),data datetime,codhorario varchar(10),indice int,semana varchar(10), constraint pkcalendario primary key (codcoligada,data,codhorario,indice,semana))
insert into calendario
select @codcoligada,@chapa2,
convert(datetime,convert(varchar,datepart(yyyy,getdate()))+'/'+
convert(varchar,datepart(mm,getdate()))+'/'+
convert(varchar,datepart(dd,getdate())))-@contador,@codhorario,@indiniciohorcont,
datename(dw,getdate()-@contador)
/*contadores*/
if @indiniciohorcont>=(SELECT MAX(INDICE) FROM ABATHOR WHERE CODHORARIO=@CODHORARIO)
begin
select @indiniciohorcont=(select distinct min(indice)
from abathor where codhorario=@codhorario)
end
else select @indiniciohorcont=@indiniciohorcont+1
select @contador=@contador-1
fetch next from cursor_dias into @codcoligada,@chapa2,@codhorario,@inicioindice,@nrodiashor
end
deallocate cursor_dias
IF @CODSITUACAO = 'F'
BEGIN
delete from func
declare @codcoligada2 int
declare @codhorario2 varchar(10)
declare @chapa3 varchar(20)
declare @contador2 int
declare @nrodiasaft1 int
declare @dtinicio1 datetime
declare cursor_comparacao1 cursor for
SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
when dtfIMGOZO IS null
then datediff(dd,DTINIGOZO,getdate())
else datediff(dd,dtiniGOZO,dtfiMGOZO)
end ,dtiniGOZO
FROM PFHSTFER A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@codcoligada and A.CHAPA=@chapa
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINIGOZO=(SELECT MAX(DTINIGOZO) FROM PFHSTFER WHERE codcoligada=@codcoligada AND CHAPA=@chapa)
open cursor_comparacao1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
set @contador2=@nrodiasaft1
while @contador2<>-1
begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key (codcoligada,chapa,codhorario,data))
--delete from func
insert into func
select @codcoligada2,@chapa3,@codhorario2,@dtinicio1+@contador2
select @contador2=@contador2-1
fetch next from cursor_comparacao1 into @codcoligada2,@chapa3,@codhorario2,@nrodiasaft1,@dtinicio1
end
deallocate cursor_comparacao1
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where a.codcoligada=@codcoligada
and a.codcoligada=b.codcoligada
and a.chapa=b.chapa
and a.codhorario=b.codhorario
and a.data=b.data
and c.codcoligada=b.codcoligada
and c.codhorario=b.codhorario
and c.indice=b.indice
and a.codcoligada=d.codcoligada
and a.chapa=d.chapa
and c.tipo in (0)
and b.semana <> 'Sunday'
and b.data not in (select gferiado.diaferiado from gferiado)
and b.data not in
(select data from aafhtfun,aparam where
aafhtfun.codcoligada=@codcoligada and aafhtfun.chapa=@chapa
and data between iniciopermes and getdate())
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data
END
ELSE
if @codsituacao in ('T','P','E','M')
/*SEGUNDA PARTE - COMPARACAO*/
begin
delete from func
declare @codcoligada1 int
declare @codhorario1 varchar(10)
declare @chapa1 varchar(20)
declare @contador1 int
declare @nrodiasaft int
declare @dtinicio datetime
declare cursor_comparacao cursor for
SELECT A.CODCOLIGADA,A.CHAPA,B.CODHORARIO,case
when dtfinal IS null
then datediff(dd,DTINICIO,getdate())
else datediff(dd,dtinicio,dtfinal)
end ,dtinicio
FROM PFHSTAFT A,PFUNC B,AHORARIO C
WHERE A.codcoligada=@CODCOLIGADA and A.CHAPA=@CHAPA
AND A.CODCOLIGADA=B.CODCOLIGADA
AND A.CHAPA=B.CHAPA
AND B.CODCOLIGADA=C.CODCOLIGADA
AND B.CODHORARIO=C.CODIGO
AND A.DTINICIO=(SELECT MAX(DTINICIO) FROM PFHSTAFT WHERE CODCOLIGADA=@CODCOLIGADA AND CHAPA=@CHAPA)
open cursor_comparacao
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
set @contador1=@nrodiasaft
while @contador1<>-1
begin
--drop table func
--create table func (codcoligada int,chapa varchar(20),codhorario varchar(10),data datetime, constraint pkfunc primary key (codcoligada,chapa,codhorario,data))
insert into func
select @codcoligada1,@chapa1,@codhorario1,@dtinicio+@contador1
select @contador1=@contador1-1
fetch next from cursor_comparacao into @codcoligada1,@chapa1,@codhorario1,@nrodiasaft,@dtinicio
end
deallocate cursor_comparacao
--drop table funchor
--CREATE TABLE FUNCHOR (CHAPA VARCHAR(20),CODHORARIO VARCHAR(10),DATA DATETIME, INDICE INT,SEMANA VARCHAR(10),BASE INT)
INSERT INTO FUNCHOR
select a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
from func a,calendario b,abathor c,
(select codcoligada,chapa,base from aafhtfun
where data = (select max(data) from aafhtfun where codcoligada=@codcoligada and chapa=@chapa)
and codcoligada=@codcoligada and chapa=@chapa)d
where a.codcoligada=@codcoligada
and a.codcoligada=b.codcoligada
and a.chapa=b.chapa
and a.codhorario=b.codhorario
and a.data=b.data
and c.codcoligada=b.codcoligada
and c.codhorario=b.codhorario
and c.indice=b.indice
and a.codcoligada=d.codcoligada
and a.chapa=d.chapa
and c.tipo in (0)--(c.tipo<>1 and c.tipo<>3)-- and c.tipo<>4)
and b.semana <> 'Sunday'
and b.data not in (select gferiado.diaferiado from gferiado)
group by a.chapa,b.codhorario,a.data,b.indice,b.semana,d.base
order by a.data
end
FETCH NEXT FROM CURSOR_CHAPA INTO @CODCOLIGADA,@CHAPA,@CODSITUACAO
END
DEALLOCATE CURSOR_CHAPA
/*select * from gferiado where diaferiado='2003-07-04'
select * from abathor where codhorario='20'*/
/*select *
from FUNCHOR where data between '2003-06-16' and '2003-07-15' order by chapa , data
*/
/*
select * from abatfunam WHERE CHAPA = '0334078'
select a.codcoligada,a.chapa from pfunc a where exists
(select 1 from aafhtfun b where a.codcoligada=b.codcoligada and a.chapa=b.chapa)
and codsituacao = 'P'
select * from gferiado
order by diaferiado
select * from abathor where CODHORARIO='30' order by INDICE
*/
SQL - Linked Server
Prezados,
segue esquema para conexão de Banco de Dados numa outra máquina/servidor, quando necessitamos executar uma Query:
Criar um Acesso :
USE master
GO
EXEC sp_addlinkedserver
'NOME DO SERVIDOR',
N'SQL Server'
GO
EXEC sp_addlinkedsrvlogin 'NOME DO SERVIDOR', 'false', NULL, 'USUÁRIO', 'SENHA'
Para consultar:
SELECT *
FROM OPENQUERY(CUSTFSPSERVER, 'SELECT * FROM FINANCEIRO.DBO.GCOLIGADA')
EXEC sp_addlinkedsrvlogin 'NOME DO SERVIDOR', 'false', NULL, 'USUÁRIO', 'SENHA'
segue esquema para conexão de Banco de Dados numa outra máquina/servidor, quando necessitamos executar uma Query:
Criar um Acesso :
USE master
GO
EXEC sp_addlinkedserver
'NOME DO SERVIDOR',
N'SQL Server'
GO
EXEC sp_addlinkedsrvlogin 'NOME DO SERVIDOR', 'false', NULL, 'USUÁRIO', 'SENHA'
Para consultar:
SELECT *
FROM OPENQUERY(CUSTFSPSERVER, 'SELECT * FROM FINANCEIRO.DBO.GCOLIGADA')
EXEC sp_addlinkedsrvlogin 'NOME DO SERVIDOR', 'false', NULL, 'USUÁRIO', 'SENHA'
Oracle - Exemplos
GRANT CREATE DATABASE LINK TO RM;
/
CREATE TABLE ZTABELASEXCLUIDAS (NOMETABELA VARCHAR(150),
CONSTRAINT PKZTABELASEXCLUIDAS PRIMARY KEY (NOMETABELA));
/
CREATE TABLE ZALIASORIGEM (NOMEALIAS VARCHAR(60),
CODCOLIGADA NUMBER(5),
CONSTRAINT PKZALIASORIGEM PRIMARY KEY (NOMEALIAS));
/
CREATE OR REPLACE PROCEDURE SP_ATIVAFOREIGNKEYS (P_ACAO IN VARCHAR)
AS
P_NOMETABELA VARCHAR(128);
P_CHAVEESTRANGEIRA VARCHAR(128);
P_CURSORNRO INTEGER;
P_EXECUTE INTEGER;
CURSOR CURSORTABELAS IS SELECT TABLE_NAME
FROM USER_TABLES;
CURSOR CURSORCONSTRAINTS IS SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = P_NOMETABELA
AND CONSTRAINT_TYPE = 'R'; /* SOMENTE FOREIGN KEYS */
BEGIN
OPEN CURSORTABELAS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
LOOP
EXIT WHEN CURSORTABELAS%NOTFOUND;
OPEN CURSORCONSTRAINTS;
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
LOOP
EXIT WHEN CURSORCONSTRAINTS%NOTFOUND;
P_CURSORNRO := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(P_CURSORNRO, 'ALTER TABLE ' || P_NOMETABELA || ' ' || P_ACAO || ' CONSTRAINT ' || P_CHAVEESTRANGEIRA, DBMS_SQL.NATIVE);
P_EXECUTE :=DBMS_SQL.EXECUTE(P_CURSORNRO);
DBMS_SQL.CLOSE_CURSOR(P_CURSORNRO);
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
END LOOP;
CLOSE CURSORCONSTRAINTS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
END LOOP;
CLOSE CURSORTABELAS;
END;
/
/
CREATE TABLE ZTABELASEXCLUIDAS (NOMETABELA VARCHAR(150),
CONSTRAINT PKZTABELASEXCLUIDAS PRIMARY KEY (NOMETABELA));
/
CREATE TABLE ZALIASORIGEM (NOMEALIAS VARCHAR(60),
CODCOLIGADA NUMBER(5),
CONSTRAINT PKZALIASORIGEM PRIMARY KEY (NOMEALIAS));
/
CREATE OR REPLACE PROCEDURE SP_ATIVAFOREIGNKEYS (P_ACAO IN VARCHAR)
AS
P_NOMETABELA VARCHAR(128);
P_CHAVEESTRANGEIRA VARCHAR(128);
P_CURSORNRO INTEGER;
P_EXECUTE INTEGER;
CURSOR CURSORTABELAS IS SELECT TABLE_NAME
FROM USER_TABLES;
CURSOR CURSORCONSTRAINTS IS SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = P_NOMETABELA
AND CONSTRAINT_TYPE = 'R'; /* SOMENTE FOREIGN KEYS */
BEGIN
OPEN CURSORTABELAS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
LOOP
EXIT WHEN CURSORTABELAS%NOTFOUND;
OPEN CURSORCONSTRAINTS;
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
LOOP
EXIT WHEN CURSORCONSTRAINTS%NOTFOUND;
P_CURSORNRO := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(P_CURSORNRO, 'ALTER TABLE ' || P_NOMETABELA || ' ' || P_ACAO || ' CONSTRAINT ' || P_CHAVEESTRANGEIRA, DBMS_SQL.NATIVE);
P_EXECUTE :=DBMS_SQL.EXECUTE(P_CURSORNRO);
DBMS_SQL.CLOSE_CURSOR(P_CURSORNRO);
FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
END LOOP;
CLOSE CURSORCONSTRAINTS;
FETCH CURSORTABELAS INTO P_NOMETABELA;
END LOOP;
CLOSE CURSORTABELAS;
END;
/
Oracle - Dicas para Criação / Manutenção de uma instância Oracle
Dicas para Criação / Manutenção de uma instância Oracle
(RM Sistemas)
O presente documento contém algumas
dicas para DBAs manterem suas instâncias Oracle em perfeito estado de
funcionamento.
As primeiras dicas referem-se a
parâmetros encontrados no arquivo init.ora, encontrado no diretório de
instalação da instância Oracle na máquina em questão.
Para alterar alguns deles (quando
possível), basta editar o arquivo em questão, salvá-lo e restartar a instância.
Quaisquer dúvidas podem ser
encaminhadas ao suporte da RM Sistemas através do mail (suporte@rm.com.br).
Tamanho do bloco Oracle
Este parâmetro deve ser configurado no momento
da criação da base de dados. Sugerimos a utilização de blocos de 8k
(DB_BLOCK_SIZE = 8192).
OBS: Note que o tamanho do bloco deve ser
informado em nº de bytes.
Caso a instância já esteja criada, pode-se
verificar a configuração deste parâmetro, editando-se o arquivo de parâmetros (init.ora) do Oracle e verificando o valor da variável DB_BLOCK_SIZE.
Uma outra alternativa para visualizar o valor
do parâmetro seria a utilização da ferramenta Instance Manager. Aqui, o
parâmetro pode ser visualizado através de:
Initialization
Parameters / Instance Specific
A alteração deste parâmetro só pode ser feita
caso a instância seja recriada.
Distribuição da memória disponível para o Oracle
Depois de determinada a quantidade de memória
que estará disponível para o Sistema Operacional e a quantidade que ficará
disponível para o Oracle, deve-se fazer a distribuição da memória disponível
para o Oracle.
o
SORT_AREA_SIZE
Deve-se configurar este parâmetro com grande
cautela, uma vez que ele reflete a quantidade de memória que cada conexão de usuário irá ter
disponível para operações de SORT.
Devemos levar em consideração também a
quantidade de memória fixa que cada conexão de usuário gasta (1,7 MB).
Sendo assim, o gasto de memória com conexões de
usuários em uma aplicação onde estejam abertas 10 sessões e o SORT_AREA_SIZE
esteja configurado com o valor de 1048576
(1MB) é:
10
* (1,7+1) = 27 MB.
OBS: Note que o valor do SORT_AREA_SIZE deve ser informado em nº de bytes.
O valor a ser configurado para este parâmetro
deve ser definido levando-se em consideração
o número de conexões simultâneas
no Oracle e a quantidade de memória disponínel para mesmo.
o
DB_BLOCK_BUFFER
Este parâmetro irá refletir a quantidade de
memória destinada ao Oracle que será utilizada para Cache de dados.
Este parâmetro deve ser configurado com o
número de blocos do Oracle que será utilizado para este Cache.
Sugerido:
60% da memória restante destinada ao Oracle destinada ao Cache de dados.
Como calcular o valor do parâmetro:
DB_BLOCK_BUFFER =
(M * 1048576) / B
Onde: M
= É a quantidade de memória ( em Mega bytes ) que deve ser destinada ao Buffer de Dados.
B= Tamanho do bloco de dados (em bytes)
utilizado pelo Oracle.
o
SHARED POOL
Este parâmetro irá refletir a quantidade de
memória destinada ao Oracle que será utilizada para o SHARED POOL.
Sugerido:
Restante da memória destinada ao Oracle (40%), destinada ao SHARED POOL.
O valor deste parâmetro deve ser determinado de
Bytes.
SHARED_POOL_SIZE = M
Onde: M=
Memória destinada ao SHARED POOL em bytes.
Tablespaces
Deve-se criar pelo menos uma TableSpace
distinta para cada finalidade: System, Dados,
Indices, Temp, Rollback, Redo.
É desejável também que os DataFiles destas
TableSpaces sejam distribuídos em discos diferentes, de forma a minimizar a
concorrência de acesso. Isto deve ser feito conforme a disponibilidade de
discos. Exemplo:
Cenário
1:
Um
disco:
System, Dados,
Iindices, Temp, Rollback , Redo no mesmo disco, mas em TableSpaces
diferentes.
Cenário
2:
Dois
discos:
1º
disco: Dados + Temp + Rollback
2º disco: System + Indices + Redo
CPU
Deve-se verificar costantemente a taxa de
utilização da CPU, a mesma não deve estar com taxa de utilização muito alta
durante a maior parte do período (90% de utilização).
Caso esteja utilizando mais de um processador
deve-se verificar se a carga da CPU está balanceada.
Para estas medições, pode se
utilizar a ferramenta Performance Monitor (no caso de servidores Windows
NT/2000).
COALESCE
Deve-se verificar periodicamente (mensalmente)
a fragmentação do espaço livre alocado
para cada TableSpace.
o
Pode-se
verificar tal fragmentação utilizando:
SELECT
TABLESPACE_NAME, TOTAL_EXTENTS , PERCENT_EXTENTS_COALESCED
FROM DBA_FREE_SPACE_COALESCED
WHERE PERCENT_EXTENTS_COALESCED <> 100
o
Para
efetuar a desfragmentação:
ALTER TABLESPACE tablespace COALESCE
Atualização de estatísticas
A rotina de atualização de estatísticas deve
ser agendada para execução diária..
Sugere-se o utilizar o Package DBMS_UTILITY
para fazer a atualização de estatísticas:
execute
DBMS_UTILITY.analyze_schema('RM','COMPUTE')
Para agendar a execução diária de estatísticas em um servidor que esteja
utilizando o Windows NT ou Windows 2000:
1º) crie um arquivo TXT com o seguinte
conteúdo:
execute DBMS_UTILITY.analyze_schema('RM','COMPUTE')
2º) Utilize o command prompt para agendar a
tarefa :
at 22:00 every:M,T,W,Th,F,S,Su cmd /c
"sqlplus rm/rm @d:\temp\script.txt"
O comando acima agenda a atualização de
estatíscas para ocorrer todos os dias às 22:00 horas. Considera-se que o
arquivo TXT criado no primeiro item é o d:\temp\script.txt
Indices
Os índices devem ser reconstruídos
periodicamente (mensalmente) , ou após periodo de grande “deleção” dos mesmos.
Para a verificação do percentual de linhas
deletadas em um determinado índice utilize:
1º) Analyze :
ANALYZE
INDEX indice VALIDATE STRUCTURE;
2º) Verificando o percetual de deleção:
SELECT LF_ROWS , DEL_LF_ROWS
FROM INDEX _STATS
OBS: Caso 30% das linhas em folhas
(LF_ROWS) tenham sido deletados (DEL_LF_ROWS), o índice deve ser reconstruído.
3º) Reconstrução do índice:
ALTER INDEX indice REBUILD tablespace.
Para reconstruir o índice, durante o
REBUILD deve haver espaço disponível
para a coexistência dos dois índices (antigo e novo).
O comando abaixo gera um script para a
reconstrução de todos os índices da base de dados:
SELECT 'ALTER INDEX ' || INDEX_NAME ||
' REBUILD TABLESPACE RM_INDICES; '
FROM USER_INDEXES
Este script deve ser executado em um momento em que não haja acesso à
base de dados.
Export / Import
O Export e Import da base de dados deve ser
feito periodicamente com finalidade de reorganizar os dados dentro da base de
dados, desfragmentar e resolver casos de linhas Migradas.
Sugere-se fazer a Exportação / Importação da
base de dados de 2 em 2 meses.
Passos para a execução do Export / Import
§ Export da base.
§ Backup físico da base
§ Drop do Schema
§ Import da base
OBS: Semanalmente sugere-se verificar o número de linhas MIGRADAS na base
de dados. Caso o número esteja muito alto, deve-se fazer o Export / Import da
base.
Para verificar o número de linhas MIGRADAS
utilize:
SELECT
TABLE_NAME, CHAIN_CNT
FROM USER_TABLES
ORDER BY CHAIN_CNT DESC
OBS: A coluna CHAIN_CNT, reflete
não apenas o número de linhas migradas, mas o processo de Export / Import irá
atacar apenas este problema.
ALERT
O ALERT dever ser verificado diariamente .
Deve-se verificar se existem mensagens de erro do tipo ORA-00600 e /ou
ORA-01578 , que indicam erros internos do Oracle ou blocos corrompidos.
Assinar:
Postagens (Atom)