Short Script – Agosto 2013

Galera, bom dia.

Tudo em paz? Semana passando rapidamente, hoje quinta – feira.

Estou voltando com mais um relação de Short Scripts para vocês, com alguns códigos voltados para atividades em Sistemas de Gestão Empresarial os chamados ERP, como por exemplo, atualização de Saldos, Movimentação de Estoque, entre outros.

Segue abaixo a relação:

Exemplo – 1 – Script – User Role List and Login Type:

SELECT

CASE

WHEN SSPs2.name IS NULL THEN ‘Public’

ELSE SSPs2.name

END AS ‘Role Name’,

SSPs.name AS ‘Login Name’,

Case SSPs.is_disabled

When 0 Then ‘0 – Habilitado’

When 1 Then ‘1 – Desabilitado’

End AS ‘Login Status’,

SSPs.type_desc AS ‘Login Type’

FROM sys.server_principals SSPs LEFT JOIN sys.server_role_members SSRM

ON SSPs.principal_id  = SSRM.member_principal_id

LEFT JOIN sys.server_principals SSPs2

ON SSRM.role_principal_id = SSPs2.principal_id

WHERE SSPs2.name IS NOT NULL

OR SSPs.type_desc <> ‘CERTIFICATE_MAPPED_LOGIN’

AND SSPs.type_desc <> ‘SERVER_ROLE’

AND SSPs2.name IS NULL

ORDER BY SSPs2.name DESC, SSPs.name

Exemplo – 2 – Lista Databases online:

SELECT name FROM master..sysdatabases
WHERE dbid > 4 AND (512 & status) <> 512

 

Exemplo – 3 – Script – Database Login and User Role List:

 

SELECT SDPs.name as Role,

m.login,

m.[User]

FROM [Master].sys.database_principals SDPs Inner JOIN (SELECT SDPs2.name AS role,

SDPs1.name AS  [User],

p2.principal_id,

SL.name AS login

FROM [Master].sys.database_principals SDPs1 Inner JOIN [Master].sys.syslogins SL

ON SDPs1.sid = SL.sid

Inner JOIN [Master].sys.database_role_members SRM

ON SRM.member_principal_id = SDPs1.principal_id

Inner JOIN [Master].sys.database_principals SDPs2

ON SRM.role_principal_id = SDPs2.principal_id

AND SDPs2.type IN (‘R’)

WHERE SDPs1.type IN (‘S’,’U’,’G’)

)  m  ON SDPs.principal_id = m.principal_id

WHERE SDPs.type IN (‘R’)

ORDER BY role, login

Go

 

— CTE —

With Roles (Role, Login, [User])

As

(SELECT SDPs2.name AS role,

SDPs1.name AS  [User],

SL.name AS login

FROM [Master].sys.database_principals SDPs1 Inner JOIN [Master].sys.syslogins SL

ON SDPs1.sid = SL.sid

Inner JOIN [Master].sys.database_role_members SRM

ON SRM.member_principal_id = SDPs1.principal_id

Inner JOIN [Master].sys.database_principals SDPs2

On SRM.role_principal_id = SDPs2.principal_id

AND SDPs2.type IN (‘R’)

WHERE SDPs1.type IN (‘S’,’U’,’G’))

 

Select * from Roles

ORDER BY role, login

 

Exemplo – 4 – Script – User Role Permissions – Grantor, Object Permissions and Permissions Type:

SELECT SDPs1.name AS [User],

SDBPs.permission_name AS [Permissions],

ISNULL(SDBPs.class_desc,”) COLLATE latin1_general_cs_as + ISNULL(‘:’+SO.name,”) COLLATE latin1_general_cs_as + ISNULL(‘:’+SC.name,”) COLLATE latin1_general_cs_as As PermissionObjetct,

SDPs.name as Grantor,

SDBPs.state_desc AS PermissionType

FROM [Master].sys.database_permissions SDBPs INNER JOIN  [Master].sys.database_principals  SDPs

on SDBPs.grantor_principal_id=SDPs.principal_id

INNER JOIN  [Master].sys.database_principals SDPs1

on SDBPs.grantee_principal_id=SDPs1.principal_id

LEFT OUTER JOIN [Master].sys.sysobjects SO

on SDBPs.major_id=SO.id and SDBPs.class =1

LEFT OUTER JOIN [Master].sys.schemas  SC

on SDBPs.major_id=SC.[schema_id]

WHERE SDPs1.name IN (‘public’)

And SDBPs.permission_name NOT IN(‘CONNECT’)

ORDER BY User, Permissions, PermissionObjetct

 

Exemplo – 5 – Ordenação Numérica:

DECLARE @NUMREQUISICAO INT,
@LOTEINI INT

SET @NUMREQUISICAO=1
SET @LOTEINI=(SELECT MIN(LOTEPRODUCAO) FROM MOVIMENTACAO_LOTEPRODUCAO)

WHILE @LOTEINI <=(SELECT MAX(LOTEPRODUCAO) FROM MOVIMENTACAO_LOTEPRODUCAO)
Begin

IF @LOTEINI >(select max(loteproducao) from movimentacao_loteproducao)
Break
Else
Begin
BEGIN TRANSACTION

UPDATE MOVIMENTACAO_LOTEPRODUCAO
SET NUMREQUISICAO=@NUMREQUISICAO
WHERE LOTEPRODUCAO=@LOTEINI

COMMIT

SET @NUMREQUISICAO=@NUMREQUISICAO+1
End

SET @LOTEINI=(SELECT TOP 1 LOTEPRODUCAO FROM MOVIMENTACAO_LOTEPRODUCAO WHERE LOTEPRODUCAO >@LOTEINI ORDER BY LOTEPRODUCAO)
End
/****************************************************/
SELECT * FROM MOVIMENTACAO_LOTEPRODUCAO
ORDER BY NUMREQUISICAO

 

Exemplo – 6 – Stored Procedure para reorganização de Números:

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_ReorganizarNumTeste]’) and OBJECTPROPERTY(id, N’IsView’) = 1)

Drop View [dbo].[V_ReorganizarNumTeste]

 

Create View V_ReorganizarNumTeste

As

Select * From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

/********************************************************/

Alter Procedure dbo.ReorganizarNumTeste

As

Declare @NumTeste Int, @Contador Int

Set @NumTeste=1

 

Select @Contador=Count(NumTeste) From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

 

While @NumTeste <= @Contador

Begin

Set @NumTeste=@NumTeste+1

 

Begin Transaction

Update CTBaloes

Set NumTeste=@NumTeste

Where DatePart(Month,DataTeste)=Month(GetDate())

If @@ERROR <> 0

Begin

ROLLBACK  TRANSACTION

Break

RETURN

End

End

 

Commit Transaction

 

/********************************************************/

Exec reorganizarnumteste

/********************************************************/

Select * From CTBaloes

Where    DatePart(Year,DataTeste)=Year(GetDate())

ORDER BY NUMTESTE

 

Declare @NumTeste Int, @Contador Int

Set @NumTeste=1

 

Select @Contador=Count(NumTeste) From CTBaloes

Where DatePart(Month,DataTeste)=Month(GetDate())

And    DatePart(Year,DataTeste)=Year(GetDate())

PRINT @Contador

/********************************************************/

 

Create View V_Teste

As

Select * From CTBaloes CT

Where DatePart(Month,CT.DataTeste)=Month(GetDate())

And    DatePart(Year,CT.DataTeste)=Year(GetDate())

 

Select * From CTBaloes CT Inner Join V_Teste VT On CT.CodProduto = VT.CodProduto

Where DatePart(Month,CT.DataTeste)=Month(GetDate())

And    DatePart(Year,CT.DataTeste)=Year(GetDate())

And    CT.DataTeste=VT.DataTeste

And    CT.Maquina=VT.Maquina

/********************************************************/

Drop View V_Teste

/********************************************************/

 

SELECT * FROM V_REORGANIZARNUMTESTE

 

Exemplo – 7 – Retornando – Linhas – Claúsula – Percent:

Select top 80 percent * from produtos

 

Exemplo – 8 – Trigger para Atualização de Saldo:

 

CREATE  Trigger T_Atualizar_Saldos

On EstFisic

After Insert, Update

As

 

Set NoCount Off

 

Declare @TipoMov Char(1),

@Codigo Char(10),

@Quantidade Float(8),

@Data_Mov DateTime

 

Select @TipoMov=Status, @Quantidade=Quantidade, @Codigo=Codigo, @Data_Mov=Data From Inserted

 

Begin

 

If @TipoMov = ‘E’

Begin

Update Produtos

Set Saldo= Saldo+@Quantidade, Data=@Data_Mov

Where Codigo = @Codigo

End

 

If @TipoMov=’S’

Begin

Update Produtos

Set Saldo= Saldo-@Quantidade, Data=@Data_Mov

Where Codigo = @Codigo

End

End

 

Exemplo – 9 – Trigger para Controle e Movimentação de Saldo:

Create Table NovosProdutos
(Codigo Int Identity(1,1),
Descricao VarChar(20),
Saldo Int)
Go

Insert Into NovosProdutos Values(‘Produto -‘+Convert(VarChar(3),@@Identity),0)
Go 100

Create Table Movimentacao
(Codigo Int Identity(1,1),
CodProduto Int,
TipoMovimentacao Char(1),
Valor Int)
Go

Create Trigger T_MovimentacaoSaldo
On Movimentacao
After Insert, Update
As
Declare @TipoMovimentacao Char(1)

Select @TipoMovimentacao=TipoMovimentacao from Inserted

If @TipoMovimentacao = ‘E’
Begin
Update NovosProdutos
Set Saldo = Saldo + I.Valor
from NovosProdutos NP Inner Join Inserted I
On NP.Codigo = I.CodProduto
End

If @TipoMovimentacao = ‘S’
Begin
Update NovosProdutos
Set Saldo = Saldo – I.Valor
from NovosProdutos NP Inner Join Inserted I
On NP.Codigo = I.CodProduto
End

–Fazendo lançamentos de entrada
Insert Into Movimentacao Values(2,’E’,10)
Insert Into Movimentacao Values(2,’E’,15)
Insert Into Movimentacao Values(2,’E’,5)
Insert Into Movimentacao Values(2,’E’,22)
Insert Into Movimentacao Values(2,’E’,10)

–Fazendo lançamentos de saída
Insert Into Movimentacao Values(2,’S’,8)
Insert Into Movimentacao Values(2,’S’,5)
Insert Into Movimentacao Values(2,’S’,3)
Insert Into Movimentacao Values(2,’S’,2)
Insert Into Movimentacao Values(2,’S’,1)

Insert Into Movimentacao Values(8,’E’,10)
Insert Into Movimentacao Values(9,’E’,15)
Insert Into Movimentacao Values(1,’E’,5)
Insert Into Movimentacao Values(3,’E’,22)
Insert Into Movimentacao Values(22,’E’,10)

–Fazendo lançamentos de saída
Insert Into Movimentacao Values(8,’S’,8)
Insert Into Movimentacao Values(9,’S’,5)
Insert Into Movimentacao Values(1,’S’,3)
Insert Into Movimentacao Values(3,’S’,2)
Insert Into Movimentacao Values(22,’S’,1)

Select * from NovosProdutos

——————————————————————————————————————————————–

Agradeço a sua visita.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

3 comentários em “Short Script – Agosto 2013”

  1. Olá Junior, tudo bem?
    Veja se pode me ajudar, estou precisando de uma consulta que me traga, dentro de um conjunto de bases de um servidor, quando foi o ultimo acesso a base, ou seja, quando foi a ultima vez que alguém acessou, alterou, consultou alguma informação.

    Obrigado.
    Marco.

    Curtir

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s