Xianto
04-06-2005, 20:13:13
He aquí un listado de procedimientos que en los ultimos años he hecho para mi trabajo y me han sido muy útiles...
Tal vez alguno pueda usarlos o poner más !!!
Reindexar todas las tablas de una db, esto es muy distinto a como lo hace el mantenimiento del MSSQL, quedan mucho mejor...
CREATE PROCEDURE xreindex AS
SET NOCOUNT ON
DECLARE @Table char(100)
DECLARE IndexCursor CURSOR FOR SELECT name FROM sysobjects WHERE type='U'
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Table
WHILE @@FETCH_STATUS=0 BEGIN
DBCC DBREINDEX(@Table)
FETCH NEXT FROM IndexCursor INTO @Table
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO
Muestra la fragmentación de una tabla a eleccion o todas, muy util para ver si es necesario una reindexacion o desfragmentacion...
CREATE PROCEDURE xfragmenta
@Tabla varchar(100)='1021'
AS
IF @Tabla = '1021'
BEGIN
PRINT 'Analizando fragmentación de todas las tablas...'
PRINT '============================================'
PRINT ' '
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
END
ELSE
BEGIN
PRINT 'Analizando fragmentación tabla: '+@Tabla (%27+@Tabla)
PRINT '=============================='
PRINT ' '
DBCC SHOWCONTIG (@Tabla)
END
GO
Defragmenta las tablas de una db con una fragmentacion superior a 30 puntos o lo que nosotros le digamos
CREATE PROCEDURE xdefrag
@maxfrag DECIMAL=30.0
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
OPEN indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Ejecutando DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentación actual '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
CLOSE indexes
DEALLOCATE indexes
GO
Procedimiento super util, ejecutas: "xbusca_campo '%sales%'" y te dice todas las tablas donde existe este campo, y su correspondiende formato... se pueden poner o no los comodines %.
CREATE PROCEDURE xbusca_campo
@campo varchar(50)
AS
select so.name as tabla, sy.name as campo,
case when sy.isnullable=0 then 'NO' else 'SI' end as nulls,
sy.length tamaño,
st.name as tipo, sc.text as defecto
from sysobjects so (nolock)
inner join syscolumns sy (nolock) on
so.id=sy.id and so.type='U' and sy.name like @campo
inner join systypes st (nolock) on
sy.xusertype=st.xusertype
left join syscomments sc (nolock) on
sy.cdefault=sc.id
order by so.name
GO
Cuando tienes muchos procedimientos y no sabes donde buscar algo, este procedimiento es lo mejor, le pasas como parametro una cadena con o sin comodines (%) y te dice los procedimientos donde esta el texto que buscas.
CREATE PROCEDURE xbusca_text_proc
@cadena_buscada varchar(100)
AS
set @cadena_buscada='%'+@cadena_buscada+'%'
select distinct so.name from sysobjects so (nolock)
inner join syscomments sc (nolock) on
so.id = sc.id and
so.type='P' and sc.text like @cadena_buscada
order by so.name
GO
Bue, espero que le sea util a alguien.... Saludetes !!!
Tal vez alguno pueda usarlos o poner más !!!
Reindexar todas las tablas de una db, esto es muy distinto a como lo hace el mantenimiento del MSSQL, quedan mucho mejor...
CREATE PROCEDURE xreindex AS
SET NOCOUNT ON
DECLARE @Table char(100)
DECLARE IndexCursor CURSOR FOR SELECT name FROM sysobjects WHERE type='U'
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @Table
WHILE @@FETCH_STATUS=0 BEGIN
DBCC DBREINDEX(@Table)
FETCH NEXT FROM IndexCursor INTO @Table
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO
Muestra la fragmentación de una tabla a eleccion o todas, muy util para ver si es necesario una reindexacion o desfragmentacion...
CREATE PROCEDURE xfragmenta
@Tabla varchar(100)='1021'
AS
IF @Tabla = '1021'
BEGIN
PRINT 'Analizando fragmentación de todas las tablas...'
PRINT '============================================'
PRINT ' '
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
END
ELSE
BEGIN
PRINT 'Analizando fragmentación tabla: '+@Tabla (%27+@Tabla)
PRINT '=============================='
PRINT ' '
DBCC SHOWCONTIG (@Tabla)
END
GO
Defragmenta las tablas de una db con una fragmentacion superior a 30 puntos o lo que nosotros le digamos
CREATE PROCEDURE xdefrag
@maxfrag DECIMAL=30.0
AS
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
OPEN tables
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
CLOSE tables
DEALLOCATE tables
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
OPEN indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Ejecutando DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentación actual '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
CLOSE indexes
DEALLOCATE indexes
GO
Procedimiento super util, ejecutas: "xbusca_campo '%sales%'" y te dice todas las tablas donde existe este campo, y su correspondiende formato... se pueden poner o no los comodines %.
CREATE PROCEDURE xbusca_campo
@campo varchar(50)
AS
select so.name as tabla, sy.name as campo,
case when sy.isnullable=0 then 'NO' else 'SI' end as nulls,
sy.length tamaño,
st.name as tipo, sc.text as defecto
from sysobjects so (nolock)
inner join syscolumns sy (nolock) on
so.id=sy.id and so.type='U' and sy.name like @campo
inner join systypes st (nolock) on
sy.xusertype=st.xusertype
left join syscomments sc (nolock) on
sy.cdefault=sc.id
order by so.name
GO
Cuando tienes muchos procedimientos y no sabes donde buscar algo, este procedimiento es lo mejor, le pasas como parametro una cadena con o sin comodines (%) y te dice los procedimientos donde esta el texto que buscas.
CREATE PROCEDURE xbusca_text_proc
@cadena_buscada varchar(100)
AS
set @cadena_buscada='%'+@cadena_buscada+'%'
select distinct so.name from sysobjects so (nolock)
inner join syscomments sc (nolock) on
so.id = sc.id and
so.type='P' and sc.text like @cadena_buscada
order by so.name
GO
Bue, espero que le sea util a alguien.... Saludetes !!!