Problem creating script for automating ALTER INDEX maintenance

I'm new to SQL 2005, but have been working with SQL 2000 for quite some time
.
In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
Self Study guide, working on managing index fragmentation.
What I am trying to do is collect data from sys.dt_db_index_physical_stats
DMF. I'm creating a temporary table that houses the table name in the forma
t
"schema.table". Then I am attempting to put some of that information into a
cursor, and run an ALTER INDEX on all tables that match the criteria I need.
Following is the script I've come up with:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPercent
)
select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare c_Indexreorg cursor
for
select distinct TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg
while (@.@.FETCH_STATUS=0)
begin
ALTER index ALL on [TableName]
REORGANIZE
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
The table is created, but I get the following error:
Msg 1088, Level 16, State 9, Line 46
Cannot find the object "TableName" because it does not exist or you do not
have permissions.
I made sure the tablename in the temp table was in schema.tablename format,
but that still didn't help.
What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
Richard Tocci
College Station, TXRichard
Do you run this script under what account?
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some
> time.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the
> format
> "schema.table". Then I am attempting to put some of that information into
> a
> cursor, and run an ALTER INDEX on all tables that match the criteria I
> need.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename
> format,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||First, I want to recommend an already prepared script for you. See this topi
c in Books Online:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
If you scroll down towards the end, you will find a script that does exactly
what you want to do. In
addition, the script will only reoorganize the index if it is fragmented in
the first place.
To answer why your script doesn't work:

> fetch next from c_Indexreorg
You don't fetch the column from the cursor into any variables. You should ha
ve something like:

> fetch next from c_Indexreorg INTO @.v1, @.v1, ...

> ALTER index ALL on [TableName]
Here you have hardcoded the table name to "TableName", and you probably don'
t have a table with that
name in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
> I'm new to SQL 2005, but have been working with SQL 2000 for quite some ti
me.
> In studying for the SQL 2005 test 70-431, I'm working in Chapter 12 of the
> Self Study guide, working on managing index fragmentation.
> What I am trying to do is collect data from sys.dt_db_index_physical_stats
> DMF. I'm creating a temporary table that houses the table name in the for
mat
> "schema.table". Then I am attempting to put some of that information into
a
> cursor, and run an ALTER INDEX on all tables that match the criteria I nee
d.
> Following is the script I've come up with:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, AvgFragInPercent, AvgPageSpaceUsedInPerce
nt)
> select schema_name(sc.schema_id) + '.' + object_name(dt.object_id) as
> 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare c_Indexreorg cursor
> for
> select distinct TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg
> while (@.@.FETCH_STATUS=0)
> begin
> ALTER index ALL on [TableName]
> REORGANIZE
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
> The table is created, but I get the following error:
> Msg 1088, Level 16, State 9, Line 46
> Cannot find the object "TableName" because it does not exist or you do not
> have permissions.
> I made sure the tablename in the temp table was in schema.tablename format
,
> but that still didn't help.
> What am I doing wrong? Is ALTER INDEX not allowed in a cursor?
>
> --
> Richard Tocci
> college Station, TX|||After reading the Books Online help file, I modified the script and now it
works. Here it is:
set ansi_nulls on
set quoted_identifier on
if exists (select * from sys.objects where name='tmp_TableIndex')
begin
drop table tmp_TableIndex
end
create table tmp_TableIndex
(
TableName varchar(50),
SchemaName varchar(50),
AvgFragInPercent int,
AvgPageSpaceUsedInPercent int
)
insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
AvgPageSpaceUsedInPercent)
select schema_name(sc.schema_id) as 'SchemaName',
object_name(dt.object_id) as 'TableName',
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats
(
db_id(db_name()), null, null, null, 'detailed'
)
dt
join sys.objects sc
on sc.object_id=dt.object_id
join sys.indexes si
on si.object_id=dt.object_id
and si.index_id=dt.index_id
where dt.index_ID<>0
and dt.avg_fragmentation_in_percent between 10 and 15
or dt.avg_page_space_used_in_percent between 60 and 75
declare @.table_name varchar(50),
@.schema_name varchar(50),
@.command varchar(100)
declare c_Indexreorg cursor
for
select distinct SchemaName, TableName from tmp_TableIndex
open c_Indexreorg
fetch next from c_Indexreorg into @.table_name, @.schema_name
while (@.@.FETCH_STATUS=0)
begin
set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
REORGANIZE'
exec (@.command)
fetch next from c_Indexreorg
end
close c_Indexreorg
deallocate c_Indexreorg
Once I created a couple more variables (I had the @.table_name variable on a
previous iteration of the script but took it out, thinking it was making it
to complex), and not slamming the schema name and table name together in my
temporary table, it seemed to work better.
THanks for all that responded.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:

> First, I want to recommend an already prepared script for you. See this to
pic in Books Online:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2
d-e57702230613.htm
> If you scroll down towards the end, you will find a script that does exact
ly what you want to do. In
> addition, the script will only reoorganize the index if it is fragmented i
n the first place.
>
> To answer why your script doesn't work:
>
> You don't fetch the column from the cursor into any variables. You should
have something like:
>
>
> Here you have hardcoded the table name to "TableName", and you probably do
n't have a table with that
> name in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:16DD402A-52FC-4F87-AD7E-B66EA78D7FE6@.microsoft.com...
>
>|||Forgive my curiosity, but below seems surprising:

> and dt.avg_fragmentation_in_percent between 10 and 15
Why wouldn't you want to reorg if the fragmentation level is high?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...[vbcol=seagreen]
> After reading the Books Online help file, I modified the script and now it
> works. Here it is:
> set ansi_nulls on
> set quoted_identifier on
> if exists (select * from sys.objects where name='tmp_TableIndex')
> begin
> drop table tmp_TableIndex
> end
> create table tmp_TableIndex
> (
> TableName varchar(50),
> SchemaName varchar(50),
> AvgFragInPercent int,
> AvgPageSpaceUsedInPercent int
> )
> insert tmp_TableIndex(TableName, SchemaName, AvgFragInPercent,
> AvgPageSpaceUsedInPercent)
> select schema_name(sc.schema_id) as 'SchemaName',
> object_name(dt.object_id) as 'TableName',
> dt.avg_fragmentation_in_percent,
> dt.avg_page_space_used_in_percent
> from sys.dm_db_index_physical_stats
> (
> db_id(db_name()), null, null, null, 'detailed'
> )
> dt
> join sys.objects sc
> on sc.object_id=dt.object_id
> join sys.indexes si
> on si.object_id=dt.object_id
> and si.index_id=dt.index_id
> where dt.index_ID<>0
> and dt.avg_fragmentation_in_percent between 10 and 15
> or dt.avg_page_space_used_in_percent between 60 and 75
> declare @.table_name varchar(50),
> @.schema_name varchar(50),
> @.command varchar(100)
> declare c_Indexreorg cursor
> for
> select distinct SchemaName, TableName from tmp_TableIndex
> open c_Indexreorg
> fetch next from c_Indexreorg into @.table_name, @.schema_name
> while (@.@.FETCH_STATUS=0)
> begin
> set @.command='ALTER index ALL on ' + @.schema_name + '.' + @.table_name + '
> REORGANIZE'
> exec (@.command)
> fetch next from c_Indexreorg
> end
> close c_Indexreorg
> deallocate c_Indexreorg
>
>
> Once I created a couple more variables (I had the @.table_name variable on
a
> previous iteration of the script but took it out, thinking it was making i
t
> to complex), and not slamming the schema name and table name together in m
y
> temporary table, it seemed to work better.
> THanks for all that responded.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>|||Actually, that should be an OR, not an AND. I was going by the 70-431 self
training book, page 453, at the bottom.
--
Richard Tocci
College Station, TX
"Tibor Karaszi" wrote:

> Forgive my curiosity, but below seems surprising:
>
> Why wouldn't you want to reorg if the fragmentation level is high?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Richard Tocci" <richardftoccijr at hotmail dot com> wrote in message
> news:FD361A40-D2EC-448D-A29E-D5A0CBA23A8B@.microsoft.com...
>|||Guys, what book are you referring to? I am studying for the exam and am
looking for all the help I can get.
Thanks!
-Richard K
"Richard Tocci" wrote:
[vbcol=seagreen]
> Actually, that should be an OR, not an AND. I was going by the 70-431 sel
f
> training book, page 453, at the bottom.
> --
> Richard Tocci
> college Station, TX
>
> "Tibor Karaszi" wrote:
>