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”; (/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”; (/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”; (/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.
Microsoft apresenta o Xbox One em Seattle, nos EUA.
A interface do Xbox One parece similar ao visual atual do Xbox 360.
Junior Galvão - MVP - Data Platform 