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

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. 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 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. 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, bem como, Professor Titular na Fatec São Roque. 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ções e Reconhecimentos: Microsoft MVP, MCC, MSTC e MIE.
Esse post foi publicado em Dicas, Material de Apoio, MSDN, Mundo SQL Server, Scripts, Short Scripts, SQL Server, TechNet, VIRTUAL PASS BR e marcado , , , , , , . Guardar link permanente.

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 )

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