Mantenimiento de Indices

FRAGMENTACION DE INDICES SQL SERVER

A - Conceptos Generales y Tipos de Fragmentación


Los índices son los únicos objetos que pierden su efectividad con el paso del tiempo si no se le da un mantenimiento adecuado.

La fragmentación de los índices tiene lugar cuando se modifican los registros de una tabla por inserción, update o borrado de datos y estas modificaciones afectan una o más páginas del índice.

Hay dos tipos de fragmentación a nivel de índice, ambas afectan directamente la performance de los procesos que utilizan los mimos, veamos:

A- Fragmentación Interna: es el tipo de fragmentación que tiene lugar cuando se lleva a cabo una operación de borrado. El borrado de datos genera liberación de espacio en las páginas de índice, lo que produce que sólo una parte de la página del índice esté ocupada. Esto provoca que el SQL Server con el tiempo tenga que leer más páginas de índice que las necesarias, por el mal aprovechamiento que implica tener páginas de índice semi vacías.

B- Fragmentación Externa: cuando llevamos a cabo un insert y la página no puede almacenarse, el SQL Server genera una nueva página para alojar los datos insertados, conservando un orden lógico, pero no un orden físico en las páginas de índice, a esto se lo conoce como “page split” . Cuando el SQL Server hace un page split está generando fragmentación externa.


B - Detección y Evaluación de la Fragmentación


A partir del Sel Server 2005 fue reemplazada la utilidad DBCC SHOWCONTIG por una vista del sistema llamadaa SYS.DM_DB_INDEX_PHYSICAL_STATS , la cual arroja datos más certeros y consume menos recursos del sistema en su ejecución.

Veamos pues un ejemplo mediante el cual se relevan aquellos índices que cuentan con más de 1000 páginas y un porcentaje de fragmentación superior al % 20.

SELECT

b.name as 'database',

c.name as 'name',

index_type_desc,

avg_fragmentation_in_percent,

page_count

FROM sys.dm_db_index_physical_stats (9, NULL, NULL, NULL, 'limited')AS A

LEFT JOIN SYS.DATABASES B

ON A.DATABASE_iD = B.DATABASE_ID

LEFT JOIN SYS.OBJECTS C

ON A.OBJECT_iD = C.OBJECT_iD

WHERE avg_fragmentation_in_percent > 20 -- buscando % de fragmentación en este caso > 20

and index_level = 0 --> analizando la rama principal de los índices

and page_count > 1000 --> mas de 1000 hojas

order by name

Vamos a hacer un paréntesis aquí y veamos lo que Microsoft nos dice en torno a los niveles de fragmentación:

A- “ Fragmentación Inócua”: Microsoft dice que no debiéramos estar preocupados por la eventual fragmentación de índices que contienen menos de 1000 páginas. Este es todo un parámetro a tener en cuenta. También nos habla de no tomar en cuenta la fragmentación existente sobre tablas pequeñas ya que las mismas no tienen impacto sobre la performance de las queries y son muy difícil de ser eliminadas por rutinas de reorganizado o reconstrucción de índices.

B- “Fragmentación Baja”: es aquella que no supera el % 5 del índice. La recomendación es no intentar eliminar la misma, pues puede ser mayor el costo de hacerlo que los beneficios obtenidos como consecuencia de ello.

C- “Fragmentación Media”: es la fragmentación > % 5 y < = al %30.: Microsoft recomienda hacer un Reorganize de los índices.

D- “Fragmentación Alta”: es la fragmentación > al % 30: Microsoft recomienda hacer un Rebuild de los índices.


C - Métodos de Desfragmentación


Hay varios métodos para desfragmentar índices, es importante elegir el método adecuado acorde al entorno de aplicación del mismo.


1) Alter Index Reorganize


• Se recomienda usar este método cuando el % de fragmentación es de > % 5 y < = al %30.:
• Se utiliza la instrucción ALTER INDEX con la claúsula REORGANIZE. Esta instrucción reemplaza a la vieja DBCC INDEXDEFRAG
• La reorganización se realiza en línea ya que no mantiene grandes bloqueos
• Este proceso utiliza una mínima cantidad de recursos del sistema
• Básicamente se desfragmentan los índices, se compactan las páginas vacías acordes al valor de fill factor y reordenan la páginas de índices a nivel físico, para que coincidan con el ordenamiento a nivel lógico.
• Para reorganizar las páginas de un índice particionado en una de las particiones, se be utilizar la cláusula PARTITION
• El reorganizar un índice No regenera las estadísticas.

Alter Index "Nombre_Indice" on "Nombre_Tabla"

Reorganize;

  2) Alter Index Rebuild / Create Index with Drop_Existing = ‘on’


• Se regenera el índice en su totalidad respetando el fill factor y, se hace un update de las estadísticas
• Es una operación que demanda muchos recursos del sistema puesto que el motor de base de datos requiere el doble de espacio del que ocupa el índice para crear primero el índice nuevo y luego borrar el viejo. Además se toma un espacio adicional para hacer esta operación en el disco salvo que se le indique hacer el ordenamiento en la TempDb
• Se usa sólo para reconstruir índices cuyo porcentaje de fragmentación esté por sobre el % 30
• Puede ser realizada en línea excepto que el índice esté sobre columnas de tipo LOB (image, text, nvarchar, xml, varchar(max)), o que sean índices XML
• Se puede hacer un Rebuild de un índice con la cláusula PARTITION, sólo en el caso del método Alter Index

Alter Index "Nombre_Indice" on "Nombre_Tabla"

Rebuild;

Create Index "Nombre_Indice" on "Nombre_Tabla(nombre campo)"

With Drop

3) Disabling Indexes


• Es el método de reconstrucción total de un índice que menos recursos consume.
• Al deshabilitar un índice se impide que el usuario tenga acceso al mismo y a las tablas subyacentes. Esto hace que solo pueda ser usado en ambientes que permitan un downtime para llevar a cabo la operación.
• No requiere a diferencia de la sentencia Rebuild del doble de espacio en disco que usa el índice, puesto que la definición del índice se conserva en los metadatos eliminándose físicamente el mismo. (para ello deben deshabilitarse todos los índices clustered y luego, en otra transacción, se debe implementar un Rebuild de todos los índices)

Alter Index "Nombre_Indice" Clustered on "Nombre_Tabla"

DISABLE;

Alter Index ALL on "Nombre_Tabla"

REBUILD with (fillfactor = xx, sort in TempDb = On);



4) Cuándo los Indices Non-Clustered se reconstruyen automáticamente?


• Desde un Heap a un Cluster: Se recontruyen los índices Non Clustered

• Desde un Cluster a un Heap: Se reconsruyen los índices Non Clustered

• Rebuilding un Unique Cluster Index : No se reconstruyen los indices Non Clustered

• Rebuilding un No Unique Cluster Index –

4.1) Hasta sql server 2000 se reconstruyen los índices non clustered
4.2) Desde sql Server 2005 ya no se reconstruyen los índices nonclustered

En Qué Filegroup se Localiza Cada Tabla ?

Amigos,

Quiero dejarles un script muy útil. El mismo sirve para obtener un recordset mediante el cual podrán tener un lista de todas las tablas correspondientes a una Base de Datos y su correspondiente FileGroup de alocación. Espero que les sea útil. Saludos.
------------------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos las tablas de una BD y su correspondiente Filegroup --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
------------------------------------------------------------------------------------------------------------------------------
SELECT
o.[name] AS 'TABLE',
f.[name] AS 'FILE_GROUP'
FROM sys.indexes i
inner JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o
ON i.[object_id] = o.[object_id]
WHERE
o.type = 'U' and
i.type < 2
Order by O.name
GO

Combinando Vistas del Sistema - Usando Metadata

Amigos,

Muchas veces solemos ahogarnos en un baso de agua al no utilizar todo el potencial que las vistas de catalogo del Sql Server gentilmente nos ofrece.

Aquí tiene un ejemplo de cómo listar cada uno de los índices de Base de Datos, mendiante la combinación de vistas del sistema.

Por favor, no duden en preguntar ante cualquier duda.

Con uds. es script.
---------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos los índices de una Base de Datos --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

SELECT
o.[name] AS 'Table Name',
i.[name] as 'Index Name',
i.[type_desc]'Description',
f.[name]AS 'Filegroup',
c.[name] as 'Fields'
FROM sys.indexes i
inner JOIN sys.filegroups f -- para saber el filegroup
ON i.data_space_id = f.data_space_id
INNER JOIN sys.objects o -- para nombre de la tabla
ON i.[object_id] = o.[object_id]
inner join Sys.Index_Columns as z --
on z.[object_id] = o.[object_id]
INNER JOIN sys.columns c
on c.[object_id] = z.[object_id] and
c.Column_ID = z.Column_ID
WHERE
o.type = 'U'
and i.[type_desc] <> 'HEAP'
and i.[type_desc] <> 'CLUSTERED' and
i.index_Id = z.index_id
order by o.name,
i.name
GO

Listar Todos Los Indices Existentes en una Base de Datos con un Solo Script

Amigos,

Hay muchas maneras de tener una mirada de los índices de una tabla, pero ninguna de ellas permite facilmente listar Todos los Indices de una Base de Datos en un sólo paso.

Ejemplos:

- Usando el Enterprise Manager.., sólo podremos obtener la visión de una tabla y un índice al mismo tiempo

- Usando el SP_Helpindex..., que sólo nos permite listar todos los índices de a una tabla por vez..

- Utilizando la tabla sysindixes, con lo cual tendremos que escribir complejas sentencias para lograr algo útil.

Pero no desesperen, aquí les traigo la solución. Espero les sea de utilidad, Saludos.
---------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script lista todos los índices de una Base de Datos --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

drop table #spindtab
set nocount on
declare @objname nvarchar(776), -- si quiero puedo ingresar el nombre de la tabla por parámetro
@objid int,
@indid smallint,
@groupid smallint,
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126),
@dbname sysname,
@usrname sysname

-- Chequear para asegurarse que el nombre de la tabla entrado por parámetro pertenezca a la base de datos --
select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)

end


-- Creación Tabla Temporal
create table #spindtab
(
usr_name sysname null,
table_name sysname null,
index_name sysname collate database_default null,
stats int null,
groupname sysname collate database_default null ,
index_keys nvarchar(2126) collate database_default null -- see @keys above for length descr
)


-- Se Guarda en un Curso el Id, Nombre de de la Tabla y Owner
declare ms_crs_tab cursor local static for
select
sysobjects.id,
sysobjects.name,
sysusers.name
from sysobjects
inner join sysusers
on sysobjects.uid = sysusers.uid
where type = 'U'

open ms_crs_tab
fetch ms_crs_tab
into @objid, @objname, @usrname

while @@fetch_status >= 0
Begin

-- Se consulta la tabla de índices.
declare ms_crs_ind cursor local static for
select
indid,
groupid,
name,
status
from sysindexes
where id = @objid and
indid between 1 and 254 and
(status & 64)=0
order by indid
open ms_crs_ind
fetch ms_crs_ind into
@indid,
@groupid,
@indname,
@status

-- Ahora se chequea cada índice, comprendiendo el tipo y el campo que utiliza, guardando la info
-- en una tabla temporal que será impresa al final

while @@fetch_status >= 0
begin
-- Primer vamos a entender cuales son las columnas involucradas
declare
@i int,
@thiskey nvarchar(131)

select @keys = index_col(@usrname + '.' + @objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys + '(-)'

select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@usrname + '.' + @objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end

select @groupname = groupname from sysfilegroups where groupid = @groupid

-- Insertar una columna para el índice relevado
insert into #spindtab values (@usrname, @objname, @indname, @status, @groupname, @keys)

-- Vamos por el próximo índice
fetch ms_crs_ind into @indid, @groupid, @indname, @status
end
deallocate ms_crs_ind

-- Vamos a buscar otra tabla
fetch ms_crs_tab into @objid, @objname, @usrname
end
deallocate ms_crs_tab

-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @empty varchar(1) select @empty = ''
declare @des1 varchar(35),
@des2 varchar(35),
@des4 varchar(35),
@des32 varchar(35),
@des64 varchar(35),
@des2048 varchar(35),
@des4096 varchar(35),
@des8388608 varchar(35),
@des16777216 varchar(35)
select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1
select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2
select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4
select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32
select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64
select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048
select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096
select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608
select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216

-- DISPLAY THE RESULTS
select
'usr_name'=usr_name,
'table_name'=table_name,
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@des1 else @empty end
+ case when (stats & 2)<>0 then ', '+@des2 else @empty end
+ case when (stats & 4)<>0 then ', '+@des4 else @empty end
+ case when (stats & 64)<>0 then ', '+@des64 else case when (stats & 32)<>0 then ', '+@des32 else @empty end end
+ case when (stats & 2048)<>0 then ', '+@des2048 else @empty end
+ case when (stats & 4096)<>0 then ', '+@des4096 else @empty end
+ case when (stats & 8388608)<>0 then ', '+@des8388608 else @empty end
+ case when (stats & 16777216)<>0 then ', '+@des16777216 else @empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by table_name, index_name

GO

drop table #spindtab

Deshabilitar/Habilitar todos los Foreign Key de una Base de Datos

Amigos,

Como uds saben ante operaciones tales como el borrado de datos de tablas referenciadas por FK, es necesario desahbilitar los mismos, para luego, una vez borrada la data, volver a habilitarlos.

Les dejo aquí un script que permite automatizar la tarea, espero que les sea de utilidad.

--------------------------------------------------------------------------------------------------------------------
-- Detalle: Este script permite deshabilitar/habilitar todos los FK de de una BD --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
---------------------------------------------------------------------------------------------------------------------

DECLARE
@TableName varchar(255),
@sql varchar(4000)

DECLARE cTable CURSOR FORWARD_ONLY FOR

-- Obtengo los nombres de las tablas de las bases de datos.
SELECT name
FROM sys.objects
WHERE
type = 'U'

OPEN cTable
FETCH NEXT FROM cTable
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
-- Para deshabilitar
SET @sql = 'alter table [' + @TableName + '] nocheck constraint all'

-- Para volver a habilitar
SET @sql = 'alter table ' + @TableName + ' check constraint all '

EXEC (@sql)

FETCH NEXT FROM cTable INTO @TableName
END

CLOSE cTable
DEALLOCATE cTable

Conocer Todos los Foreign Key de una Base de Datos.

Amigos,

Les dejo un script que les permitirá conocer todos los Foreign Key de todas las tablas de una Base de Datos.

Es un script muy útil a la hora de encarar un proceso de migración, borrado, update, inserción de datos etc en cualquier tabla.

La idea es no tener que recorrer por consola una a una las tablas y poder solucionar esto rápidamente.

Va el script:

-------------------------------------------------------------------------------------
-- Detalle: Este script permite listar todos los FK de todas las tablas de una BD --
-- Gustavo Herrera para Sql Server Tips - http://gherrerasqlserver.blogspot.com/ --
-- ----------------------------------------------------------------------------------

SELECT
Origin_Table = FK.TABLE_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
FK_Column = CU.COLUMN_NAME,
Reference_Table = PK.TABLE_NAME,
Reference_Column = PT.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
ORDER BY
origin_table

Borrar Registros Duplicados (dejando sólo los registros válidos)

Buen dia amigos,

Les traigo un script utilísimo si han tenido la mala suerte de, involuntariamente, duplicar, triplicar, cuadruplicar etc registros de una tabla...
Con la ayuda de mi script en unas pocas línes de código podrán volver la tabla a su estado anterior, es decir, podrá eliminar sólo los registros duplicados, conservando los registros válidos. Manos a la obra..

--------------------------------------------------------------
-- Detalle: Este script permite borrar registros duplicados --
-- Autor: Gustavo Herrera --
--------------------------------------------------------------

-- 1) Agrego un campo "id" identity (si no lo tiene)
Alter Table TablaX
add id integer identity

-- 2) Borro registros duplicados
Delete from TablaX
where Id > (Select min(Id) From TablaX as b Where TablaX.Idmsg = b.Idmsg)

-- 3) Dropeo el campo creado para correr el paso 2
Alter Table TablaX
Drop Column id

Histórico de Ejecución de un SP

Amigos,

Les traigo del botiquín otro script interesante cuando desean estudiar el histórico de ejecución de un job. Simplemente completen el script con el nombre del job deseado y listo. Ya lo saben.. nada de nada perdiendo el tiempo con el "view history" ;). Saludos.


---------------------------------------------------------
-- Autor: Gustavo Herrera --
-- Listar histórico tiempo de ejecución de un job --
---------------------------------------------------------

select job_name, run_datetime, run_duration
from
(
select job_name, run_datetime,
SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
SUBSTRING(run_duration, 5, 2) AS run_duration
from
(
select DISTINCT
j.name as job_name,
run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +
(run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
from msdb..sysjobhistory h
inner join msdb..sysjobs j
on h.job_id = j.job_id
) t
) t
where job_name = 'Nombre del Job'
order by run_datetime

Activity Monitor ? no, no.. mejor vista del Sistema Sys.Sysprocesses

Amigos,

Cuántas veces se han sentido frustrados ante la imposibilidad de poder estudiar "por consola" cuáles son los procesos que están utilizando recursos de Sql Server mediante la poco efectiva herramienta Activity Monitor? Apuesto a que muchas veces que han intentado utilizar esta herramienta en ocasiones en las cuales el Sql está siendo fuertemente impactado, se han topado con un desalentado "Time Out"

Pues bien, a no remar contra la corriente... Bill Gates pensó en nostros y nos da una vista del Sistema llamada Sys.Sysprocesses, la cual nos permite obtener los mismos resultados que el Activity Monitor, sin Time Outs de por medio.

A utlizarla entonces, va un ejemplo.


-- Sys.sysprocesses --
select
spid,
blocked, -- solo valor si está bloqueada
waittime, -- 0 = process its not waiting -
lastwaittype, -- Description of last waiting
dbid, -- Data base id used by the process
cpu, -- Cumulative cpu time for the process
physical_io, -- Cumulative disk reads and writes
memusage, -- Number of page in the cache allocated by the process
Login_time, -- Time in which a process begin to log
last_batch, -- Time in wich a last process has ocurred
open_tran, -- Number of open transactions for the process
status, -- "dormant" -- is being resetting the session --
-- "running" -- is running one or more batches --
-- "background" -- running background process such as rollback process --
-- "pending" -- waiting for thread to continue
-- "runnable" -- the task is in the runnable queue --
-- "suspended" -- waiting for an event to complete --
sid, -- user identificator
hostname, -- name of the workstation
cmd, -- command that is being executed --
nt_username -- user name for the process
from sys.sysprocesses


-- En caso de quere5 "asesinar" un proceso --
KILL SPID;

Listar Todos Los Jobs de una Base de Datos

Amigos,
Les dejo un script que permite listar todos los jobs correspondientes a un servidor de Bases de Datos.
Creo que el script es realmente interesante. Ojalá así les resulte.
Hasta la próxima.


------------------------------------------------
-- Autor: Gustavo Herrera (Mayo 2011 --
-- Detalle: Lista Todos los Jobs de un Server --
-----------------------------------------------
select
'Server' = left(@@ServerName,20),
'JobName' = S.name,--left(S.name,90),
'ScheduleName' = left(ss.name,25),
'Enabled' = CASE (S.enabled)
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE '??'
END,
'Frequency' = CASE(ss.freq_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks' else 'Weekly' end)
WHEN 16 THEN
(case when (ss.freq_recurrence_factor > 1)
then 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
WHEN 64 THEN 'SQL Startup'
WHEN 128 THEN 'SQL Idle'
ELSE '??'
END,
'Interval' = CASE
WHEN (freq_type = 1) then 'One time only'
WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
from (select ss.schedule_id,
freq_interval,
'D1' = CASE WHEN (freq_interval & 1 <> 0) then 'Sun ' ELSE '' END,
'D2' = CASE WHEN (freq_interval & 2 <> 0) then 'Mon ' ELSE '' END,
'D3' = CASE WHEN (freq_interval & 4 <> 0) then 'Tue ' ELSE '' END,
'D4' = CASE WHEN (freq_interval & 8 <> 0) then 'Wed ' ELSE '' END,
'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu ' ELSE '' END,
'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri ' ELSE '' END,
'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat ' ELSE '' END
from msdb..sysschedules ss
where freq_type = 8
) as F
where schedule_id = sj.schedule_id
)
WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
WHEN (freq_type = 32) then (select freq_rel + WDAY
from (select ss.schedule_id,
'freq_rel' = CASE(freq_relative_interval)
WHEN 1 then 'First'
WHEN 2 then 'Second'
WHEN 4 then 'Third'
WHEN 8 then 'Fourth'
WHEN 16 then 'Last'
ELSE '??'
END,
'WDAY' = CASE (freq_interval)
WHEN 1 then ' Sun'
WHEN 2 then ' Mon'
WHEN 3 then ' Tue'
WHEN 4 then ' Wed'
WHEN 5 then ' Thu'
WHEN 6 then ' Fri'
WHEN 7 then ' Sat'
WHEN 8 then ' Day'
WHEN 9 then ' Weekday'
WHEN 10 then ' Weekend'
ELSE '??'
END
from msdb..sysschedules ss
where ss.freq_type = 32
) as WS
where WS.schedule_id =ss.schedule_id
)
END,
'Time' = CASE (freq_subday_type)
WHEN 1 then left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
ELSE '??'
END,

'Next Run Time' = CASE SJ.next_run_date
WHEN 0 THEN cast('n/a' as char(10))
ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120) + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
END

from msdb.dbo.sysjobschedules SJ
join msdb.dbo.sysjobs S on S.job_id = SJ.job_id
join msdb.dbo.sysschedules SS on ss.schedule_id = sj.schedule_id
order by S.name

Buscar String en Jobs de una Base de Datos

Quién no ha pasado alguna vez por la necesidad de buscar un string dentro de N cantidad de Store Procedures correspondientes a una Base de Datos?

La tarea es por demás tediosa si se la encara "manualmente". Uds saben, no es muy práctico abrir cada sp y buscar el string presionando "f3".

Pues bien, les dejo un store procedure, el cual hace este trabajo de modo automático. Simplemente tienen que generarlo y luego ejecutarlo con dos parámetros, string a buscar y nombre de la base de datos.

Espero que sea de utilidad, saludos.

---------------------------------------------------------------------------
-- OBJECT NAME: p_FindText
-- DATE: 01/04/2011
-- INPUTS:
-- @StrFind -> Cadena a buscar
-- @VarDBName -> DB en la que se buscará el string
-- OUTPUTS: Nombres de SP que contienen la cadena buscada
-- DESCRIPTION:
/*
El método consta de utilizar una SP que busca entre las tablas de sistema
de una determinada DB, utilizando un LIKE contra el campo dónde el motor
guarda el texto de los SP. Es importante destacar que, obviamente, esto no
funciona con los SP encriptados. */
-----------------------------------------------------------------------------

CREATE PROCEDURE dbo.p_FindText
@strFind varchar (100),
@varDBName varchar (100)
as
BEGIN
declare @varQuery varchar (1000)
select @varQuery =
'SELECT distinct ' +
'name SP_Name, ''sp_helptext '''''' + name + ''''''''SP_HT ' +
'FROM [' + @varDBName + '].[dbo].[sysobjects] inner join [' + @varDBName + '].[dbo].[syscomments] ' +
'on [' + @varDBName + '].[dbo].[sysobjects].id = [' + @varDBName + '].[dbo].[syscomments].id ' +
'where xtype = ''P'' ' +
'and text like ''%' + @strFind + '%'' ' +
'order by name '
exec (@varQuery)
END


----------- Ejecutar el sp p_FindText ------------
DECLARE
@strFind varchar(100),
@varDBName varchar(100)

SELECT
@strFind = 'string a buscar',
@varDBName = 'Base de Datos'

EXEC p_FindText @strFind, @varDBName