Indexdefrag for all tables in the database
-
I had the same problem with multiple owners in one database. I replaced the following statement:
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
With this and it worked great:
DECLARE tables CURSOR FOR
SELECT (Table_Schema+'.'+TABLE_NAME) as tablename
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Thanks!
-
Ya that should do it
. -
Thank you Anita, Leifah and Ninja. You were of great help .
-
Our pleasure
. -
Create Procedure sp_IndexDefragAllDatabasesTables
As
EXECUTE sp_msforeachdb 'USE [?]
DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name
FROM information_schema.tables with (nolock)
WHERE table_type = ''base table''
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid
FROM sysindexes with (nolock)
WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ''Defragmenting index_id = '' + convert(char(3), @indid) + ''of the '' + rtrim(@TableName) + '' table''
IF @indid 255
DBCC INDEXDEFRAG ([?], @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
'
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply