Hello World, muito, mas muito bom dia….
Mais um sábado começando, justamente hoje que excepcionalmente eu não vou trabalhar e deveria estar dormindo estou aqui aproveitando um pouco do meu tempo para me dedicar a outra enorme paixão, meu blog e meu seguidores que estão me ajudando desde o começo deste ano a aumentar de forma considerável a audiência dos meus posts e sessões aqui compartilhadas.
Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, estou compartilhando a minha feita a exatos três meses no final do último post da sessão Short Scripts, hoje publicando mais um conjunto de scripts adicionados a minha biblioteca particular de códigos e exemplos nos últimos meses.
O post de hoje
Como de costume selecionei os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:
- Armazenamento de arquivos;
- Construção de Frases;
- Conversão de caracteres;
- Data Type Money;
- Excel;
- Grant All Permissions;
- Operador Cube;
- Operador Rollup;
- Select Recursivo;
- Sequência Numérica;
- Views;
- XP_DirTree;
- XML Path.
Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Dezembro 2017. Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.
Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.
Short Scripts
— Short Script 1 – Armazenando arquivos do Excel diretamente no SQL Server —
Set Nocount On
Go
Create Table FileList
(id int identity(1,1) primary key clustered,
FileName varchar(max))
Go
Create Table #TempTable
(id int identity(1,1) primary key clustered,
FileName varchar(max),
FileDepth int,
FileID int)
Go
Create Table dbo.TestBlob
(tbId int IDENTITY(1,1) NOT NULL,
tbName varchar (50) NULL,
tbDesc varchar (100) NULL,
tbBin varbinary (max) NULL)
Go
Insert Into #TempTable
EXEC master.sys.xp_dirtree ‘E:\ExcelOutput’,0,1;
Go
Select * from #TempTable
Go
Declare @I int=0, @FileName varchar(max), @Count int
Select * into #TempFileList from FileList
Set @Count=(Select count(*) from #TempFileList)
Declare @SQLText nvarchar(max)
While (@i<@Count)
Begin
Set @FileName=(select top 1 FileName from #TempFileList)
Set @SQLText=’Insert TestBlob(tbName, tbDesc, tbBin) Select ”’+@FileName+”’,”Files”,
BulkColumn from Openrowset( Bulk ”’+@FileName+”’, Single_Blob) as tb’
Print @SQLText
Delete from #TempFileList where FileName=@FileName
Set @I=@I+1
End
Select tbID as ID,
tbName as ‘File Name’,
tbBin as ‘Converted file’
from TestBlob
Go
Drop Table #TempFileList
Go
— Short Script 2 – Convertendo caracteres diretamente para o formato e data type Money —
SELECT ‘1,,,,,,’+$0
Go
— Short Script 3 – Criando uma frase através de um Select Recursivo —
Create Table myWords
(RowID Int,
Word Varchar(20))
Go
Insert Into myWords Values(1, ‘This’),(2, ‘is’),(3, ‘an’),(4, ‘interesting’),
(5,’table’)
Declare @Sentence as varchar(8000)
SET @Sentence = ”
SELECT @Sentence = @Sentence + word + ‘ ‘
FROM myWords
ORDER BY RowID
PRINT @Sentence
Go
— Short Script 4 – Utilizando XML Path para gerar uma sequência numérica —
Declare @MyTable Table
(MyID Int)
Insert Into @MyTable Values(1),(2),(3),(4),(5)
Go
— Execução 1 —
Select MyID as “text()”, ‘;’ as “text()” from @MyTable for xml path(”)
Go
— Execução 2 —
Select MyID + ‘;’ as “text()” from @MyTable for xml path(”)
Go
— Short Script 5 – Atribuindo Grant All para todas tabelas em um Banco de Dados —
USE MyDatabase
Go
If EXISTS (Select [name] FROM master..sysdatabases WHERE [name] = ‘MyDatabase’)
Begin
Print ‘Updating Permissions for MyDatabase’
Print ‘ ‘
Declare @tablename varchar(255), @tablename_header varchar(255)
Declare tnames_cursor CURSOR FOR
Select name FROM MyDatabase..sysobjects
WHERE type = ‘U’
Open tnames_cursor
Fetch Next From tnames_cursor INTO @tablename
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
Begin
Select @tablename_header = ‘Updating ‘ + ‘MyDatabase..’ + RTrim(UPPER(@tablename) )
Print @tablename_header
Exec (‘Grant All on ‘ + @tablename +’ to shanewiso’)
End
Fetch Next From tnames_cursor INTO @tablename
End
Deallocate tnames_cursor
End
Go
— Short Script 6 – Diferença entre os operadores Rollup e Cube —
Create Table Funcionarios
(Codigo Int Primary Key,
Nome VarChar(50) Not Null,
Sexo VarChar(10) Not Null,
Salario Int Not Null,
Departamento VarChar(50) Not Null)
Go
Insert Into Funcionarios
Values
(1, ‘David’, ‘Masculino’, 5000, ‘Sales’),
(2, ‘Jim’, ‘Feminino’, 6000, ‘HR’),
(3, ‘Kate’, ‘Feminino’, 7500, ‘IT’),
(4, ‘Will’, ‘Masculino’, 6500, ‘Marketing’),
(5, ‘Shane’, ‘Feminino’, 5500, ‘Finance’),
(6, ‘Shed’, ‘Masculino’, 8000, ‘Sales’),
(7, ‘Vik’, ‘Masculino’, 7200, ‘HR’),
(8, ‘Vince’, ‘Feminino’, 6600, ‘IT’),
(9, ‘Jane’, ‘Feminino’, 5400, ‘Marketing’),
(10, ‘Laura’, ‘Feminino’, 6300, ‘Finance’),
(11, ‘Mac’, ‘Masculino’, 5700, ‘Sales’),
(12, ‘Pat’, ‘Masculino’, 7000, ‘HR’),
(13, ‘Julie’, ‘Feminino’, 7100, ‘IT’),
(14, ‘Elice’, ‘Feminino’, 6800,’Marketing’),
(15, ‘Wayne’, ‘Masculino’, 5000, ‘Finance’)
Go
— Agrupamento simples através de Group By através da coluna Departamento —
Select Departamento,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Departamento
Go
— Utilizando operador Rollup para gerar totais e subtotais com base na coluna Departamento —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Rollup (Departamento)
Go
— Procurando subtotais através do operador Rollup para colunas Departamento e Sexo —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Coalesce (Sexo,’Sexos’) As Sexo,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Rollup (Departamento, Sexo)
Go
— Utilizando o Operador Cube para gerar todas as possíveis combinações de agrupamentos de dados através das colunas Departamento e Sexo —
Select Coalesce (Departamento, ‘Departamentos’) As Departamento,
Coalesce (Sexo,’Sexos’) As Sexo,
Sum(Salario) As Salario_Sum
From Funcionarios
Group By Cube (Departamento, Sexo)
Go
— Short Script 7 – Identificando a relação de colunas utilizadas em uma view —
— Exemplo 1: Utilizando Information_Schema —
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns
WHERE UsedColumns.VIEW_NAME=’NameofView’
Go
— Exemplo 2: Utilizando DMVs —
SELECT
v.name AS ViewName,
c.name AS ColumnName,
columnTypes.name AS DataType,
aliases.name AS Alias
FROM sys.views v INNER JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
INNER JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
INNER JOIN sys.columns c
ON c.object_id = d.referenced_major_id
INNER JOIN sys.types AS columnTypes
ON c.user_type_id=columnTypes.user_type_id
AND c.column_id = d.referenced_minor_id
INNER JOIN sys.columns AS aliases
On c.column_id=aliases.column_id
AND aliases.object_id = object_id(‘[SchemaName].[ViewName]’)
WHERE v.name = ‘ViewName’;
Go
Show, missão cumprida! Mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância, apresentam um valor e conhecimento do mais alto nível.
Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.
Acredito que você tenha observado que estes códigos são conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.
Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.
Links
Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:
- https://pedrogalvaojunior.wordpress.com/2017/09/16/short-scripts-setembro-2017/
- https://pedrogalvaojunior.wordpress.com/2017/06/08/short-scripts-junho-2017/
- https://pedrogalvaojunior.wordpress.com/2017/03/31/short-scripts-marco-2017/
- https://pedrogalvaojunior.wordpress.com/2016/12/15/short-scripts-dezembro-2016/
- https://pedrogalvaojunior.wordpress.com/2016/10/31/short-scripts-outubro-2016/
- https://pedrogalvaojunior.wordpress.com/2016/06/20/short-scripts-junho-2016/
Agradecimento
Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença ao meu blog, desejo e espero que você possa ter encontrado algo que lhe ajudou.
Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no post do mês de fevereiro de 2018.
Um forte abraço, feliz natal e próspero ano novo.
Junior Galvão - MVP - Data Platform 