Función SQL para Comparar un rango de direcciones IP

Esta función nos sirve para comparar si una dirección IP se encuentre entre un rango de direcciones IP desde-hasta.

Recibe como parámetros la dirección IP a comparar, el rango IP "desde" y el rango IP "hasta", como salida devuelve  1 (bit)  si la IP a comparar se encuentra entre dicho rango, de lo contrario devuelve 0.

En muchos casos las direcciones IP son guardadas en la base como caracteres, para poder comparar un rango IP primero se debe convertir a un BINARY(4) que  es lo que realmente representa a una dirección IP y permite  comprar rangos.

La función es la siguiente:
CREATE FUNCTION dbo.RangoIPv4(@ip AS VARCHAR(15),@ipDesde AS VARCHAR(15),@ipHasta AS VARCHAR(15)) RETURNS BIT
AS
BEGIN
    DECLARE @binIP AS BINARY(4)
    DECLARE @binDesde AS BINARY(4)
    DECLARE @binHasta AS BINARY(4)
    DECLARE @bit as bit

    SELECT @binIP =
CAST( CAST( PARSENAME ( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    SELECT @binDesde =
CAST( CAST( PARSENAME( @ipDesde, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipDesde, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipDesde, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipDesde, 1 ) AS INTEGER) AS BINARY(1))

    SELECT @binHasta =
CAST( CAST( PARSENAME( @ipHasta, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipHasta, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipHasta, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ipHasta, 1 ) AS INTEGER) AS BINARY(1))

set @bit= Case when @binIP between @binDesde and @binHasta then 'TRUE' else 'FALSE' end
 
    RETURN @bit
END
go

Un par de ejemplos de los resultados:

select dbo.RangoIPv4('192.168.10.8','192.168.10.1','192.168.10.255')

Resultado:

-----
1

select dbo.RangoIPv4('192.168.10.8','192.168.10.1','192.168.10.7')

Resultado:

-----
0

Fuente, adaptación del artículo: http://stackoverflow.com/questions/1385552/datatype-for-storing-ip-address-in-sql-server

Función SQL para obtener la edad de una persona

Con esta simple función podremos saber la edad en años a partir de la fecha de nacimiento:


Select dbo.GetEdad('19810522')

-----------
30

(1 row(s) affected)

Script:
CREATE function dbo.GetEdad

(
        @FechaNacimiento DATETIME
)

RETURNS int
Begin
declare @edad  integer
if (@FechaNacimiento is null or @FechaNacimiento = '' )
set @edad = 0
else
if (month(getdate()) > month(@FechaNacimiento))
  set @edad = DateDiff(yyyy,@FechaNacimiento,getdate())
else
  if   (month(getdate()) = month(@FechaNacimiento)
  AND day(getdate()) >= day(@FechaNacimiento))
set @edad = DateDiff(yyyy,@FechaNacimiento,getdate())
  else
set @edad = DateDiff(yyyy,@FechaNacimiento,getdate()) -1
Return  @edad
End

Mandar Mail HTML desde SQLserver

Este es un simple script para enviar un mail desde sql server (2005/2008) con un formato un poco mas amigable:


DECLARE @strMensaje VARCHAR(MAX);
SET @strMensaje = '<p><i><H3>El motivo de este correo es informarte que ha sido seleccionado para segunda entrevista.</H3></i></p>'
SET @strMensaje = @strMensaje + '<p><i><H3>Esperamos se comunique a la brevedad.</H3></i></p>'
SET @strMensaje = @strMensaje + '<p><i><H3>¡Muchas gracias!</H3></i></p>'
SET @strMensaje = @strMensaje + '<p>&nbsp;</p>'
SET @strMensaje = @strMensaje + '<p><b><H4>Equipo de Selección RRHH(FIRMA)<BR>EMPRESA BLABLA Inc.<BR>Santa Ana 2058, 2do. Piso, CP X5000ACE - Córdoba, Argentina</H4></b></p>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'perfilMail1',
@recipients = 'pepe@pepe.com',
@body = @strMensaje ,
@body_format = 'HTML',
@subject = 'Prueba Mail HTML'

Y este es el resultado:

De: NO REPLY
Enviado el: Jueves, 14 de Julio de 2011 12:01 p.m.
Para: pepe@pepe.com
Asunto: Prueba Mail HTML

El motivo de este correo es informarte que ha sido seleccionado para segunda entrevista.

Esperamos se comunique a la brevedad.

¡Muchas gracias!

Equipo de Selección RRHH(FIRMA)
EMPRESA BLABLA Inc.
Santa Ana 2058, 2do. Piso, CP X5000ACE - Córdoba, Argentina

fuente: http://msdn.microsoft.com/es-es/library/ms190307.aspx

Obtener cuanto ocupa una tabla y cada uno de los indices de la misma

Por medio de este script podemos obtener el espacio en disco  que ocupa una tabla y el detalle del espacio ocupado por cada uno de los indices de dicha tabla:

RESULTADO:
name      rows            reserved               data                     index_size             unused
-------     -----------          ------------------     ------------------       ----------------            ------------------
Client     10580220    2809928 KB         1408328 KB         1384496 KB         17104 KB

IndexName                                   IndexSizeKB
-------------------------------             --------------------
IX_ForDataView                              933944
IX_tuning_id_include_username        445144
PK_Client                                     1413736


SCRIPT:   (en  la variable  @strNombreTabla se indica la tabla a analizar)
DECLARE @strNombreTabla VARCHAR(50)
SET @strNombreTabla = 'Client'


EXEC sp_spaceused @strNombreTabla
SELECT i.name AS IndexName, SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
WHERE s.[object_id] = object_id(@strNombreTabla )
GROUP BY i.name ORDER BY i.name

Función Recursiva SPLIT , transformar una cadena de texto separada por un caracter (ej coma) en una tabla

Por medio de la sig. función (del tipo recursiva) podemos transformar una cadena de texto en tabla, ej:


Declare @stringtest as varchar(8000)

set @stringtest ='1,2,3,4,5,6,7,8,9'

Select * from [dbo].[Split_returns_Table](@stringtest,',')

RESULTADO:

Id
--
1
2
3
4
5
6
7
8
9

(9 row(s) affected)

FUNCION:

CREATE FUNCTION [dbo].[Split_returns_Table]  

(  

  @Id        varchar(8000),  

  @Separador varchar(8)   --= ','  

)  

RETURNS TABLE  

AS  

RETURN  

(  

  WITH SplitETC (Inicio, Fin)  

    AS  

  (  

       SELECT 1,  

              CHARINDEX(@Separador, @Id)  

    UNION ALL  

       SELECT Fin + LEN(@Separador),  

              CHARINDEX(@Separador, @Id, Fin + LEN(@Separador))  

         FROM SplitETC  

        WHERE Fin > 0  

  )  

  SELECT RTRIM(LTRIM(SUBSTRING(@Id, Inicio, Largo))) Id  

    FROM (SELECT Inicio,  

                 CASE WHEN Fin IS NULL OR Fin = 0 THEN  

                     LEN(@Id) + 1  

                 ELSE  

                     Fin  

                 END  - Inicio  AS Largo  

            FROM SplitETC /*OPTION (MAXRECURSION 0) */) AS Split  

)

Función SQL para quitar Acentos (y pasa a mayusculas)

Por medio de la sig función podemos usarla para que le quite los acentos a cadenas de caracteres.

Uso:
Select dbo.replaceAccentCharAndEnieUpper('123ÁáéíóúñÑ456')

Resultado:
123AAEIOUNN456

Función:


CREATE FUNCTION replaceAccentCharAndEnieUpper (@source as varchar(255))
RETURNS varchar(255) AS
BEGIN

declare @charList as varchar(20)
declare @temp as varchar(255)
declare @i as int

set @temp = @source
set @charList = 'aeiou'

set @i = 0
while @i <= len(@charList)
begin
set @temp = replace(@temp,  substring(@charList, @i, 1)  ,  substring(@charList, @i, 1)  )
set @i = @i + 1
end
set @temp = Replace(@temp, '’', '''')
set @temp = Replace(@temp, 'ñ', 'n')

set @temp = Upper(@temp)

return @temp

END

Fuente:

http://us.generation-nt.com/answer/removing-accents-doing-accent-insensitive-processing-help-151335981.html

Consulta para analizar el uso de Indices en una tabla, determinar índices no usados

Cuando en una tabla con muchas filas tenemos varios índices sin usar nos genera dos grandes problemas , uno el  espacio desperdiciado por los índices que no se usan y el otro y tal vez mas importante es el consumo que generan estos índices cuando se le hace un Insert o un Update en la tabla.

Por medio de esta consulta podemos corroborar la cantidad de usos que se le dieron a los índices de cierta tabla.

Se debe prestar atención a las columnas user_seeks,  user_scans,  user_lookups, las mismas indican la cantidad de veces que se usaron desde el ultimo reinicio del servidor, si las tres columnas están en 0 se podría borrar el índice ya que esta consumiendo recursos y no se está usando.

(Mas info http://msdn.microsoft.com/en-us/library/ms188755.aspx)

Remplazar 'empleado' por el nombre de la tabla a analizar:


Declare @table as nvarchar(200)

Set @table='empleado'

SELECT i.name,s.*
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id AND i.index_id=s.index_id AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1
and object_name(i.object_id) =@table
order by user_seeks,user_scans,user_lookups

Procedimiento Almacenado para buscar texto en todas las tablas y en todas las columnas

Por medio de este sp se puede buscar una cadena en toda una base, se busca en todas las tablas y en todas las columnas:


exec ToolsSearchAllTablesAllColumns 'Ezequiel'

ColumnName                                             ColumnValue
-----------------------------------------                      ---------------------------------
[dbo].[TABLA_A].[Apellido y Nombre]        Gonzalez, Ezequiel
[dbo].[TABLA_B].[apeynom]                      LIJTINSTENS, EZEQUIEL
[dbo].[TABLA_C].[cysfirdes]                      LEG 98488 AVA EZEQUIEL



CREATE PROC [dbo].[ToolsSearchAllTablesAllColumns]

@SearchStr nvarchar(100)

AS
BEGIN
-- Purpose: To search all columns in all tables for a given search string
-- Written by: Francisco Tapia
-- Site: http://sqlthis.blogspot.com
-- Inspired by: Narayana Vyas Kondreddi http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
-- Tested on: SQL Server 7.0, 2000 and 2005
-- Date modified: June 23, 2009

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

SELECT @TableName = '', @ColumnName = ''
SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

WHILE (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName = (
SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '|' + QUOTENAME(C.Column_name))
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND C.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext')
AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName

)

SET @Parse = PATINDEX ('%|%', @ColumnName)

SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING(' + @ColumnName + ',1, 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
)
END
END


SELECT ColumnName, ColumnValue FROM #Results
ORDER BY ColumnName


END

GO

SQL Función para obtener antiguedad o Seniority

Por medio de esta función pueden obtener la antiguedad , por ejemplo en un trabajo:

Select dbo.ToolSeniority('20090701','20110111')

RESULTADO:

-------------------------
1 Año 6 Meses 10 Días

Usa dos funciones: (que fui encontrando y adaptando a las necesidades puntuales  del formato de salida)

CREATE FUNCTION dbo.DateDiffPoda

(

@type char(2),

        @FromDate DATETIME,

        @ToDate DATETIME

)

RETURNS INT

BEGIN

        RETURN  

   CASE

               WHEN @FromDate > @ToDate THEN NULL --Filtra fecha invertida

ELSE

  Case

  When upper(@type) not in ('YY','MM','DD') THEN NULL  

  WHEN upper(@type)='YY' THEN       --AÑO

CASE 

  WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1

  ELSE DATEDIFF(month, @FromDate, @ToDate)

END / 12

  WHEN upper(@type)='MM' THEN       --MES

CASE

  WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1

  ELSE DATEDIFF(month, @FromDate, @ToDate)

END

  WHEN upper(@type)='DD' THEN       --MES

CASE

  WHEN convert(nvarchar,@FromDate,108) > convert(nvarchar,@ToDate,108)  THEN DATEDIFF(dd, @FromDate, @ToDate) - 1

  ELSE DATEDIFF(dd, @FromDate, @ToDate)

END

end

END

END

GO

CREATE function ToolSeniority

(

        @FechaIngreso DATETIME,

        @FechaActual DATETIME

)

RETURNS varchar(25)

--------------------

--Ezequiel Podadera

--22/02/2010

--------------------

Begin

Declare @FechaAñoActual datetime  --Fecha que tiene el mismo año que fecha actual (para calculo interno)

Declare @FechaAñoActualMES datetime --Fecha que tiene el mismo año y mes que fecha actual (para calcular diff días)

Declare @DiffAño int

Declare @DiffMes int 

Declare @DiffDia int 

--set @FechaIngreso=getdate()-1549  DATOS DE PRUEBA

--set @FechaActual=getdate()

SET @DiffAño = dbo.datediffPoda('yy',@FechaIngreso,@FechaActual) 

-- FechaAñoActual se posiciona a fecha sin diff en años, para calcular diff en mes

SET @FechaAñoActual = Dateadd(yy, @DiffAño,@FechaIngreso) 

SET @DiffMes = dbo.datediffPoda('mm',@FechaAñoActual,@FechaActual)

-- FechaAñoActualMes se posiciona a fecha sin diff en años-meses, para calcular diff en días

SET @FechaAñoActualMES = DateADD(mm,@DiffMes,@FechaAñoActual)  

SET @DiffDia = dbo.datediffPoda('dd', @FechaAñoActualMES,@FechaActual)

--Formatea la salida

Declare @sAño varchar(8)

Declare @sMes varchar(9)

Declare @sDia varchar(8)

Declare @sSalida varchar(25)

set @sAño=        case when @DiffAño = 0   then ''

  when @DiffAño = 1   then Cast(@DiffAño as varchar) + ' Año '

  when @DiffAño > 1   then Cast(@DiffAño as varchar) + ' Años '

end

set @sMes=        case when @DiffMes = 0   then ''

  when @DiffMes = 1   then Cast(@DiffMes as varchar) + ' Mes '

  when @DiffMes > 1   then Cast(@DiffMes as varchar) + ' Meses '

end

set @sDia=      case when @DiffDia = 0   then ''

  when @DiffDia = 1   then Cast(@DiffDia as varchar) + ' Día '

  when @DiffDia > 1   then Cast(@DiffDia as varchar) + ' Días '

end

set @sSalida= Case when @sAño + @sMes + @sDia ='' then 'Sin Antiguedad' else @sAño + @sMes + @sDia end

Return @sSalida

END

GO