Data por extenso alterando o language

Esse é um jeito bem facil e rapido para ter uma data por extenso somente trocanto o Language do banco:

*** No exemplo eu uso por padrão o us_english no banco de dados. Verifique antes qual o default language do seu banco para alterar a última linha do código:


Declare @Data DateTime

set language brazilian

Declare @Retorno VarChar(200)

If @Data is Null
Begin
Set @Data = getdate()
End

Select DATENAME(weekday, @Data) + ', ' + convert(varchar(02),DatePart(day,@Data)) + ' De ' + DateName(Month,@Data) + ' de ' + convert(varchar(04),DatePart(YEar,@Data)) + '.'

SET LANGUAGE us_english

Importar e Exportar imagem do banco.

Hoje há muita controversia a respeito de colocar imagens direto no banco de dados ou colocar apenas o caminho para uma pasta fisica.

Independente da discussão, aqui vai um exemplo de como colocar uma imagem no banco e depois recuperá-la.

Primeiro passo: Criar a tabela.

If Exists(Select 0 from Sys.Objects Where name = 'tbCachimbos')
Begin
Drop Table dbo.tbCachimbos
End
GO
Create Table dbo.tbCachimbos(
idCachimbo int Identity(1,1) NOT NULL,
dsCachimbo VarChar(1000) Not Null,
dsimage Image NULL,
Constraint PK_Cachimbos Primary Key Clustered (idCachimbo ASC))


* Notem o campo dsimage do tipo Image.


Segundo passo: Fazer o insert de um registro e depois colocar a imagem.

Insert Into dbo.tbCachimbos (dsCachimbo) Values ('Brebbia First Rocciata Double Silver Band')

Update dbo.tbCachimbos
Set dsimage = (Select * From OpenRowset (Bulk 'd:\Projetos\Brebbia.jpg', Single_Blob) as a )
Where idCachimbo = @@Identity


* Faça um select da tabela tbCachimbos e veja como está o campo dsimage.


Terceiro passo: Recuperar a imagem.

Para recuperar a imagem, é preciso utilizar o BCP para gravar um arquivo. O problema de gravar um arquivo imagem a partir do campo de uma tabela é conseguir recuperar a tipagem correta da coluna e gravar em um arquivo sem nenhuma alteração de conteudo.

Entre alguns exemplos que consegui, acabei optando por usar um arquivo FMT como padrão de saida.

O arquivo FMT fica assim:

9.0
1
1 SQLIMAGE 0 0 "" 1 dsimage ""


E para recuperar a imagem:

Declare @SQLcommand NVarChar(4000)

Set @SQLcommand = 'bcp "SELECT dsimage FROM BancoTeste.dbo.tbCachimbos" queryout "d:\Projetos\Brebbia_Recuperado.jpg" -T -fd:\Projetos\Imagens.fmt'

Exec xp_cmdshell @SQLcommand, no_output

Diminuir o tamanho do banco TempDB

As vezes algum processo pesado ou longo pode influenciar muito no tamanho do arquivo de dados (mdf) do TempDB. A solução para diminuir novamente o arquivo é simples.

Método 1:
Diminui para um tamanho especifico:
USE TEMPDB
GO
DBCC SHRINKFILE (tempdev, 50)
--* O comando acima diminui o tamanho do arquivo de dados do tempdb para 50MB.

DBCC SHRINKFILE (templog, 5)
--* O comando acima diminui o tamanho do arquivo de log do tempdb para 5MB.


Método 2:
Diminuir para um percentual do tamanho atual
DBCC SHRINKDATABASE (tempdb, 10)
--* O comando acima diminui o tamanho do banco tempdb para 10% do atual.


Os dois métodos acima podem não funcionar caso o banco de dados TempDB ainda possua dados, nesse caso o único método que funcionará é o 3o.

Método 3:

Esse método é o mais simples:

Reinicie o serviço do SQLServer:
Clique com o botão direito do mouse em "SQL Server" no SQL Server Configuration Manager e clique em Restart ou Reiniciar.






Para saber o resultado em qualquer dos métodos acima

SP_HELPDB TEMPDB

Como iniciar o SQLServer em modo single-user

Clique com o botão direito do mouse em "SQL Server" no SQL Server Configuration Manager e clique em Propriedades.
(Essas alterações podem tambem ser feitas pelo Services do Windows no serviço "SQL Server (MSSQLSERVER)")

Clique em Parâmetros de inicialização para visualizar as seguintes entradas:

A opção "-c" reduz o tempo de inicialização ao iniciar SQL Server no prompt de comando. Normalmente, o Mecanismo de Banco de Dados do SQL Server inicia como um serviço chamando o Gerenciador de Controle de Serviços. Porque o Mecanismo de Banco de Dados do SQL Server não inicia como um serviço quando inicia do prompt de comando.
A opção "-m" indica que quando você inicia uma instância de SQL Server em modo de usuário único, só um único usuário pode conectar.
A opção "-T3608" indica que uma instância de SQL Server deveria ser iniciada com um sinalizador de rastreamento especificado (trace#) em efeito. São usados sinalizadores de rastreamento para iniciar o servidor com comportamento sem padrão. O sinalizador de rastreamento 3608 evita que o SQL Server recupere qualquer banco de dados, exceto o banco de dados mestre.

O parametro de inicialização fica por exemplo:
-dD:\MSSQL\DATA\master.mdf;-eD:\MSSQL\LOG\ERRORLOG;-lC:\MSSQL\LOGS\mastlog.ldf; -m -c -T3608

Após alterado, pare o serviço e inicie novamente.

Para voltar ao modo normal, retire as opções -m -c -T3608 e reinicie o serviço.

Mover os arquivos do banco de dados Msdb

Para mover o banco de dados MSDB, é necessário iniciar o SQL Server com a opção -c, a opção -m e o sinalizador de rastreamento 3608.

O sinalizador de rastreamento 3608 evita que o SQL Server recupere na inicialização qualquer banco de dados, exceto o banco de dados Master.

Após adicionar a opção -c, a opção -m e o sinalizador de rastreamento 3608, execute as seguintes etapas:


. Interrompa e reinicie o SQL Server.
. Certifique-se de que o serviço SQL Server Agent não está sendo executado no momento.
. Desanexe o banco de dados msdb como segue:
use master
go
sp_detach_db 'msdb'
go

. Mova os arquivos Msdbdata.mdf e Msdblog.ldf do local atual (D:\Mssql8\Data) para o novo local (E:\Mssql8\Data).
. Remova -c -m -T3608 da caixa de parâmetros de inicialização no Enterprise Manager.
. Interrompa e reinicie o SQL Server.

Observação: Se tentar anexar novamente o banco de dados msdb iniciando o SQL Server junto com a opção -c, a opção -m e o sinalizador de rastreamento 3608, talvez você a seguinte mensagem de erro seja exibida:
Servidor: Msg 615, Nível 21, Estado 1, Linha 1
Não foi possível encontrar a tabela de banco de dados ID 3, nome 'Model'.

.Anexe novamente o banco de dados msdb conforme a seguir:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go

Observação: Se usar este procedimento e mover o banco de dados Model, estará tentando desanexar o banco de dados msdb enquanto desanexa o banco de dados Model.
Ao fazer isto, será necessário anexar novamente o banco de dados Model primeiro e anexar novamente o banco de dados msdb.
Se anexar novamente o banco de dados msdb primeiro, a seguinte mensagem de erro será exibida ao tentar anexar novamente o banco de dados Model:
Msg 0, Nível 11, Estado 0, Linha 0
Erro grave no comando atual. Os resultados, se houver, devem ser descartados.

Neste caso, é necessário desanexar o banco de dados msdb, anexar novamente o banco de dados Model e anexar novamente o banco de dados msdb.

Após mover o banco de dados msdb, talvez a seguinte mensagem de erro seja exibida:
Erro 229: Permissão EXECUTE negada no objeto'ObjectName', banco de dados 'mestre', proprietário 'dbo'.

Esse problema ocorre porque a corrente de propriedade foi quebrada.
Os proprietários do banco de dados msdb e do banco de dados mestre não são os mesmos.
Neste caso, o proprietário do banco de dados msdb foi alterado. Como solução alternativa, execute as seguintes declarações Transact-SQL.
É possível fazer isso usando a linha de comando Osql.exe ou a linha de comando Sqlcmd.exe:

USE MSDB
Go
EXEC sp_changedbowner 'sa'
Go

Mover os arquivos do banco de dados Master

Caso você preciso trocar o banco de dados Master de lugar, os passos são esses:

Observação: Aqui também é possível alterar o local do log de erro.

. Clique com o botão direito do mouse em "SQL Server" no SQL Server Configuration Manager e clique em Propriedades.
(Essas alterações podem tambem ser feitas pelo Services do Windows no serviço "SQL Server (MSSQLSERVER)")

. Clique em Parâmetros de inicialização para visualizar as seguintes entradas:
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf -eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG -lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

-d é o caminho totalmente qualificado para o arquivo de dados do banco de dados mestre.

-e é o caminho totalmente qualificado para o arquivo de log de erro.

-l é o caminho totalmente qualificado para o arquivo de log do banco de dados mestre.


. Altere os caminhos dos arquivos para o lugar desejado. No exemplo abaixo os arquivos serão passados para a Pasta D:\BD\SQLDATA e o Log para D:\BD\Log
-dD:\BD\SQLDATA\master.mdf -eD:\BD\Log -lD:\BD\SQLDATA\mastlog.ldf

. Pare o serviço "SQL Server".
. Copie os arquivos Master.mdf e Mastlog.ldf para o novo local (D:\BD\SQLDATA\).
. Inicie o serviço "SQL Server".

Primeiras letras em maiúsculas sem Loop. (Camel Case)

Esse Select (CTE) usa a recursividade para trocar todas as primeiras letras das palavras para maiusculas:


Declare @Texto VarChar(8000)

Set @Texto = 'CamelCase é a denominação em inglês para a prática de escrever palavras compostas ou frases, onde cada palavra é iniciada com Maiúsculas e unidas sem espaços. É um padrão largamente utilizado em diversas linguagens de programação, como Java, Ruby e Python, principalmente nas definições de Classes e Objetos. Pela sua associação com tecnologia, o marketing se apropriou dessa maneira de escrever, injetando certo ar de "tecnologia" nos produtos assim nomeados: iPod, GameCube, OpenOffice.org, StarCraft, dentre outros.'

;With tbCamelCase as (
Select upper( substring( @Texto, 1, 1 )) AS cc,
1 as Seq
Union All
select Case When substring(@Texto, n.Seq, 1) = ' '
Then upper(substring( @Texto, n.Seq + 1, 1 ))
Else substring( @Texto, n.Seq + 1, 1)
End AS cc,
n.Seq + 1 as Seq
From tbCamelCase n
Where n.Seq + 1 <= Len(@Texto))
Select Replace((Select cc AS [text()] from tbCamelCase for xml path( '' )), ' ', ' ')
option(maxrecursion 30000)

Números randomicos dentro de um período.

Seguindo a idéia dos últimos posts, essa rotina gera um número randomico dentro de um período estabelecido.

DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 800 --Limite inferior do periodo
SET @Upper = 999 --Limite superior do periodo
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND(CAST(NEWID() AS varbinary )) + @Lower), 0)
SELECT @Random


Um exemplo dessa aplicação é gerar números aleatórios de telefones:

DECLARE @Random VarChar(9)
DECLARE @Upper Decimal(9,0)
DECLARE @Lower Decimal(9,0)

SET @Lower = 30000000 --Limite inferior do periodo
SET @Upper = 99999999 --Limite superior do periodo
Select @Random = Stuff(Convert(VarChar(9), Convert(Decimal(9,0), ROUND(((99999999 - @Lower -1) * RAND(CAST(NEWID() AS varbinary)) + @Lower), 0))), 5, 0, '-')
SELECT @Random

Gerar CNPJ Válido

Por razões obvias de segurança de informação nós temos que trocar alguns dados (scramble) de informações confidenciais quando atualizamos as bases de desenvolvimento e homologação como dito nos post anterior. Outra informação necessãria é o CNPJ, essa rotina cria CNPJs validos e randomicos:



Declare @n Int, @n1 Int, @n2 Int, @n3 Int, @n4 Int, @n5 Int, @n6 Int, @n7 Int, @n8 Int, @n9 Int, @n10 Int, @n11 Int, @n12 Int
Declare @d1 Int, @d2 Int

Set @n = 9
Set @n1 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n2 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n3 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n4 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n5 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n6 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n7 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n8 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n9 = 0
Set @n10 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n11 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n12 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @d1 = @n12*2+@n11*3+@n10*4+@n9*5+@n8*6+@n7*7+@n6*8+@n5*9+@n4*2+@n3*3+@n2*4+@n1*5
Set @d1 = 11 - (@d1%11)
if (@d1>=10)
Set @d1 = 0
Set @d2 = @d1*2+@n12*3+@n11*4+@n10*5+@n9*6+@n8*7+@n7*8+@n6*9+@n5*2+@n4*3+@n3*4+@n2*5+@n1*6
Set @d2 = 11 - (@d2%11)
if (@d2>=10)
Set @d2 = 0


Select
Cast(@n1 as VarChar)+
Cast(@n2 as VarChar)+'.'+
Cast(@n3 as VarChar)+
Cast(@n4 as VarChar)+
Cast(@n5 as VarChar)+'.' +
Cast(@n6 as VarChar)+
Cast(@n7 as VarChar)+
Cast(@n8 as VarChar)+'/'+
Cast(@n9 as VarChar)+
Cast(@n10 as VarChar)+
Cast(@n11 as VarChar)+
Cast(@n12 as VarChar)+'-'+
Cast(@d1 as VarChar)+
Cast(@d2 as VarChar)

Gerar CPF Válido

Por razões obvias de segurança de informação nós temos que trocar alguns dados (scramble) de informações confidenciais quando atualizamos as bases de desenvolvimento e homologação. Uma delas é o CPF, essa rotina cria CPFs validos e randomicos:



Declare @n Int, @n1 Int, @n2 Int, @n3 Int, @n4 Int, @n5 Int, @n6 Int, @n7 Int, @n8 Int, @n9 Int
Declare @d1 Int, @d2 Int

Declare @NumeroCPFs Int

Create Table #TabelaCPF (sCPF VarChar(14))

Set @NumeroCPFs = 1000

While @NumeroCPFs > 0
Begin
Set @n = 9;
Set @n1 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n2 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n3 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n4 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n5 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n6 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n7 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n8 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @n9 = Cast((@n + 1) * RAND(CAST(NEWID() AS varbinary )) as int)
Set @d1 = @n9*2+@n8*3+@n7*4+@n6*5+@n5*6+@n4*7+@n3*8+@n2*9+@n1*10;
Set @d1 = 11 - (@d1%11);
if (@d1>=10)
Set @d1 = 0
Set @d2 = @d1*2+@n9*3+@n8*4+@n7*5+@n6*6+@n5*7+@n4*8+@n3*9+@n2*10+@n1*11;
Set @d2 = 11 - ( @d2%11 );
if (@d2>=10)
Set @d2 = 0;

Insert Into #TabelaCPF Values (
Cast(@n1 as VarChar)+
Cast(@n2 as VarChar)+
Cast(@n3 as VarChar)+'.'+
Cast(@n4 as VarChar)+
Cast(@n5 as VarChar)+
Cast(@n6 as VarChar)+'.'+
Cast(@n7 as VarChar)+
Cast(@n8 as VarChar)+
Cast(@n9 as VarChar)+'-'+
Cast(@d1 as VarChar)+
Cast(@d2 as VarChar))

Set @NumeroCPFs = @NumeroCPFs - 1

End


Select * from #TabelaCPF

Drop Table #TabelaCPF

Recuperar banco em Suspect

Aproveitando o assunto Apagão, como o que aconteceu essa madrugada (10/11/2009) em SP e mais um monte de lugares, é até comum, quando há problemas com os NoBreaks e Geradores, ocorrer algum problema com os servidores de banco de dados e eles voltarem como Suspects.

Basicamente, se não houver problemas muito mais sérios, a receita é:


EXEC sp_resetstatus 'Nome_BancoDados';

ALTER DATABASE Nome_BancoDados SET EMERGENCY

DBCC checkdb('Nome_BancoDados')

ALTER DATABASE Nome_BancoDados SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('Nome_BancoDados', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE Nome_BancoDados SET MULTI_USER

Renomear os arquivos e nome lógico dos arquivos de um banco.

Select Name, Physical_Name
from BDDesenvOld.Sys.Database_files

Name Physical_Name
------------------- ----------------------------------
BDDesenv_Temp E:\DataSQL\BDDesenv.mdf
BDDesenv_Temp_log E:\DataSQL\BDDesenv_log.ldf



USE master
GO
ALTER DATABASE BDDesenvOld SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE BDDesenvOld
MODIFY FILE (NAME = BDDesenv_Temp, NEWNAME = BDDesenvOld, FILENAME = 'E:\DataSQL\BDDesenvOld.mdf')
GO
ALTER DATABASE BDDesenvOld
MODIFY FILE (NAME = BDDesenv_Temp_log, NEWNAME = BDDesenvOld_log, FILENAME = 'E:\DataSQL\BDDesenvOld_log.ldf')
GO
alter database BDDesenvOld set offline
GO
/*

Agora tem que renomear o arquivo na mão pelo SO

*/

alter database BDDesenvOld set Online
GO
ALTER DATABASE BDDesenvOld SET multi_user





Select Name, Physical_Name
from BDDesenvOld.Sys.Database_files


Name Physical_Name
--------------------- -------------------------------------
BDDesenvOld E:\DataSQL\BDDesenvOld.mdf
BDDesenvOld_log E:\DataSQL\BDDesenvOld_log.ldf

Renomear um database.

Para renomear um banco de dados "em voo", primeiro você precisa deixar o banco de dados em modo exclusivo, para que ninguem mais conecte.

Este procedimento não renomeia o nome fisico do arquivo, somente o nome do banco no SQLServer.


USE [master]
GO

ALTER DATABASE DBDesenv SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DBDesenv MODIFY NAME = DBDesenvOld
GO
ALTER DATABASE DBDesenvOld SET multi_user

Como mover um banco de dados

As vezes, por manutenções em servidores, é necessário mover os arquivos de um banco de dados de uma pasta/drive para outro lugar.

Isso é facilmente resolvido por um detach / atach.



use BDDesenvolvimento

--Para saber exatamente onde o arquivo está.
sp_helpfile

name fileid filename filegroup size maxsize growth usage
---------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------
BDDesenvolvimento 1 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BDDesenvolvimento.mdf PRIMARY 13312 KB Unlimited 10240 KB data only
BDDesenvolvimento_log 2 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\BDDesenvolvimento_log.ldf NULL 5696 KB 2147483648 KB 10% log only


use master
go

--Desvincule o banco de dados do servidor:
sp_detach_db 'BDDesenvolvimento'

go

/*Depois, copie os arquivos de dados e de log do local atual (D:\Mssql7\Data) para um novo local (E:\Sqldata). */

use master
go
--Vincule novamente o banco de dados.
sp_attach_db 'BDDesenvolvimento','e:\DataSQL\BDDesenvolvimento.mdf','e:\LogsSQL\BDDesenvolvimento_log.ldf'
go


use BDDesenvolvimento
GO

--Verifique o novo caminho do banco.
sp_helpfile

name fileid filename filegroup size maxsize growth usage
--------------------- ------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------ ------------------ ---------
BDDesenvolvimento 1 e:\DataSQL\BDDesenvolvimento.mdf PRIMARY 13312 KB Unlimited 10240 KB data only
BDDesenvolvimento_log 2 e:\LogsSQL\BDDesenvolvimento_log.ldf NULL 5696 KB 2147483648 KB 10% log only

Listar as instancias de SQLServer do dominio.

Esta rotina utiliza o OSQL via cmdshell para listar todas as instancias (servidores) SQLServer disponíveis na sua rede.
Lembrando que para utilizar o cmdshell, talvez você precise liberar o acesso no seu servidor.

Declare @SQL as Varchar(100)

If Object_ID('tempdb..#InstanciasSQL') is Not Null
Begin
Drop Table #InstanciasSQL
End

CREATE TABLE #InstanciasSQL ([FName] NVARCHAR(1000))

SET @SQL = 'EXEC XP_CMDSHELL "OSQL -L"'

Insert Into #InstanciasSQL
Exec(@SQL)

Select LTrim(RTrim(FName))
from #InstanciasSQL
Where LTrim(RTrim(FName)) Not in ('Servers:')
And FName is not null

Log de execução do Maintenance Plan

Esse script facilita muito para saber o log de erro na execução de um Maintenance Plan.


With UltimaExecucao as (
Select A.Plan_Id, Max(A.Start_Time) as DataExec
from msdb..sysmaintplan_log A
Inner Join msdb..sysmaintplan_plans B On A.Plan_Id = B.id
Where B.name = 'OrgDiario' --Aqui vai o nome do seu MP
Group by A.Plan_Id)
Select C.Name,
C.Owner,
Case when D.succeeded = 1 then 'True' else 'False' End as Succeeded,
D.line1,
D.line2,
D.line3,
D.line4,
D.line5,
D.start_time,
D.end_time,
D.error_number,
D.error_message
From UltimaExecucao A
Inner Join msdb..sysmaintplan_log B On A.Plan_Id = B.Plan_Id
And A.DataExec = B.start_time
Inner Join msdb..sysmaintplan_plans C On C.id = A.Plan_Id
Inner Join msdb..sysmaintplan_logdetail D On D.task_detail_id = B.task_detail_id

Script para saber quais Backups foram executados e quando

Select B.backup_start_date,
B.backup_finish_date,
B.database_name as source_database_name,
C.physical_device_name as backup_file_used_for_restore
From msdb..backupset B
INNER JOIN msdb..backupmediafamily C ON B.media_set_id = C.media_set_id
Order by B.backup_start_date DESC

Script para saber quais Restores foram executados e quando

Select A.destination_database_name,
A.restore_date,
B.backup_start_date,
B.backup_finish_date,
B.database_name as source_database_name,
C.physical_device_name as backup_file_used_for_restore
From msdb..restorehistory A
INNER JOIN msdb..backupset B ON A.backup_set_id = B.backup_set_id
INNER JOIN msdb..backupmediafamily C ON B.media_set_id = C.media_set_id
Order by A.restore_date DESC

Cálculo de Feriados Móveis

Seguindo regras de cálculos já bastante conhecidas na internet, segue implementação em SQLServer dos feriados móveis no Brasil.


Declare @ano int

Set @ano = 2009

DECLARE
@seculo INT,
@G INT,
@K INT,
@I INT,
@H INT,
@J INT,
@L INT,
@MesDePascoa INT,
@DiaDePascoa INT,
@pascoa smalldatetime


SET @seculo = @ano / 100
SET @G = @ano % 19
SET @K = ( @seculo - 17 ) / 25
SET @I = ( @seculo - CAST(@seculo / 4 AS int) - CAST(( @seculo - @K ) / 3 AS int) + 19 * @G + 15 ) % 30
SET @H = @I - CAST(@I / 28 AS int) * ( 1 * -CAST(@I / 28 AS int) * CAST(29 / ( @I + 1 ) AS int) ) * CAST(( ( 21 - @G ) / 11 ) AS int)
SET @J = ( @ano + CAST(@ano / 4 AS int) + @H + 2 - @seculo + CAST(@seculo / 4 AS int) ) % 7
SET @L = @H - @J
SET @MesDePascoa = 3 + CAST(( @L + 40 ) / 44 AS int)
SET @DiaDePascoa = @L + 28 - 31 * CAST(( @MesDePascoa / 4 ) AS int)
SET @pascoa = CAST(@MesDePascoa AS varchar(2)) + '-' + CAST(@DiaDePascoa AS varchar(2)) + '-' + CAST(@ano AS varchar(4))


Select @pascoa as 'Pascoa',
DateAdd(dd, -3, @pascoa) as 'Sexta-Feira Paixao',
DateAdd(dd, -47, @pascoa) as 'Quarta Carnaval',
DateAdd(dd, 60, @pascoa) as 'CORPUS CHRISTI'

Miudezas Parte 4: Caminho do arquivo fisico do bd.

/*
Select * from sys.database_files

Select * from sys.Master_Files

Select * from Sys.Databases

*/

Select A.Database_id,
A.Type_Desc as 'Tipo',
B.Name as 'Banco',
A.Name as 'Nome Arquivo',
A.Physical_Name as 'Caminho Arquivo'
from Sys.Master_Files A
Inner Join Sys.Databases B On A.Database_Id = B.Database_Id
Order by B.Name, Tipo DESC

Métodos Hexadecimal em SQLServer

/*

Representação String to Hex

*/
DECLARE @HEXB AS varbinary(1000) ,
@HEXV AS varchar(1000)

-- Convert hexstring value in a variable to varbinary:

DECLARE @hexstring varchar(max) ;
SET @hexstring = 'abcedf012439' ;
SELECT @HEXB =
CAST('' AS xml).value('xs:hexBinary( substring(sql:variable("@hexstring"), sql:column("t.pos")) )' , 'varbinary(max)')
FROM
( SELECT
CASE substring(@hexstring , 1 , 2)
WHEN '0x' THEN 3
ELSE 0
END ) AS t ( pos )

Select @HEXB,
SQL_VARIANT_PROPERTY(@HEXB,'BaseType') AS '@HEXB Base Type'
GO


/*

Hex p/ representação em string - Function não documentada

*/
select master.dbo.fn_varbintohexstr(@HEXB) as String

--OU

DECLARE @hexbin varbinary(max)
SET @hexbin = 0xabcedf012439
Set @HEXV = '0x' + CAST('' AS xml).value('xs:hexBinary(sql:variable("@hexbin") )' , 'varchar(max)')
Select @HEXV,
SQL_VARIANT_PROPERTY(@HEXV,'BaseType') AS '@HEXV Base Type'
GO


/*

Valor correspondente em string/int

*/

DECLARE @HEXB AS varbinary(1000),
@charvalue as VarChar(1000)
Set @HEXB = 0x46617573746F

declare @vc varchar(8)
declare @vi Int
declare @vb varbinary(8)

set @vb = @HEXB
set @vc = CONVERT(varchar(8),@vb,2)

SELECT @vb, @vc

set @vb = 0x000000C1
set @vc = CONVERT(Int,@vb,2)
SELECT @vb, @vc


/*

Hex p/ representação em string

*/

DECLARE @HEXB AS varbinary(1000),
@charvalue as VarChar(1000)
Set @HEXB = 0x193

Set @charvalue = '0x' + cast('' as xml).value('xs:hexBinary(sql:variable("@HEXB") )', 'varchar(max)');

SELECT SQL_VARIANT_PROPERTY(@charvalue,'BaseType') AS '@charvalue Base Type',
SQL_VARIANT_PROPERTY(@charvalue,'Precision') AS '@charvalue Precision',
SQL_VARIANT_PROPERTY(@charvalue,'Scale') AS '@charvalue Scale',
SQL_VARIANT_PROPERTY(@charvalue,'MaxLength') AS '@charvalue MaxLength',
@charvalue as '@charvalue Valor'

SELECT


/*

Conversão de String / Int p/ HEX


*/

declare @hexstring varchar(max);
set @hexstring = '193';

select CONVERT(varbinary(max), @hexstring, 1);

set @hexstring = '193';
select CONVERT(varbinary(max), @hexstring, 2);

declare @hexInt Int;
set @hexInt = '193';
select CONVERT(varbinary(max), @hexInt, 2);

Combinação de valores Base-2.

Combinação de valores Base 2 é bastante utilizado em "campos" onde precisamos guardar uma combinação de "possibilidades" ou opções em um único campo. Por exemplo em campos de status ou pendencias.

O valor resultante desta combinação único, nenhuma outra combinação resulta no mesmo valor, então com uma lógica simples é possivel achar essa combinação.

Um exemplo disso são os campos Status e Status2 da tabela sys.databases ( http://msdn.microsoft.com/pt-br/library/ms179900.aspx )

Select Name, Status, Status2 from Master.dbo.sysdatabases

Name Status Status2
---------- ----------- -----------
master 65544 1090520064

O Status 65544 é a combinação de 65536 e 8
O Status2 1090520064 é a combinação de 1073741824, 16777216 e 1024

De qualquer forma, a sequencia é sempre número anterior * 2, ou em base-2: 2^n.

2, 4, 8, 16, 32, 64, 128, 256, 512, 1024, etc, etc, etc.....

Seguindo o exemplo de "pendências", para facilitar vamos imagina um caso onde um cliente fez um cadastro em uma loja para contratar um financiamento, e está "devendo" alguns documentos para terminar o processo do financiamento:

Código Valor Descrição
1 2 Falta RG
2 4 Falta CPF
3 8 Falta Certidão de Nascimento
4 16 Falta Certidão de Casamento
5 32 Falta Certidão Negativa
6 64 Falta Comprovante de Renda
7 128 Falta Comprovante de Residencia
8 256 Falta Reservista
9 512 Falta Passaporte
10 1024 Falta Comprovante de Votação

Combinando os valores 3 (8), 5 (32), 7 (128), 8 (256) e 10 (1024) = 1448
Ainda faltam para o cliente:
- Falta Certidão de Nascimento
- Falta Certidão Negativa
- Falta Comprovante de Residencia
- Falta Reservista
- Falta Comprovante de Votação

Uma forma simples de se fazer isso é, sabendo que a soma dos números anteriores nunca é maior ou igual à atual, fazer um loop decrescente "easy cake" diminuindo do resultado final +/- assim:

/************************************************************************************************/
Declare @Numero float, @LimiteSuperior float, @iLimiteSuperior Int

Set @Numero = 1448

Set @LimiteSuperior = 562949953421312 -- 49 opções se precisar mais que isso é só calcular.

Set @iLimiteSuperior = 49

Declare @Resultados table (Base int, Valor float)
While @Numero > 0
Begin
If @LimiteSuperior < = @Numero Begin

Insert Into @Resultados Values (@iLimiteSuperior, @LimiteSuperior)
Set @Numero = @Numero - @LimiteSuperior

End

Set @LimiteSuperior = @LimiteSuperior / 2 --Acha o valor anterior
Set @iLimiteSuperior = @iLimiteSuperior - 1

End

Select * from @Resultados

/************************************************************************************************/

Outra forma, que eu acho mais interessante, tambem é um loop decrescente, porem trabalhamos com Log Base-2 que conseguimos com a divisão: LOG(Numero)/LOG(2).

/************************************************************************************************/
Declare @Numero float, @Expoente float
Declare @Resultados table (Base int, Valor float)

Set @Numero = 1448

While @Numero > 0
Begin

Set @Expoente = FLOOR(LOG(@Numero)/LOG(2))
Insert Into @Resultados Values (@Expoente, POWER(cast(2 as float), @Expoente))
set @Numero = @Numero - POWER(cast(2 as float), @Expoente)

End

Select * from @Resultados

Gerar Senhas Randomicas parte 2.

Esse código eu utilizava em uma Function para gerar senhas randomicas em algumas ocasiões no sistema.

Como parametro de entrada da function eu passava os itens abaixo e recebia lista de senhas como Table.

@GrupoBitMinuscula
@GrupoBitNumericos
@GrupoBitEspeciais
@GrupoBitDiferenci
@GrupoCharDiferenci
@QuantidadeSenha
@TamanhoSenha


Declare @GrupoCharMaiuscula VarChar(26),
@GrupoCharMinuscula VarChar(26),
@GrupoCharNumericos VarChar(10),
@GrupoCharEspeciais VarChar(26),
@GrupoCharDiferenci VarChar(Max)

Declare @GrupoBitMaiuscula Bit,
@GrupoBitMinuscula Bit,
@GrupoBitNumericos Bit,
@GrupoBitEspeciais Bit,
@GrupoBitDiferenci Bit

Declare @GrupoCharTotal VarChar(Max),

Declare @TextoSenha Table (dsSenha VarChar(Max))

Declare @tmpTextoSenha VarChar(Max)

Declare @TamanhoSenha Int,
@QuantidadeSenha Int

Set @QuantidadeSenha = 10 --Quantidade de senhas para serem geradas
Set @GrupoCharMaiuscula = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
Set @GrupoCharMinuscula = 'abcdefghijklmnopqrstuvwxyz'
Set @GrupoCharNumericos = '1234567890'
Set @GrupoCharEspeciais = '!#%&()*+,-./:;<=>@[]^_{}~'
Set @GrupoCharDiferenci = ''

Set @GrupoBitMaiuscula = 1
Set @GrupoBitMinuscula = 1
Set @GrupoBitNumericos = 1
Set @GrupoBitEspeciais = 1
Set @GrupoBitDiferenci = 0

Set @GrupoCharTotal = Case when @GrupoBitMaiuscula = 1 Then @GrupoCharMaiuscula Else '' End +
Case when @GrupoBitMinuscula = 1 Then @GrupoCharMinuscula Else '' End +
Case when @GrupoBitNumericos = 1 Then @GrupoCharNumericos Else '' End +
Case when @GrupoBitEspeciais = 1 Then @GrupoCharEspeciais Else '' End +
Case when @GrupoBitDiferenci = 1 Then @GrupoCharDiferenci Else '' End

While @QuantidadeSenha > 0
Begin
set @tmpTextoSenha = ''

Set @TamanhoSenha = 15 --Tamanho da senha em Caracteres

While @TamanhoSenha > 0
Begin
Select @tmpTextoSenha = @tmpTextoSenha + SUBSTRING(@GrupoCharTotal , Cast((Len(@GrupoCharTotal) - 2) * RAND(CAST(NEWID() AS varbinary )) + 1 as int) , 1)
Set @TamanhoSenha = @TamanhoSenha - 1
End
Insert Into @TextoSenha Values (@tmpTextoSenha)
Set @QuantidadeSenha = @QuantidadeSenha - 1
End

Select * from @TextoSenha

Random no SQLServer

A função Rand no SQLServer, assim como descriton o BOL, "Retorna um valor float pseudo-aleatório de 0 a 1, exclusivo" e "Chamadas repetitivas de RAND() com o mesmo valor de semente retornam os mesmos resultados. ". Ou seja, de Randomico não tem praticamente nada.

Mas dá para contornar isso usando alguns artificios.

Exemplo, execute algumas vezes repetidas esta linha:
SELECT RAND(), RAND(3);

Os resultados não mudam.

Idem Aqui:
SELECT RAND(159784);
SELECT RAND(159784);
SELECT RAND(159784);

Aqui vou apresentar 2 meios (Existem vários) de fazer com que o Rand realmente seja um Rand, utilizando seu parametro de seed.

1) Usando uma Data:

SELECT RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) );

2) Usando o o NewID:

SELECT RAND(CAST( NEWID() AS varbinary ));


Com isso em mãos, é possivel então estabelecer limites Mínimos e Máximos para o Random:

DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number

--Para gerar Randomicamente os números em float:
SELECT Cast((@Upper - @Lower -1) * RAND(CAST(NEWID() AS varbinary)) + @Lower as Float)

--Para gerar Randomicamente os números em Int:
SELECT Cast((@Upper - @Lower -1) * RAND(CAST( NEWID() AS varbinary )) + @Lower as Int)

Gerar Senhas Randomicas parte 1.

Este código gera senhas randomicas usando como suporte a spt_values.


declare @password varchar(15) -- Aqui o 15 é a quantidade de caracteres

set @password=''

select @password=@password+char(n) from
(
Select Top 15 Number as N -- Aqui o 15 é a quantidade de caracteres
From master..spt_values
Where type='p'
And number between 48 and 122
Order by newid()
) as t

select @password

Retornar os valores afetados nos Inserts, Updates ou Deletes.

Outra forma mais profissional de se fazer o que foi descrito no post ("Update em Tabela e em variavel ao mesmo tempo.") é utilizando a cláusula OUTPUT do Insert, Update ou Delete.

Ela contém os valores Deleted e Inserted de todas as colunas do update, independente da quantidade de linhas que o Insert, Update ou Delete afetaram:


create table #permanent(id int, d float, comment varchar(15))
go
insert into #permanent values(1, 10., 'Original Row')
insert into #permanent values(2, 10., 'Original Row')
insert into #permanent values(3, 10., 'Original Row')
go

create table #staging(id int, d float)
go
insert into #staging values(2, 15.)
insert into #staging values(3, 15.)
insert into #staging values(4, 15.)
go

declare @updated_ids table(id int, d float, commentAnt varchar(15), commentNew varChar(15))

update #permanent
set d = s.d, comment = 'Modified Row'
output inserted.id, inserted.d, deleted.comment, inserted.comment into @updated_ids
from #permanent p, #staging s
where p.id=s.id

Select *
From @updated_ids

Drop Table #staging
Drop Table #permanent

Split parte 3

Assim como o post Split parte 2, este exemplo quebra uma linha separada por ";" em linhas usando recursos do XQuery, porém a partir de uma tabela e não de uma variavel com valores fixos.




Create Table #tmpLista (idLinha Int, xCol VarChar(max))
Insert Into #tmpLista Values (1, 'Linha A1;Linha B1;Linha C1;Linha D1;Linha E1;Linha F1')
Insert Into #tmpLista Values (2, 'Linha A2;Linha B2;Linha C2;Linha D2;Linha E2;Linha F2')
Insert Into #tmpLista Values (3, 'Linha A3;Linha B3;Linha C3;Linha D3;Linha E3;Linha F3')
Insert Into #tmpLista Values (4, 'Linha A4;Linha B4;Linha C4;Linha D4;Linha E4;Linha F4')
Insert Into #tmpLista Values (5, 'Linha A5;Linha B5;Linha C5;Linha D5;Linha E5;Linha F5')
Insert Into #tmpLista Values (6, 'Linha A6;Linha B6;Linha C6;Linha D6;Linha E6;Linha F6')
Insert Into #tmpLista Values (7, 'Linha A7;Linha B7;Linha C7;Linha D7;Linha E7;Linha F7')
Insert Into #tmpLista Values (8, 'Linha A8;Linha B8;Linha C8;Linha D8;Linha E8;Linha F8')
Insert Into #tmpLista Values (9, 'Linha A9;Linha B9;Linha C9;Linha D9;Linha E9;Linha F9')
Insert Into #tmpLista Values (10, 'Linha A10;Linha B10;Linha C10;Linha D10;Linha E10;Linha F10')
DECLARE @delimiter NVARCHAR(5)
Set @delimiter = ';'
;with tbLista as (
SELECT CAST ( '' + REPLACE ( xCol , @delimiter , '' ) + '' AS xml ) AS xCol, idLinha
From #tmpLista
Where idLinha Between 1 and 5
)
SELECT T.split.value('.', 'nvarchar(max)') AS data, idLinha
FROM tbLista CROSS APPLY xCol.nodes('/d') T (split)

Drop Table #tmpLista

Update em Tabela e em variavel ao mesmo tempo.

Me perguntaram como poderiam fazer um update de uma tabela e ao mesmo tempo um "set" de uma variavel com o valor antigo do update.

Achei meio sem sentido, talvez só para economizar um select, mas de qualquer jeito posto aqui:


Create Table #Tabela1 (id Int, Nome Varchar(50))

Insert Into #Tabela1 Values (1, 'AAA')
Insert Into #Tabela1 Values (2, 'BBB')
Insert Into #Tabela1 Values (3, 'CCC')
Insert Into #Tabela1 Values (4, 'DDD')
Insert Into #Tabela1 Values (5, 'EEE')

Declare @Nome VarChar(50)

Update #Tabela1
Set @Nome = Nome,
Nome = 'AAA1'
Where id = 1

Select @Nome as 'Antigo'

Select * from #Tabela1 Where id = 1

Drop Table #Tabela1

Join Array - Concatenar registros parte 2

Este exemplo, utilizando recursos simples do SQLServer, faz um "join" dos registros em uma string separados por ";" em uma variavel.


DECLARE @Procedures varchar(MAX)

Select @Procedures = COALESCE(@Procedures + ', ', '') + name
From sysobjects
Where name like 'sp_%'

Select @Procedures

Join Array - Concatenar registros parte 1

Este é outro exemplo utilizando os recursos de XML do SQLServer. Ele faz um "join" dos registros em uma string separados por ";"

A rigor só a query abaixo já faz o "join", mas ela retorna como XML:

Select cast(name as varchar) + '; ' AS [text()]
from dbo.sysobjects
where name like 'sp_%'
For XML PATH ('')

Então para retornar em String:


Select (Select cast(name as varchar) + '; ' AS [text()]
from dbo.sysobjects
where name like 'sp_%'
For XML PATH ('')) as Array

Split parte 2

Esse exemplo quebra uma linha separada por ";" em linhas, também usando recursos do XQuery.


DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5)

DECLARE @textXML XML;

Set @data = 'Linha A1;Linha B1;Linha C1;Linha D1;Linha E1;Linha F1'

SELECT @textXML = CAST('' + REPLACE(@data, ';', '') + '' AS XML);

SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T (split)

Split parte 1

Esse exemplo quebra uma linha separada por ";" em colunas, usando recursos do XQuery.


Create Table #tmpLista (xCol VarChar(max))
Insert Into #tmpLista Values ('Linha A1;Linha B1;Linha C1;Linha D1;Linha E1;Linha F1')
Insert Into #tmpLista Values ('Linha A2;Linha B2;Linha C2;Linha D2;Linha E2;Linha F2')
Insert Into #tmpLista Values ('Linha A3;Linha B3;Linha C3;Linha D3;Linha E3;Linha F3')
Insert Into #tmpLista Values ('Linha A4;Linha B4;Linha C4;Linha D4;Linha E4;Linha F4')
Insert Into #tmpLista Values ('Linha A5;Linha B5;Linha C5;Linha D5;Linha E5;Linha F5')
Insert Into #tmpLista Values ('Linha A6;Linha B6;Linha C6;Linha D6;Linha E6;Linha F6')
Insert Into #tmpLista Values ('Linha A7;Linha B7;Linha C7;Linha D7;Linha E7;Linha F7')
Insert Into #tmpLista Values ('Linha A8;Linha B8;Linha C8;Linha D8;Linha E8;Linha F8')
Insert Into #tmpLista Values ('Linha A9;Linha B9;Linha C9;Linha D9;Linha E9;Linha F9')
Insert Into #tmpLista Values ('Linha A10;Linha B10;Linha C10;Linha D10;Linha E10;Linha F10')

;WITH tmpListaDados (DadosXML) AS (
SELECT CAST('' + REPLACE(xCol,';','') + '' AS XML)
FROM #tmpLista)
SELECT CAST(DadosXML.query('for $coluna in /N return $coluna/n[1]/text()') AS VARCHAR(max)) As Coluna1,
CAST(DadosXML.query('for $coluna in /N return $coluna/n[2]/text()') AS VARCHAR(max)) As Coluna2,
CAST(DadosXML.query('for $coluna in /N return $coluna/n[3]/text()') AS VARCHAR(max)) As Coluna3,
CAST(DadosXML.query('for $coluna in /N return $coluna/n[4]/text()') AS VARCHAR(max)) As Coluna4,
CAST(DadosXML.query('for $coluna in /N return $coluna/n[5]/text()') AS VARCHAR(max)) As Coluna5,
CAST(DadosXML.query('for $coluna in /N return $coluna/n[6]/text()') AS VARCHAR(max)) As Coluna6
FROM tmpListaDados

Drop Table #tmpLista

O que o seu SQLServer está executando agora?

Estas duas querys mostram exatamente o que está sendo executado nas sessões ativas do banco de dados.

É bastante útil quando você está com problemas de execução no banco e precisa saber o que está causando os problemas.

/********************************************************/
/* Mosta o SQL que cada sessão está executando */
/********************************************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END -
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 -- Ignore system spids.
AND session_Id NOT IN (@@SPID) -- Ignore this current statement.
ORDER BY 1, 2

SELECT SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
--The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
--The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] - SDER.[statement_start_offset])/2)
END
ELSE
--1st part of full command is running
CASE SDER.[statement_end_offset]
WHEN -1 THEN
--The end of the full command is also the end of the active statement
RTRIM(LTRIM(DEST.[text]))
ELSE
--The end of the active statement is not at the end of the full command
LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)
END
END AS [executing statement],
DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id > 50
ORDER BY SDER.[session_id], SDER.[request_id]

Estatísticas e ShowPlan de uma Query.

Para exibir as estatísticas e o show plan de uma query:

Os exemplos abaixo mostram os tipos de estatísticas.
/**************************************************************************************************************//* *//* Statistics */ /* */ /**************************************************************************************************************/

SET STATISTICS TIME ON
go
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
go
SET STATISTICS TIME Off

SET STATISTICS IO ON;
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
GO
SET STATISTICS IO OFF;


SET STATISTICS PROFILE On
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
GO
SET STATISTICS PROFILE Off

SET STATISTICS XML ON
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
GO
SET STATISTICS XML Off

/**************************************************************************************************************//* *//* ShowPlan */ /* */ /**************************************************************************************************************/
SET SHOWPLAN_ALL ON;
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Hash Join sys.objects B On B.object_id = A.id
GO
SET SHOWPLAN_ALL OFF;
GO

SET SHOWPLAN_TEXT ON;
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Join sys.objects B On B.object_id = A.id
GO
Select B.object_id, B.name as Tabela, A.name from syscolumns A Inner Hash Join sys.objects B On B.object_id = A.id
GO
SET SHOWPLAN_TEXT Off;
GO

Listar arquivos do diretório.

Para listar os arquivos de alguma pasta do servidor ou de outra maquina da rede, a partir do SQLServer 2000 é possivel usar uma das Stored Procedures não documentada.

Há outras opções, entre elas utilizando a xp_cmdshell e utilizando FSO. Mas de longe esta é a mais simples.

No exemplo a seguir, ele vai listar todos os arquivos e pasta do c:\

Parametros:

1) Pasta inicial: caminho da pasta inicial para pesquisa
2) Nivel da busca: 1 é somente a pasta inicial, 0 ou Null são todas as pastas a partir da pasta inicial informada, qualquer valor > 1 é a quantidade de niveis de subpastas a partir da pasta inicial informada.
3) Inclui arquivo: 1 Inclui arquivos e pastas; 0 ou Null só pastas.

EXEC master.dbo.xp_dirtree N'c:\', 1, 1

O retorno é:

1) SubDirectory - Nome da pasta ou arquivo
2) Depth - Nivel do arquivo em relação à pasta inicial da pesquisa.
3) File - 1 para arquivo e 0 para Pasta

Ela vale tambem para diretórios de maquinas da rede, desde que estejam compartilhadas (não precisam estar mapeadas)

EXEC master.dbo.xp_dirtree N'\\Servidor_de_rede\comum', 1, 1

Espaço em disco 3

Esta solução realmente é a que eu menos gosto, mas é bastante utilizada por usar a xp_cmdshell.

No exemplo abaixo, utilizando a xp_cmdshell a rotina executa a aplicação DOS fsutil retornando o espaço livre do disco C:. Este retorno é gravado em uma tabela temporária e depois separado e mostrado.


DECLARE @Drive varchar(max),
@SQL VARCHAR(max)

SET @SQL = 'EXEC XP_CMDSHELL "fsutil volume diskfree c:"'

Create Table #tmpLista (xCol VarChar(max))

Insert Into #tmpLista
Exec(@SQL)

Delete #tmpLista Where xCol is null

;WITH tmpDrivers (DadosXML) AS (
SELECT CAST('' + REPLACE(xCol,':','') + '' AS XML)
FROM #tmpLista)
SELECT CAST(DadosXML.query('for $coluna in /N return $coluna/n[1]/text()') AS VARCHAR(80)) As Descricao,
--CAST(DadosXML.query('for $coluna in /N return $coluna/n[last()]/text()') AS VARCHAR(80)) As Tamanho
CAST(DadosXML.query('for $coluna in /N return $coluna/n[2]/text()') AS VARCHAR(80)) As Tamanho
FROM tmpDrivers

Drop Table #tmpLista

Espaço em disco 2

Esta é sem dúvida a mais complicada, visto que, utiliza objetos do servidor (Scripting.FileSystemObjects) com a SP sp_OACreate. Porém é a que mais traz dados completos.



IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo

CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Label varchar(10)
)

DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Label varchar(10)

INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)

OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN

SET @cDrive = 'GetDrive("' + @DriveLetter + '")'

EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

IF @Result <> 0
EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Label)
END

FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''

-- Produce report.
SELECT DriveLetter
, Label
, FreeSpace AS [FreeSpace MB]
, (TotalSpace - FreeSpace) AS [UsedSpace MB]
, TotalSpace AS [TotalSpace MB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC
GO

DROP TABLE ##_DriveSpace
DROP TABLE ##_DriveInfo

Espaço em disco 1

Existem algumas formas de se saber o espaço em disco total ou livre do servidor. Esta é uma delas:

Ela executa a Stored Procedure xp_fixeddrives que retorna a lista de drivers do servidor e o espaço livre de cada um, insere estes dados em uma tabela temporaria, para depois cruzar com a informação da function fn_servershareddrives, que mostra se é um drive Shared.


CREATE TABLE #DriveSpace ( Drive char(1), MBFree int )

-- get availabe drives and free space on each of them
INSERT INTO #DriveSpace EXEC xp_fixeddrives

SELECT * INTO #DriveUnion FROM (
SELECT A.DriveName COLLATE Latin1_General_CI_AS [Drives Available],
#DriveSpace.MBFree, 'X'[Shared]
FROM ::fn_servershareddrives() A
LEFT JOIN #DriveSpace on A.DriveName COLLATE Latin1_General_CI_AS = #DriveSpace.drive
UNION
SELECT Drive COLLATE Latin1_General_CI_AS [Drives Available],
MBFree, '-'[Shared]
FROM #DriveSpace
WHERE Drive NOT IN (SELECT DriveName COLLATE Latin1_General_CI_AS
FROM ::fn_servershareddrives()) ) A

Select * from #DriveUnion

Drop Table #DriveUnion
Drop Table #DriveSpace

Executar comandos DOS no SQLServer

A stored procedure xp_cmdshell é utilizada para executar comandos no DOS pelo SQLServer
Por default o SQLServer vem com opção de uso da xp_cmdshell desabilitado, sendo necessário habilitar manualmente pelo administrador do banco.
A liberação da xp_cmdshell implica em alguns riscos de segurança, pesquise sobre os riscos e avalie sua necessidade.
Quando é chamado por um usuário que não é um membro da função de servidor fixa sysadmin, xp_cmdshell se conecta ao Windows usando o nome da conta e a senha armazenadas na credencial chamada ##xp_cmdshell_proxy_account##. Se essa credencial de proxy não existir, xp_cmdshell falhará.
Para habilitar a opção de uso da xp_cmdshell:
sp_configure 'xp_cmdshell', '1'
GO
Reconfigure
Go

Aqui vai um exemplo usando o comando Dir C:\*.*:

Declare @DirCmd as Varchar(100)
Declare @Return as Varchar(Max)
Drop Table #dirlist
CREATE TABLE #dirlist ([FName] NVARCHAR(1000), [DS] VARCHAR(14), [CreateDate] DATETIME)
SET @DirCmd = 'dir C:\*.*'
EXEC @Return = master..xp_cmdshell @DirCmd

Várias informações do seu servidor

Mais informações detalhadas sobre seu servidor:

Select
SERVERPROPERTY('BuildClrVersion') BuildClrVersion,
SERVERPROPERTY('Collation') Collation,
SERVERPROPERTY('CollationId') CollationId,
SERVERPROPERTY('ComparisonStyle') ComparisonStyle,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('Edition') Edition,
SERVERPROPERTY('EditionID') EditionID,
SERVERPROPERTY('EngineEdition') EngineEdition,
SERVERPROPERTY('InstanceName') InstanceName,
SERVERPROPERTY('IsClustered') IsClustered,
SERVERPROPERTY('IsFullTextInstalled') IsFullTextInstalled,
SERVERPROPERTY('IsIntegratedSecurityOnly') IsIntegratedSecurityOnly, SERVERPROPERTY('IsSingleUser') IsSingleUser,
SERVERPROPERTY('LCID') LCID,
SERVERPROPERTY('LicenseType') LicenseType,
SERVERPROPERTY('MachineName') MachineName,
SERVERPROPERTY('NumLicenses') NumLicenses,
SERVERPROPERTY('ProcessID') ProcessID,
SERVERPROPERTY('ProductVersion') ProductVersion,
SERVERPROPERTY('ProductLevel') ProductLevel,
SERVERPROPERTY('ResourceLastUpdateDateTime') ResourceLastUpdateDateTime,
SERVERPROPERTY('ResourceVersion') ResourceVersion,
SERVERPROPERTY('ServerName') ServerName,
SERVERPROPERTY('SqlCharSet') SqlCharSet,
SERVERPROPERTY('SqlCharSetName') SqlCharSetName,
SERVERPROPERTY('SqlSortOrder') SqlSortOrder,
SERVERPROPERTY('SqlSortOrderName') SqlSortOrderName,
SERVERPROPERTY('FilestreamShareName') FilestreamShareName,
SERVERPROPERTY('FilestreamConfiguredLevel') FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') FilestreamEffectiveLevel
GO


A xp_msver retorna:

ProductName
ProductVersion
Language
Platform
Comments
CompanyName
FileDescription
FileVersion
InternalName
LegalCopyright
LegalTrademarks
OriginalFilename
PrivateBuild
SpecialBuild
WindowsVersion
ProcessorCount
ProcessorActiveMask
ProcessorType
PhysicalMemory
Product ID

exec master..xp_msver
GO



A sp_helpserver retorna entre outras informações o collation do banco, sempre o id=0 é seu banco atual

exec master..sp_helpserver


A sp_server_info retorna:

DBMS_NAME
DBMS_VER
OWNER_TERM
TABLE_TERM
MAX_OWNER_NAME_LENGTH
TABLE_LENGTH
MAX_QUAL_LENGTH
COLUMN_LENGTH
IDENTIFIER_CASE
TX_ISOLATION
COLLATION_SEQ
SAVEPOINT_SUPPORT
MULTI_RESULT_SETS
ACCESSIBLE_TABLES
USERID_LENGTH
QUALIFIER_TERM
NAMED_TRANSACTIONS
SPROC_AS_LANGUAGE
ACCESSIBLE_SPROC
MAX_INDEX_COLS
RENAME_TABLE
RENAME_COLUMN
DROP_COLUMN
INCREASE_COLUMN_LENGTH
DDL_IN_TRANSACTION
DESCENDING_INDEXES
SP_RENAME
REMOTE_SPROC
SYS_SPROC_VERSION


exec master..sp_server_info

Log no Trace do Banco de Dados

Assim como o Event Viewer, em alguns processo é bom gravar algo no Trace do do servidor.

E esse input de evento no Trace é feita com a sp_trace_generateevent

1) event_id - Numero do evento, ele deve ser entre 82 e 91 e deve ser previamente criado pela sp_trace_setevent

2) userinfo - Opcional, uma descrição de até 128 posições sobre o evento.

3) userdata - Opcional, uma descrição de até 8000 posições VarBinary do evento.


EXEC master..sp_trace_generateevent 82, 'Algum evento que mereça uma entrada manual no Trace'

Log no Event Viewer pelo SQLServer.

Em alguns processo é bom gravar algo no Event Viewer do servidor, isso é uma prática muito utilizada por programadores de sistemas em suas aplicações.

No SQLServer tambem dá para gravar Logs no Event Viewer.

1) error_number - É um número de erro definido pelo usuário maior que 50.000. O valor máximo é 2147483647 (2^31 - 1).

2) message - É uma cadeia de caracteres com no máximo 2048 caracteres.

3) severity - É uma destas três cadeias de caracteres: INFORMATIONAL, WARNING ou ERROR. severity é opcional, com um padrão INFORMATIONAL.

EXEC xp_logevent 60000, 'Este é um teste de de Log Event pelo SQLServer', INFORMATIONAL

Lá no servidor o resultado no Event Viewer foi:

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17061
Date: 20/8/2009
Time: 18:34:06
User: **********
Computer: **********
Description:
Error: 60000 Severity: 10 State: 1 Este é um teste de de Log Event pelo SQLServer
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Ler o Log do banco de dados via query

Quer ler o Log do seu banco de dados via query sem precisar abrir o arquivo?
Existe uma Stored Procedure não documentada no SQLServer que faz isso.
Lembramos que as Stored Procedure não documentadas a MS não garante sua continuidade em próximas versões e nem seu funcionamento perfeito.

Os parâmetros de entrada dela são:
1) Número do arquivop de Log: 0 = Atual, 1 = Arquivo #1, 2 = Arquivo #2, etc...
2) Tipo do arquivo de Log: 1 or NULL = Log de Erro, 2 = Log do SQL Agent
3) Search string 1: String para busca nos resultados
4) Search string 2: String para refinamento da busca
5) Data inicial para busca
6) Data final para busca
7) Ordenação dos resultados: N'asc' = ascending, N'desc' = descending

EXEC master.dbo.xp_readerrorlog 0, 1, Null, Null, Null, Null, N'desc'

Msg 0, Level 11, State 0, Line 0 / Msg 0, Level 20, State 0, Line 0

Já peguei esses erros no SQLServer 2005 com SP2 ou menor. Ele simplesmente mata sua conexão e acontece normalmente quando são utilizadas as functions
sys.dm_exec_sql_text e sys.dm_exec_query_plan com qualquer Cross Join.

O remédio é instalar o Service Pack 3 ou posterior quando houver para o SQLServer 2005.

Msg 0, Level 11, State 0, Line 0
Erro grave no comando atual. Os resultados, se houver algum, deverão ser descartados.
Msg 0, Level 20, State 0, Line 0
Erro grave no comando atual. Os resultados, se houver algum, deverão ser descartados.

O erro é falta do Service Pack:
http://support.microsoft.com/kb/910416/en-us#top

você sabe quais são as Querys mais custosas do seu Banco?

Pois é..... aqui estão elas, ordenadas por total_logical_reads e com o query plan de cada uma (em xml)

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

View de Gerenciamento Dinâmico sys.dm_os_ring_buffers

A View sys.dm_os_ring_buffers é uma DMV (Dynamic Management Views) muito boa para colher informações sobre a "saúde" do seu servidor.

Apesar de ser um pouco chato a extração de informações do campo Record (xml), vale a pena estar sempre de olho nas informações que esta view traz.

As informações são divididas por tipos (ring_buffer_type):


RING_BUFFER_RESOURCE_MONITOR – memory state changes due to various types of memory pressure

RING_BUFFER_MEMORY_BROKER – notification to components by memory broker advising them to grow, shrink or stay stable

RING_BUFFER_SINGLE_PAGE_ALLOCATOR – when the Buffer Pool single page allocator turns on/off internal memory pressure

RING_BUFFER_OOM – out-of-memory conditions

RING_BUFFER_BUFFER_POOL – severe buffer pool failures, including
buffer pool out-of-memory conditions

RING_BUFFER_SCHEDULER – scheduler operations

RING_BUFFER_SCHEDULER_MONITOR – scheduler health

RING_BUFFER_EXCEPTION – list of exceptions

RING_BUFFER_CLRAPPDOMAIN – state of AppDomains loaded in the system

RING_BUFFER_SECURITY_ERROR – (new in SQL Server 2005 SP2) Windows
API failure information


No post anterior tem um exemplo de como utilizar esta View para verificar o processamento do servidor.

Como está o processamento do servidor.

Usando a tabela de sistema sys.dm_os_ring_buffers, você consegue saber como está o processador do servidor nos últimos 256 minutos.

Estes dados são percentuais entre Idle (Livre), Utilizados pelo SQL e Utilizados por outros processos do sistema (Não SQLServer).

Esta informação combinadas com algumas estatisticas e traces podem te dar uma bela ideia de como está se comportando sua base, por horarios, querys, atividades, etc... e até se ha necessidade de intervensão física no servidor.

declare @ts_now bigint

select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

select record_id, dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime, SQLProcessUtilization, SystemIdle, 100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization from (select record.value('(./Record/@id)[1]', 'int') as record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization, timestamp from (select timestamp, convert(xml, record) as record from sys.dm_os_ring_buffers where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%%') as x ) as y order by record_id desc

Miudezas Parte 3: Transformar TimeStamp em DateTime

As vezes é necessário transformar um TimeStamp que é um varbinary em DateTime (como no post seguinte), isso é meio chato de fazer, mas segue um dos modos:


Select dateadd(ms, -1 * @@DBTS, GetDate()) as EventTime

Separar Nome e Sobrenome

Este código eu peguei do blog do MVP Gustavo Maia Aguiar, que você deve visitar obrigatoriamente!!!


Ele transfoma a string em XML para poder utilizar os comandos XQuery.

No NomeXML.query você nota a $nome/n[1]/text() que pega a primeira parte de nome e o $nome/n[last()]/text() que pega somente a última. Essa solução eu vou usar mais para frente para fazer um "Split" de uma string no SQLServer.


CREATE TABLE #Nomes (Nome VARCHAR(80))
-- Inserção de registros
INSERT INTO #Nomes VALUES ('Joana Maria')
INSERT INTO #Nomes VALUES ('Ana Paula Silveira')
INSERT INTO #Nomes VALUES ('Pedro Paulo Almeida')
INSERT INTO #Nomes VALUES ('Carlos Eduardo da Silva')
INSERT INTO #Nomes VALUES ('Rodrigo Diógenes Cunha Meira')
INSERT INTO #Nomes VALUES ('Maria')


;WITH NomesF (Nome, NomeXML) AS
( SELECT Nome, CAST('' + REPLACE(Nome,' ','') + '' AS XML) FROM #Nomes)
SELECT Nome,
CAST(NomeXML.query('for $nome in /N return $nome/n[1]/text()') AS VARCHAR(80)) As PrimeiroNome,
CAST(NomeXML.query('for $nome in /N return $nome/n[last()]/text()') AS VARCHAR(80)) As SobreNome,
CAST(NomeXML.query('for $nome in /N return concat($nome/n[1],'' '',$nome/n[last()])') AS VARCHAR(80)) As NomeAbreviado,
LEFT(Nome, Case When CHARINDEX(' ',Nome) = 0 Then Len(Nome) Else CHARINDEX(' ',Nome) -1 End) as UsingReverse1,
RIGHT(Nome, Case When CHARINDEX(' ',REVERSE(Nome)) = 0 Then 0 Else CHARINDEX(' ',REVERSE(Nome))-1 end) as UsingReverse2
FROM NomesF

Como deixar uma Stored Procedure como system sp

Um das vantagens de ter uma Stored Procedure como system sp é poder chama-la de qualquer banco de dados do servidor. Isso é realmente muito útil para stored procedures com códigos mais genéricos e com relativa importancia.

Para isso sua Stored Procedure deve ser criada no banco Master.

EXEC sp_ms_marksystemobject 'nome_procedure'
GO

Para verificar se a Stored procedure está como system:


SELECT NAME, IS_MS_SHIPPED
FROM SYS.OBJECTS
WHERE NAME = 'nome_procedure'
GO

Calendário em SQLServer

Achei na Net esse código, achei bem interessante.
Alem de ser um excelente exemplo de CTE com recursividade.


declare @start datetime,
@end datetime
set @start = '2009-01-01'
set @end = '2010-01-01'
;with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as
(
select @start ,
case when datepart(dw,@start) in (1,7) then 0 else 1 end,
year(@start),
datepart(qq,@start),
datepart(mm,@start),
datepart(dd,@start),
datepart(dw,@start),
datename(month, @start),
datename(dw, @start),
datepart(wk, @start)
union all
select date + 1,
case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from calendar where date + 1< @end
)
select * from calendar option(maxrecursion 10000)

Campos Identity

Este post mostra alguns métodos para trabalhar com campos Identitys.

Primeiro, estra query mostra como estão os Identitys de todas as tabelas do seu banco de dados, a tabela, coluna, o tipo, o valor atual e o % de uso:


SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.system_type_id
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC


Para incrementar passo (seed) a um identity e retornar o valor:
SELECT IDENT_INCR('sua_tabela') AS 'IDENT_INCR';

Para mostra o valor atual do identity:
SELECT IDENT_CURRENT('sua_tabela') AS Current_Identity;

Mostra o "valor de passo" (seed) da identity
SELECT IDENT_SEED('sua_tabela') AS 'IDENT_SEED';



Reset na Identity para o próximo valor valido na tabela.
DBCC CHECKIDENT('sua_tabela')

Reset na Identity para o valor informado no último parâmetro.
DBCC CHECKIDENT('sua_tabela', RESEED, 0)



Para desligar o Identity de uma tabela MOMENTANEAMENTE, para um insert:

SET IDENTITY_INSERT sua_tabela On --Desliga o Identity

Faça os Inserts necessários para sua manutenção/carga.

SET IDENTITY_INSERT sua_tabela Off --Religa o Identity

Você sabe a diferença entre Some, Any e All??

Com este exemplo bem simples, fica facil de entender.

CREATE TABLE #T1 (ID int)
GO
INSERT Into #T1 VALUES (1)
INSERT Into #T1 VALUES (2)
INSERT Into #T1 VALUES (3)
INSERT Into #T1 VALUES (4)

Print 'The following query returns TRUE because 3 is less than some of the values in the table.'
IF 3 < SOME (SELECT ID FROM #T1)
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Print 'The following query returns FALSE because 3 is not less than all of the values in the table.'
IF 3 < ALL (SELECT ID FROM #T1)
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Print 'The following query returns TRUE because 3 is less than anyone of the values in the table.'
Print 'SOME is an ISO standard equivalent for ANY.'
IF 3 < Any (SELECT ID FROM #T1)
PRINT 'TRUE'
ELSE
PRINT 'FALSE'

Verificar e Trocar o owner do Banco

Para listar os Bancos e seus owners:


SELECT databases.NAME as Banco,
server_Principals.NAME as Owner
FROM sys.[databases]
INNER JOIN sys.[server_principals] ON [databases].owner_sid = [server_principals].sid



Esta opção deve ser utilizada somente se o banco perdeu o owner, ou é claro, se você tem certeza do que está fazendo.

Para fazer isso você precisa ser SysAdmin ou estar no grupo DB_Owner.

Este exemplo irá trocar o owner do banco de dados atual para SA.


USE Seu_Banco_De_Dados
GO
EXEC sp_changedbowner 'sa'
GO

Detalhes da Execução de um Job.

Para saber os detalhes da execução de um Job:

Com essa Query dá para saber se o Job ainda está executando, ou se já finalizou, com sucesso ou erro, quanto tempo demorou, etc.

/***************************************/
/* Detalhes da Execução do JOB */
/***************************************/
Use msdb
GO
SELECT sjb.Job_id,
stsv.server_id,
stsv.server_name,
stsv.enlist_date,
stsv.last_poll_date,
cast(dateadd(ss, cast(substring(cast(last_run_time + 1000000 as char(7)),6,2) as int),
dateadd(mi, cast(substring(cast(last_run_time + 1000000 as char(7)),4,2) as int),
dateadd(hh, cast(substring(cast(last_run_time + 1000000 as char(7)),2,2) as int),
convert(datetime,cast (last_run_date as char(8)))))) as DateTime) as LastRunDate,
sjs.last_run_duration,
sjs.last_run_outcome,
sjs.last_outcome_message
FROM msdb.dbo.sysjobs sjb
Inner Join msdb.dbo.sysjobservers sjs On sjb.Job_Id = sjs.job_id
Left Join msdb.dbo.systargetservers_view stsv ON (sjs.server_id = stsv.server_id)
WHERE sjb.name = 'BKPIncremental-1.BKPIncremental-1'
Select Top 2 A.*
from msdb..sysjobhistory A
Inner Join msdb.dbo.sysjobs B On A.Job_Id = B.job_id
Where B.name = 'Nome do Seu Job'

Caso tenha dado erro na execução do Job, esta Query, mostra o log do erro do Job:


Select Top 2 A.*
from msdb..sysjobhistory A
Inner Join msdb.dbo.sysjobs B On A.Job_Id = B.job_id
Where B.name = 'Nome do Seu Job'

Os registros sempre vem uma linha para o Job Outcome e uma linha para cada Step do Job, como o exemplo é um Job de um único Step coloquei o Top 2.

Tamanho da tabela no Banco

Essa SP de sistema, mostra o tamanho em KB das seguintes informações da tabela:

1) Quantida de registros da tabela

2) Espaço reservado para a tabela

3) Espaço utilizado pelos dados

4) Espaço utilizado pelos Indices

5) Espaço ainda livre

sp_spaceused 'sua_tabela'

Estas informações são importantes para por exemplo rever um planejamento de capacidade do servidor.

Como está a utilização dos seus Indices?

Depois de criar os indices necessários, o ideal é verificar se realmente eles estão sendo bem aproveitados.

A grosso modo, não adianta você criar um indice em tabelas que são mais utilizadas para inserts/updates do que para consultas.

E isso "talvez" você só saiba algum tempo depois, após algumas estatisticas.

Esta primeira query mostra a quantidade de Inserts, Updates e Deletes por indices.

/*leaf_insert_count - total count of leaf level inserts leaf_delete_count - total count of leaf level inserts leaf_update_count - total count of leaf level updates
*/

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], A.LEAF_INSERT_COUNT, A.LEAF_UPDATE_COUNT, A.LEAF_DELETE_COUNT FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = A.[OBJECT_ID] AND I.INDEX_ID = A.INDEX_ID WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1 And (A.LEAF_INSERT_COUNT > 0 Or A.LEAF_UPDATE_COUNT > 0 Or A.LEAF_DELETE_COUNT > 0)Order by LEAF_INSERT_COUNT DESC

Esta mostra a quantidade de Index Seeks, Index Scans, Index Lookups e operações de Insert, Update e Delete por indices.

/*user_seeks - number of index seeks
user_scans- number of index scans
user_lookups - number of index lookups
user_updates - number of insert, update or delete operations
*/

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 Order by User_Updates DESC, User_Seeks ASC

Miudezas Parte 2: Saber se o SQLAgent está ativo

IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server está ativo mas o SQL Server Agent está INATIVO'
END
ELSE
BEGIN
PRINT 'SQL Server e o SQL Server Agent estão ativos'
END

Miudezas Parte 1: Último dia do mês.

--Último dia do mês anterior
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

--Último dia do mês atual
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

--Último dia do próximo mês
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))

Horas Uptime

Quer saber a quantidade de horas que seu servidor está ativo?

Os primeiros processos das tabela de sysprocesses são iniciado somente quando o banco é iniciado, e não tem como esses processos pararem ou reiniciarem sem parar o banco. Então a partir deles dá p/ saber quanto tempo seu banco está online.



SELECT 'Uptime: ' + Cast(datediff(mi, login_time, getdate()) /60 as VarChar) + ' Horas'
FROM master..sysprocesses WHERE spid = 1

Como iniciar um Job pelo DOS.

Para isso vamos utilizar o aplicativo do SQLServer para DOS OSql. Ele permite que seja executado qualquer instrução ou arquivo sql apenas pelo prompt do DOS, com funcionamento muito semelhante ao SQLCmd (porém muito mais simples) veremos o funcionamento dele mais detalhadamente em um post mais adiante.

Além do OSql, vamos utilizar o Stored Procedure de sistema "sp_start_job" do banco msdb para iniciar o Job.

Vamos supor que o Job que você quer iniciar se chama Job1. Então, no prompt do DOS:

osql -S "NOME_DO_SERVIDOR" -E -Q"exec msdb.dbo.sp_start_job 'Job1'"

Como executar uma Procedure automaticamente no Start do banco.

Algumas vezes é necessário executar algum processo, stored procedure assim que o banco de dados for iniciado. O exemplo abaixo eu criei para me enviar um e-mail, caso o banco seja reiniciado sem eu saber.

Primeiro eu crio o processo que eu quero que seja executado em uma stored procedure. Ela tem que ser criada no Master.

use master
GO
CREATE PROCEDURE sp_AlertStartDBAS
BEGIN
Declare @sbody as VarChar(4000)
Declare @ssubject as VarChar(4000)

set @sbody = 'O servidor ' + CONVERT(sysname, SERVERPROPERTY('servername')) + ' foi inicializado às ' + Cast(getdate() as VarChar)
set @ssubject = 'Inicialização de Banco de Dados: ' + CONVERT(sysname, SERVERPROPERTY('servername'))

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Admin', @recipients = 'seuemail@provedor.com.br', @body = @sbody, @subject = @ssubject;

END



O próximo passo é configurar ela para ser executada no Start usando a proc sp_procoption. ( http://msdn.microsoft.com/en-us/library/ms181720.aspx )

EXECUTE sp_procoption @procname = 'sp_AlertStartDB', @optionname = 'startup', @optionvalue = 'on'

Tamanho físico dos arquivos do banco

Quer saber como está o tamanho fisico dos arquivos do seu banco?

SELECT Name,
[Physical_Name],
Left(CONVERT(VARCHAR, CAST( [SIZE]*8 AS MONEY ), 1),
Len(CONVERT(VARCHAR, CAST( [SIZE]*8 AS MONEY ), 1)) - 3) as Size_KB
FROM sys.database_files

Foreing Key sem Indices

Na maioria das vezes se você tem uma FK em uma tabela, a chance de se fazer buscas por essa chave é enorme, então, com bom senso, é bom ter indices nessas FKs.

Como descobrir quais colunas não possuem índices nas chaves estrangeiras (Foreing Key)

Select Object_Name(constid), Object_Name(fkeyid), Object_Name(rkeyid), * From Sysreferences Where not exists (Select 1 from Sysindexkeys Where id = fkeyid and fkey1 = colid and keyno=1)