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);