22 abril 2012

FOR SELECT no SQL Server

Pessoal,

Muita gente (assim como eu) usa um recurso muito útil do Fibrebird que é o FOR SELECT, onde criamos uma loop e a cada passada ele carrega as variáveis com o registro atual e dentro podemos colocar outras instruções usando essas variáveis.

Ao migrar para o SQL Server possivelmente você terá esse problema de "adaptação" se não tiver experiência na sintaxe do banco. Nesse caso temos que mudar algumas linhas no código para que o mesmo recurso funcione. Daí teremos que trabalhar com CURSORES... Veja como abaixo:


Utilizo um exemplo onde tenho uma tabela de perfil de acesso e configuração do perfil, onde tenho outra tabela com minhas configurações base para os perfis, assim que inseridos (Quando tiver tempo posto tudo aqui em um tutorial). Ao inserir o perfil base, eu tenho que lançar esse registro em todos os perfis de acesso já cadastrados (numa trigger), daí faço o seguinte (usando o FOR SELECT no Firebird)


NO FIREBIRD:



CREATE OR ALTER TRIGGER PERFILACESSO_BASE_AI0 FOR PERFILACESSO_BASE
ACTIVE AFTER INSERT POSITION 0
AS
declare variable vCodPerfilAcesso codigo_small;
declare variable vCodColaborador  codigo;


begin
  /* Insere na tabela de configuracao de acesso e perfis personalizados as novas configuracoes */
  for select idPERFILACESSO
      from perfilacesso
      into :vcodperfilacesso do
  begin
    insert into perfilacesso_config
    values (null, :vcodperfilacesso, new.tag_form,new.grupo,new.descricaofuncao, new.acessar, new.incluir,
            new.editar,new.deletar,new.localizar,new.imprimir,new.acessar_visivel,
            new.incluir_visivel,new.editar_visivel,new.deletar_visivel,new.localizar_visivel,new.imprimir_visivel);
  end
end



NO SQL SERVER


Esse exemplo agora no SQL Server, preciso "traduzí-lo" para que a coisa funcione, buscando o registro na tabela temporária INSERTED para fazer os inserts

CREATE TRIGGER trInsereConfigNovoPerfil

   ON  PERFILACESSO_BASE
   AFTER INSERT
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @IDPERFIL SMALLINT;


--INSTANCIA O CURSOR COM O CONTEUDO DA TABELA (=FOR SELECT FB)
DECLARE CADPERFIL CURSOR FOR SELECT IDPERFILACESSO  FROM PERFILACESSO (NOLOCK);
 
--ABRE O CURSOR CHAMADO CADPERFIL (Instancia do Cadastro de Perfis de Acesso)
OPEN CADPERFIL; 

--PEGA O VALOR DO PRIMEIRO REGISTRO E ATRIBUI À VARIAVEL
FETCH CADPERFIL INTO @IDPERFIL;

--FAZ LOOP NO CURSOR PARA GRAVAR AS CONFIGURAÇÕES DE ACESSO EM CADA PERFIL
WHILE (@@FETCH_STATUS=0)
BEGIN
   INSERT INTO PERFILACESSO_CONFIG
   SELECT NULL,@IDPERFIL,I.TAG_FORM,I.GRUPO,I.DESCRICAOFUNCAO, I.ACESSAR, I.INCLUIR,
I.EDITAR,I.DELETAR,I.LOCALIZAR,I.IMPRIMIR,I.ACESSAR_VISIVEL,
I.INCLUIR_VISIVEL,I.EDITAR_VISIVEL,I.DELETAR_VISIVEL,I.LOCALIZAR_VISIVEL,I.IMPRIMIR_VISIVEL
   FROM INSERTED I
   
   --PEGA PROXIMO REGISTRO
FETCH NEXT FROM CADPERFIL INTO @IDPERFIL;

END


--FECHA O CURSOR
CLOSE CADPERFIL;
DEALLOCATE CADPERFIL; --DESTROI A INSTÂNCIA

END
GO


Abraços e até a próxima!

20 abril 2012

Limpar todas as tabelas do seu banco SQL Server


Para limpar todos os registros de suas tabelas no SQL Server de uma vez só, proceda com esses 3 simples comandos abaixo.


1) Pela Query 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'



2) Criando uma Stored Procedure


CREATE PROCEDURE sp_EmplyAllTable
AS
   EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
   EXEC sp_MSForEachTable 'DELETE FROM ?'
   EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO


Mágico não?

Até a próxima!

19 abril 2012

Limpar/Deletar todos os elementos (Tables/FK´s,etc) de um Banco no SQL Server

O código abaixo limpa todas as tables, fk´s, sp´s, etc no SQL Server...

Bom proveito!!

Abra uma nova query no SQL Server Management Studio

Cole o script abaixo e execute


/* EN: Drop allnon-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])
WHILE @name is not null
BEGIN
    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Procedure: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped View: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Function: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])
END
GO
/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
WHILE @name is not null
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint IS NOT NULL
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'
        EXEC (@SQL)
        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO