Short Script – Julho – 2013

Pessoal, boa tarde.

Olha nós aqui outra vez, e se deus quiser, muitas outras vezes.

Estou de volta com mais uma relação de códigos para a minha sessão Short Scripts, nesta relação vocês poderão encontrar exemplos e códigos relacionados aos seguintes itens:

  • Alter User – Remapeando Usuário com base no Login;
  • Comando Order By com Variáveis;
  • Criando – Estatísticas de Colunas Única
  • Forçando o uso do Operador Nested Loops
  • Operador UnPivot;
  • Query dinâmica em Linked Server com variáveis;
  • Range de Datas formatado; e
  • SysJobSteps x SysJobs – Encontrando – Comando executado pelo Job.

 

Segue abaixo os códigos de exemplo:

— Exemplo – Comando – Alter User – Remapeando Usuário com base no Login —

Use teste

— Remapeando usuário com base no login existente —
alter user teste
with login=loginteste,
default_schema=schema_teste

— Adicionando o Usuário a RoleMember DBO —
sp_addrolemember ‘db_owner’,’loginteste’

 

— Comando Order By com Variáveis —

create table produtos(
Codigo varchar(10) not null,
Descricao varchar(50) not null,
Etc varchar(200)
);

insert into produtos values
(‘001’, ‘qualquer coca é boa’, ‘aaa’),
(‘002’, ‘eu quero uma coca da pepsi’, ‘bbb’),
(‘003’, ‘só que qualquer coca faz mal’, ‘ccc’),
(‘004’, ‘o que que a baiana tem?’, ‘ddd’),
(‘005’, ‘lá tem cocaína’, ‘eee’),
(‘006’, ‘Coca, cocaína e coca cola’, ‘fff’),
(‘007’, ‘aqui não tem’, ‘ggg’),
(‘008’, ‘a cocada é de côco’, ‘hhh’)

declare @valor varchar(20), @valor2 varchar(20);
set @valor2 =’coca’;
set @valor =’%’+@valor2+’%’;

select Codigo, Descricao, Etc,
charindex(@VALOR2, Descricao) as posição,
(len(Descricao)-charindex(@VALOR2,Descricao)+1) as restante,
substring(Descricao, charindex(@VALOR2, Descricao), (len(Descricao)-charindex(@VALOR2,Descricao)+1)) as trecho
from produtos
where (Descricao LIKE @VALOR) OR (Codigo LIKE @VALOR)
order by substring(Descricao, charindex(@VALOR2, Descricao), (len(Descricao)-charindex(@VALOR2,Descricao)+1))

 

— Criando – Estatísticas de Colunas Única —

EXEC sp_createstats @indexonly = ‘NO’,  @fullscan = ‘FULLSCAN’, @norecompute =’NO’

 

— Forçando o uso do Operador Nested Loops —

— Criando a Tabela Orders com base no Select da Junção de SysColumns
SELECT TOP 10000
OrderID = IDENTITY(INT,1,1),
OrderAmt = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
OrderDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
INTO dbo.Orders
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2
go

— Criando a Tabela OrderDetail —
CREATE TABLE OrderDetail
(OrderID int NOT NULL,
OrderDetailID int NOT NULL,
PartAmt money NULL,
PartID int NULL)

— Inserindo os Dados em OrderDetail —
Insert Into OrderDetail (OrderID, OrderDetailID, PartAmt, PartID)
Select OrderID,
OrderDetailID = 1,
PartAmt = OrderAmt / 2,
PartID = ABS(CHECKSUM(NEWID()))%1000+1
FROM Orders

— Consultando os dados —
Select O.OrderId,
OD.OrderDetailID,
O.OrderAmt,
OD.PartAmt,
OD.PartID,
O.OrderDate
From Orders O Inner Join OrderDetail OD
On O.OrderID = OD.OrderID

— Forçando o Uso do Nested Loop com Opção Loop Join —
Select O.OrderId,
OD.OrderDetailID,
O.OrderAmt,
OD.PartAmt,
OD.PartID,
O.OrderDate
From Orders O Inner Join OrderDetail OD
On O.OrderID = OD.OrderID
— This is a hash match for this example
Option (loop join) –force a loop join

— Adicionando Chaves Primárias —
ALTER TABLE dbo.Orders
ADD PRIMARY KEY CLUSTERED (OrderID)
Go

ALTER TABLE dbo.OrderDetail
ADD PRIMARY KEY CLUSTERED (OrderID,OrderDetailID)
Go

— Consultando os dados, após a Criação das Chaves Primárias —
Select O.OrderId,
OD.OrderDetailID,
O.OrderAmt,
OD.PartAmt,
OD.PartID,
O.OrderDate
From Orders O Inner Join OrderDetail OD
On O.OrderID = OD.OrderID

— Fazendo uso do Operador Nested Loop —
Select O.OrderId,
OD.OrderDetailID,
O.OrderAmt,
OD.PartAmt,
OD.PartID,
O.OrderDate
From Orders O Inner Join OrderDetail OD
On O.OrderID = OD.OrderID
Where O.OrderID < 10

 

— Operador UnPivot —

— Exemplo 1 —
CREATE TABLE dbo.CustomerPhones
(
CustomerID INT PRIMARY KEY, — FK
Phone1 VARCHAR(32),
Phone2 VARCHAR(32),
Phone3 VARCHAR(32)
);

INSERT dbo.CustomerPhones (CustomerID, Phone1, Phone2, Phone3)
VALUES (1,’705-491-1111′, ‘705-491-1110′, NULL),
(2,’613-492-2222′, NULL, NULL),
(3,’416-493-3333’, ‘416-493-3330’, ‘416-493-3339′);

— Forçando UnPivot sem utilizar o Operador UnPivot —
SELECT CustomerID, Phone = Phone1
FROM dbo.CustomerPhones WHERE Phone1 IS NOT NULL
UNION ALL
SELECT CustomerID, Phone = Phone2
FROM dbo.CustomerPhones WHERE Phone2 IS NOT NULL
UNION ALL
SELECT CustomerID, Phone = Phone3
FROM dbo.CustomerPhones WHERE Phone3 IS NOT NULL;

— Utilizando o Operador UnPivot —
SELECT CustomerID, Phone
FROM
(
SELECT CustomerID, Phone1, Phone2, Phone3
FROM dbo.CustomerPhones
) AS cp
UNPIVOT
(
Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS up;
— Exemplo 2 —
CREATE TABLE dbo.CustomerPhones2
(
CustomerID INT PRIMARY KEY, — FK
Phone1 VARCHAR(32),
PhoneType1 CHAR(4),
Phone2 VARCHAR(32),
PhoneType2 CHAR(4),
Phone3 VARCHAR(32),
PhoneType3 CHAR(4)
);

INSERT dbo.CustomerPhones2 VALUES
(1,’705-491-1111’, ‘cell’, ‘705-491-1110’, ‘home’, NULL,NULL),
(2,’613-492-2222′, ‘home’, NULL, NULL, NULL, NULL),
(3,’416-493-3333′, ‘work’, ‘416-493-3330’, ‘cell’,
‘416-493-3339’, ‘home’);

— Utilizando o Operador UnPivot —
SELECT CustomerID, Phone, PhoneType
FROM
(
SELECT CustomerID, Phone, PhoneType,
idp = SUBSTRING(Phones, LEN(Phones) – PATINDEX(‘%[^0-9]%’, REVERSE(Phones)) + 2, 32),
idpt = SUBSTRING(PhoneTypes, LEN(PhoneTypes) – PATINDEX(‘%[^0-9]%’, REVERSE(PhoneTypes)) + 2, 32)
FROM
(
SELECT CustomerID, Phone1, Phone2, Phone3,
PhoneType1, PhoneType2, PhoneType3
FROM dbo.CustomerPhones2
) AS cp
UNPIVOT
(
Phone FOR Phones IN (Phone1, Phone2, Phone3)
) AS p
UNPIVOT
(
PhoneType FOR PhoneTypes IN (PhoneType1, PhoneType2, PhoneType3)
) AS pt
) AS x
WHERE idp = idpt;

 

— Query dinâmica em Linked Server com variáveis —

Declare @NomePlanilha Varchar(20),

@Comando Varchar(500)

Set @NomePlanilha = ‘ABA – ‘+Convert(VarChar(10),GetDate(),103)

Set @Comando = ‘SELECT * INTO PLANILHA
FROM OPENROWSET(”Microsoft.ACE.OLEDB.12.0”,
”Excel 12.0;Database=C:\caminho\planilha.xlsx”, [‘+””+@NomePlanilha+’$’+”’])’

Exec(@Comando)

SELECT * INTO PLANILHA
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;Database=C:\caminho\planilha.xlsx’, [‘ABA – 08/02/2013$’])

 

— Range de Datas formatado —

CREATE FUNCTION RANGE_DATAS(@DATA_INICIAL DATETIME,@DATA_FINAL DATETIME)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @DATA_TEMP AS DATETIME
DECLARE @COUNT AS INT
DECLARE @LIMITE AS INT
DECLARE @RETORNO AS VARCHAR(MAX)
DECLARE @MES AS VARCHAR(2)
DECLARE @ANO AS CHAR(4)

————————————————
— PARA TESTE
————————————————
— DECLARE @DATA_INICIAL AS DATETIME
— DECLARE @DATA_FINAL AS DATETIME
— SET @DATA_INICIAL = ‘2012-01-01’
— SET @DATA_FINAL = ‘2014-01-01’
————————————————
SET @LIMITE = DATEDIFF(MM,@DATA_INICIAL,@DATA_FINAL) + 1
SET @COUNT = 1
SET @RETORNO = ”

WHILE (@COUNT <= @LIMITE)
BEGIN
SET @DATA_TEMP = DATEADD(MONTH, @COUNT-1, @DATA_INICIAL)
SET @ANO = DATEPART(YEAR, @DATA_TEMP)
SET @MES = DATEPART(MONTH, @DATA_TEMP)
SET @RETORNO = @RETORNO + ‘[‘ + @MES + ‘/’ + @ANO +’]’
IF (@COUNT <> @LIMITE ) SET @RETORNO = @RETORNO + ‘,’
SET @COUNT = @COUNT + 1
END

RETURN @RETORNO

END
Select dbo.range_datas (‘2012-01-01’, ‘2020-12-01’)

 

— SysJobSteps x SysJobs – Encontrando – Comando executado pelo Job —

select j.name, s.step_name, s.command from sysjobsteps s inner join sysjobs j
on s.job_id = s.job_id
where command like ‘%Backup-Master%’

 

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

Agradeço mais uma vez a sua visita.

Nos encontramos em breve.

 

 

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.

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