June 3, 2014 at 5:13 am
HI there,
I have got this script but when i run this it fails (message is at the bottom ). Can some one correct this script?
use AdventureWorks
go
set nocount on
go
If exists (select * from tempdb.sys.all_objects where name like '#Reorganize' )
Drop table #Reorganize
create table #Reorganize
(Schemaname varchar(50),
tablename varchar(50),
Indexname varchar(150),
Fragmentation float)
go
If exists (select * from tempdb.sys.all_objects where name like '#Rebuild' )
drop table #Rebuild
create table #Rebuild
(Schemaname varchar(100),
tablename varchar(100),
Indexname varchar(150),
Fragmentation float)
go
insert into #reorganize(Schemaname,tablename,Indexname,Fragmentation)
select s.name,o.name,i.name,ips.avg_fragmentation_in_percent from sys.objects o left outer join sys.schemas s on
o.schema_id= s.schema_id left outer join sys.indexes i on
o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id('AdventureWorks’), NULL, NULL, NULL, NULL) AS IPS
on i.object_id=IPS.object_id and i.index_id=ips.index_id
where o.type='U' and i.index_id > 0 and avg_fragmentation_in_percent between 5 and 30
go
insert into #Rebuild(Schemaname,tablename,Indexname,Fragmentation)
select s.name,o.name,i.name,ips.avg_fragmentation_in_percent from sys.objects o left outer join sys.schemas s on
o.schema_id= s.schema_id left outer join sys.indexes i on
o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id('AdventureWorks’), NULL, NULL, NULL, NULL) AS IPS
on i.object_id=IPS.object_id and i.index_id=ips.index_id
where o.type='U' and i.index_id > 0 and avg_fragmentation_in_percent > 30
Declare @cmd varchar(1000)
DECLARE @Iname varchar(250)
DECLARE @Jname varchar(250)
declare @sname varchar(150)
declare @tname varchar(150)
DECLARE db_reindex CURSOR for
select indexname,[SCHEMANAME],tablename from #Reorganize
OPEN db_reindex
FETCH NEXT from db_reindex into @Iname,@sname,@tname
WHILE @@FETCH_STATUS = 0
BEGIN
set @Jname= @sname + '.'+ @tname
set @cmd= 'Alter INdex ' + @Iname + ' on '+ @Jname + ' reorganize'
execute (@cmd)
FETCH NEXT from db_reindex into @iname,@sname,@tname
select 'Executed Reindex reorganize for ' + @Jname + ' '+ @Iname
END
CLOSE db_reindex
DEALLOCATE db_reindex
GO
Declare @cmd Varchar(1000)
DECLARE @Iname varchar(250)
DECLARE @Jname varchar(250)
declare @sname varchar(150)
declare @tname varchar(150)
DECLARE db_reindex CURSOR for
select indexname,[SCHEMANAME],tablename from #Rebuild
OPEN db_reindex
FETCH NEXT from db_reindex into @Iname,@sname,@tname
WHILE @@FETCH_STATUS = 0
BEGIN
set @Jname= @sname + '.'+ @tname
set @cmd= 'Alter INdex ' + @Iname + ' on '+ @Jname + ' rebuild'
execute (@cmd)
FETCH NEXT from db_reindex into @iname,@sname,@tname
select 'Executed Reindex rebuild for ' + @Jname + ' '+ @Iname
END
CLOSE db_reindex
DEALLOCATE db_reindex
GO
------------ERROR-------------------
Msg 2714, Level 16, State 6, Line 5
There is already an object named '#Reorganize' in the database.
Msg 2714, Level 16, State 6, Line 3
There is already an object named '#Rebuild' in the database.
Msg 8152, Level 16, State 2, Line 4
String or binary data would be truncated.
The statement has been terminated.
June 3, 2014 at 7:32 am
Check this thread about halfway down the page where Gail mentions the parse-time error. I think the simplest thing to do would be to use new table names.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply