index deal... does this look right?
-
basically trying to create a quick sp which will either disable,
or rebuild all nonClustered indexes on a table depending upon which
parameters are supplied.
create procedure usp_indexops1
as
declare @tablename varchar (100) --table name obviously
declare @operation varchar (7) --operation is either 'disable' or 'rebuild'
declare @nonclustindex varchar (100) --nonClustered index names
declare MYLOOP cursor
read_only
for
select sysindexes.name from sysindexes join sysobjects on sysindexes.id = sysobjects.id
were indid > 1 and xtype = 'U' and sysobjects.name = @mytable
open cursor
fetch next from MYLOOP into @nonclustindex
while (@@fetch_status = 0)
begin
declare @script varchar(max)
select @script = 'alter index [' + @nonclustindex + '] on [' + @tablename + '] ' + @operation '''
close MYLOOP
deallocaet MYLOOP
end
i think thats pretty much it, but it doesn't look 100% right.
whats missing?
_________________________
-
e.g.set nocount on
declare @OnlineRebuild char(1)
Set @OnlineRebuild = 'Y'
print '-- Begin ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Declare @SQLStmt varchar(max);
declare c1 cursor for
--print 'use [' + db_name() + ']' + char(10) + 'GO'
-- Using a CTE to avoid multiple scans
WITH cteTbWithDisabledIx (TABLE_SCHEMA , TABLE_NAME, INDEX_NAME, INDEX_ID )
AS (
Select T.TABLE_SCHEMA , T.TABLE_NAME, I.[name], I.[index_id]
from INFORMATION_SCHEMA.Tables T with (nolock)
Inner Join sys.indexes I with (nolock)
ON I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 0
where exists ( Select *
from sys.indexes I with (nolock)
where I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 1 )
)
Select 'use [' + db_name() + '] ;
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD '
+ case @OnlineRebuild when 'Y' then ' WITH ( ONLINE = ON )' else '' end + ' ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
Left Join cteTbWithDisabledIx D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and D.TABLE_SCHEMA IS NULL
and T.TABLE_NAME <> 'dtproperties'
Union ALL
Select 'use [' + db_name() + '];
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] IX ' + cast(INDEX_ID as varchar(15)) + ' [' + INDEX_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX [' + INDEX_NAME + '] ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = ON ) ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
inner join cteTbWithDisabledIx D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and INDEX_NAME is not NULL -- Heap niet van toepassing
order by 1 ;
open c1
FETCH NEXT FROM c1 INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
--print @sqlstmt
exec ( @SQLStmt )
FETCH NEXT FROM c1 INTO @SQLStmt
END
-- Cursor afsluiten
CLOSE c1
DEALLOCATE c1
print '-- Usage ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
DBCC UPDATEUSAGE (0) with count_rows ;
print '-- Usage Statistics ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
EXEC sp_updatestats ;
print '-- The end ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
cool thanks.
_________________________
-
I've seen I posted an older version which doesn't take non-online-rebuildable objects into account.
This version is more suitable.
set nocount on
declare @OnlineRebuild char(1)
Set @OnlineRebuild = 'Y'
print '-- Begin ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Declare @SQLStmt varchar(max);
DECLARE @Tellerke BIGINT
SET @Tellerke = 0
declare c1 cursor for
--print 'use [' + db_name() + ']' + char(10) + 'GO'
-- Using a CTE to avoid multiple scans
-- Select Object that cannot use Online-reindexing
-- (disabled indexes, colomns of datatype (n)text, image, (n)varchar(max),.. )
WITH cteExcludedObjects (TABLE_SCHEMA , TABLE_NAME, INDEX_NAME, INDEX_ID )
AS (
Select T.TABLE_SCHEMA , T.TABLE_NAME, I.[name], I.[index_id]
from INFORMATION_SCHEMA.Tables T with (nolock)
Inner Join sys.indexes I with (nolock)
ON I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 0 -- Only select active indexes !
where exists ( Select *
from sys.indexes I with (nolock)
where I.[object_id] = object_id('[' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + ']')
and I.is_disabled = 1 )
OR EXISTS (SELECT *
FROM INFORMATION_SCHEMA.Columns C
Where C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
-- exclude text, ntext, image, varchar(max), nvarchar(max), varbinary(max)
AND ( C.CHARACTER_OCTET_LENGTH > 8000
OR C.CHARACTER_OCTET_LENGTH = (-1)
)
)
)
Select 'use [' + db_name() + '] ;
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX ALL ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD '
+ case @OnlineRebuild when 'Y' then ' WITH ( ONLINE = ON )' else '' end + ' ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
Left Join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and D.TABLE_SCHEMA IS NULL
and T.TABLE_NAME <> 'dtproperties'
Union ALL
Select 'use [' + db_name() + '];
print '' tabel [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] IX ' + cast(INDEX_ID as varchar(15)) + ' [' + INDEX_NAME + '] '' + convert(varchar(25),getdate(),121) ;
ALTER INDEX [' + INDEX_NAME + '] ON [' + T.TABLE_SCHEMA + '].['+ T.TABLE_NAME + '] REBUILD WITH ( ONLINE = OFF ) ;'
from INFORMATION_SCHEMA.Tables T with (nolock)
inner join cteExcludedObjects D
on T.TABLE_SCHEMA = D.TABLE_SCHEMA
and T.TABLE_NAME = D.TABLE_NAME
Where T.TABLE_TYPE = 'BASE TABLE'
and INDEX_NAME is not NULL -- Heap niet van toepassing
order by 1 ;
open c1
FETCH NEXT FROM c1 INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
-- print @sqlstmt
exec ( @SQLStmt )
set @Tellerke = @Tellerke + 1
FETCH NEXT FROM c1 INTO @SQLStmt
END
-- Cursor afsluiten
CLOSE c1
DEALLOCATE c1
Print ' '
Print '# Objects handled'
Print '------------------'
Print @Tellerke
print ' '
print '-- Usage ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
DBCC UPDATEUSAGE (0) with count_rows ;
print '-- Usage Statistics ' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
EXEC sp_updatestats ;
print '-- The end' + db_name() + ' ' + convert(varchar(25),getdate(),121) ;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply