miércoles, 23 de marzo de 2011

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:

jueves, 10 de marzo de 2011

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.


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

miércoles, 9 de marzo de 2011

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




miércoles, 2 de marzo de 2011

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