Short Scripts – Maio – 2013

Galera, boa tarde.

Mais um dia de muito chuva e muito trabalho.

Aproveitando o meu tempo livre, estou retorno com mais um post da minha série mensal de Short Scripts. Como você já sabe, o objetivo desta série é compartilhar, pequenos códigos com pouco complexidade, mas que podem ser de extrema importância e utilidade.

Segue abaixo a relação de Short Scripts:

1 – Exemplo – Bulk Insert – Mapeando Path

Set NoCount On

If Object_Id (‘tempdb..#Documento_Teste’) Is Not Null
Drop Table #Documento_Teste
Go

Create Table #Documento_Teste
( id_Doc Int Identity (1,1) ,
nm_Path Varchar (50) );

—=============================================================================
— LE O DIRETORIO E INSERI TODOS OS PATHS LIDOS NA TABELA TEMPORARIO
—=============================================================================

Insert Into #Documento_Teste (nm_Path)
EXEC master..xp_cmdshell ‘dir c:\teste /b’ ;—- PODE USAR AQUI UMA VARIAVEL COMO PARAMETRO DE ENTRADA DE UMA PROCEDURE

—=============================================
— APAGA REGISTROS NULOS SE TIVER
—=============================================

Delete From #Documento_Teste Where nm_Path Is Null ;

—=============================================
— APAGA REGISTROS NULOS SE TIVER
—=============================================

Declare @nm_Arquivo Varchar(50)
Declare @Path Varchar(80)
Declare @SQL Varchar(1000)

Set @Path = ‘c:\teste\’ — PODE USAR AQUI UMA VARIAVEL COMO PARAMETRO DE ENTRADA DE UMA PROCEDURE
Declare cCursor_Image Cursor For

Select Distinct nm_Path From #Documento_Teste ;

Open cCursor_Image
Fetch Next From cCursor_Image Into @nm_Arquivo

While @@Fetch_Status = 0
Begin

Select @SQL = ‘INSERT INTO Documento_Teste (nm_Path,im_Doc) ‘ + char(13)
Select @SQL = @SQL + ‘SELECT nm_Doc = ”’ + @Path + @nm_Arquivo + ”’, * ‘+ char(13)
Select @SQL = @SQL + ‘FROM OPENROWSET(BULK ”’+ @Path + @nm_Arquivo + ”’ , SINGLE_BLOB) AS Document’

Execute ( @SQL )

Fetch Next From cCursor_Image Into @nm_Arquivo
End

Close cCursor_Image
Deallocate cCursor_Image

Set NoCount Off
Go

 

2 – Exemplo – Cursor – Localizando – Índice +Nome da Tabela.

Use TempDB
Go

DECLARE @DatabaseName varchar(30), @cmd varchar(1000), @IndexName Varchar(100)

Set @IndexName=’idx_balance_daf’

DECLARE cur_FindIndexName CURSOR FOR

SELECT name FROM MASTER..SYSDATABASES
WHERE name NOT IN (‘master’, ‘msdb’, ‘model’, ‘tempdb’)
and status <> 66048
Order By Name

OPEN cur_FindIndexName
FETCH NEXT FROM cur_FindIndexName
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = ‘SET NOCOUNT ON’ + char(10) +
‘USE ‘ + @DatabaseName + ” + char(10) +

‘If Exists(Select Name from sys.sysindexes Where Name =’+””+@IndexName+””+’)
begin

select’+””+@DatabaseName+”’As DatabaseName’+’, si.Name As IndexName, st.Name As TableName
from sys.sysindexes si Inner Join sys.tables st
on si.id = st.object_id
where si.name = ‘+””+@IndexName+”” + char(10)+’

End’

Exec(@cmd)

FETCH NEXT FROM cur_FindIndexName
INTO @DatabaseName
END

Close cur_FindIndexName
Deallocate cur_FindIndexName

 

3 – Exemplo – Descobrindo objetos através da Sys.Allocation_Units

SELECT au.allocation_unit_id, OBJECT_NAME(p.object_id) AS table_name,
fg.name AS filegroup_name,
au.type_desc AS allocation_type,
au.data_pages,
partition_number
FROM sys.allocation_units au Inner JOIN sys.partitions p

ON au.container_id = p.partition_id

Inner JOIN sys.filegroups fg

ON fg.data_space_id = au.data_space_id

WHERE au.allocation_unit_id = allocation_unit_id
ORDER BY au.allocation_unit_id;

 

4 – Exemplo – Função Host_Name() – Retornando o HostName da Máquina

Create Table Auditoria2
(Codigo Int Identity(1,1),
Host VarChar(50) Default Host_Name())

Insert Into Auditoria2 Default Values

Select * from Auditoria2

 

5 – Exemplo = Matando todos os processos – Sleeping

Declare @SPID SmallInt,
@Contador SmallInt,
@Comando VarChar(20)

Declare @Id Table
(CodId SmallInt)

Insert Into @ID
Select SPID from SysProcesses
Where Status=’sleeping’

Select Top 1 @SPID = CodID from @ID
Where CodID > 0

While @Contador >=(Select Count(*) from @ID)
Begin

Set @Comando=’Kill ‘+Convert(VarChar(2),@SPID)

Exec(@comando)

Set @Contador = @Contador – 1

Select Top 1 @SPID = CodID from @ID
Where CodID >@SPID
End

 

6 – Exemplo – Query – Monitoramento de Consumo com ocorrência de FullScans

SELECT total_worker_time/execution_count as AvgCPU,
total_elapsed_time/execution_count as AvgDuration,
(total_logical_reads+total_physical_reads)/execution_count as AvgReads,
execution_count,
substring(st.text, (qs.statement_start_offset/2)+1 , ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end – qs.statement_start_offset)/2) + 1) as txt,
qp.query_plan.value(‘declare default element namespace “http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8221;; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]’ , ‘decimal(18,4)’) * execution_count AS TotalImpact
qp.query_plan.value(‘declare default element namespace “http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8221;; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]’ , ‘varchar(100)’) AS [Database]
qp.query_plan.value(‘declare default element namespace “http://schemas.microsoft.com/sqlserver/2004/07/showplan&#8221;; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]’ , ‘varchar(100)’) AS [Table]
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
where cast(query_plan as varchar(max)) like ‘%missing%’
order by TotalImpact desc

 

7 – Exemplo – Retornando o Tamanho de Tabelas com consideração de grande tamanho

declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)

create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

— Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = ‘U’

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id

select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) – data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
– data
where objid = @id

/* unused: sum(reserved) – sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
– (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id

update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id

fetch next from c_tables
into @id
end
select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ‘ ‘ + ‘KB’)

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = ‘E’
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

 

8 – Exemplo – Utilizando opção Mirror – Espelhando arquivos de Backup

Backup Database Master
To Disk = ‘D:\Master.bak’
Mirror To Disk = ‘D:\Master-Mirror.bak’
With Init, Format

 

Pois bem, aqui estão mais 8 Short Scripts, espero que este material possa ter ajudado.

Nos encontramos brevemente.

Até mais.

Anúncios
Esse post foi publicado em Dicas, Material de Apoio, MSDN, Mundo SQL Server, Scripts, Short Scripts, SQL Server, TechNet, VIRTUAL PASS BR e marcado , , , , , , por Junior Galvão - MVP. Guardar link permanente.

Sobre 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. 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 1994 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, entre outros recursos. 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. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, etc. Trabalhei entre 2011 e 2017 como Administrador de Banco de Dados e Coordenador de TI no FIT – Instituto de Tecnologia da Flextronics, atualmente exerço a função de Professor Universitário na FATEC São Roque. CTO da Galvão Tecnologia, consultoria especializada em Gestão de TI, Administração de Servidores Windows Server, Bancos de Dados Microsoft SQL Server e Virtualização. Possuo titulação Oficial Microsoft MVP e reconhecimentos: MCC, MSTC e MIE.

Deixe um comentário

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 )

Foto do Google+

Você está comentando utilizando sua conta Google+. 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 )

w

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.