Problem with Script

Hey all,
Can anyone explain me why this doesnt work:
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
*****declare show_defrag cursor for
exec ('select [name] from ' + @.dbname
+ '..sysobjects where type =''U''') for read only *** how
come i cannot use the variable @.dbname in the second
cursor without getting an error? Any workarround?
Please help,
Thanxs> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
Do the whole lot inside dynamic SQL:
SET @.sql = 'DECLARE show_defrag cursor for SELECT "name" FROM ' + @.dbname +
'..sysobjects WHERE ...'
EXEC(@.sql)
However, you don't have to loop inside the database for DBCC SHOWCONTIG.
Just use DBCC SHOWCONTIG at the database level using the WITH TABLERESULTS
option.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> Hey all,
> Can anyone explain me why this doesnt work:
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> *****declare show_defrag cursor for
> exec ('select [name] from ' + @.dbname
> + '..sysobjects where type =''U''') for read only *** how
> come i cannot use the variable @.dbname in the second
> cursor without getting an error? Any workarround?
> Please help,
> Thanxs|||It dont seem to work... the process just
stays "pending"... Here is the whole script.. any help
would be greatful:
set nocount on
CREATE TABLE #aux (
[ObjectName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectId] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexName] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[IndexId] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Level] [varchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Pages] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Rows] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MinimumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MaximumRecordSize] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageRecordSize] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[FowardedRecords] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Extents] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentSwitches] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AverageFreeBytes] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AveragePageDensity] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ScanDensity] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[BestCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ActualCount] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LogicalFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ExtentFragmentation] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
declare db_defrag cursor for
select [name] from master..sysdatabases where dbid
NOT IN (1,2,3,4,5,6) for read only
declare @.dbname varchar(50)
open db_defrag
fetch next from db_defrag into @.dbname
while @.@.fetch_status = 0
declare @.sql varchar (300)
SET @.sql = 'DECLARE show_defrag cursor for
SELECT [name] FROM ' + @.dbname
+ '..sysobjects WHERE type = ''U'' for read only'
declare @.objectname varchar(200)
declare @.msg varchar(200)
EXEC(@.sql)
--declare show_defrag cursor for
--select [name] from ..sysobjects where type = 'U'
for read only
open show_defrag
fetch next from show_defrag into @.objectname
while @.@.fetch_status = 0
Begin
INSERT INTO #aux
EXEC ('DBCC SHOWCONTIG (' + @.objectname
+ ') WITH TABLERESULTS')
if exists (select Objectname from #aux where
Objectname = @.objectname and
ExtentSwitches > Extents or
ScanDensity < cast (95.0 as Decimal) or
LogicalFragmentation > cast (10.0 as Decimal) or
ExtentFragmentation > cast (10.0 as Decimal))
Begin
set @.msg = 'The table ' + @.objectname + ' in
database' + @.dbname + ' is fragmented'
Print @.msg
--exec master..xp_logevent 51515, @.msg,
informational
End
fetch next from db_defrag into @.dbname
fetch next from show_defrag into @.objectname
End
deallocate db_defrag
deallocate show_defrag
drop table #aux
set nocount off
>--Original Message--
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>Do the whole lot inside dynamic SQL:
>SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
FROM ' + @.dbname +
>'..sysobjects WHERE ...'
>EXEC(@.sql)
>However, you don't have to loop inside the database for
DBCC SHOWCONTIG.
>Just use DBCC SHOWCONTIG at the database level using the
WITH TABLERESULTS
>option.
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>":)" <anonymous@.discussions.microsoft.com> wrote in
message
>news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
>> Hey all,
>> Can anyone explain me why this doesnt work:
>> declare db_defrag cursor for
>> select [name] from master..sysdatabases where dbid
>> NOT IN (1,2,3,4,5,6) for read only
>> declare @.dbname varchar(50)
>> open db_defrag
>> fetch next from db_defrag into @.dbname
>> while @.@.fetch_status = 0
>> *****declare show_defrag cursor for
>> exec ('select [name] from ' + @.dbname
>> + '..sysobjects where type =''U''') for read only ***
how
>> come i cannot use the variable @.dbname in the second
>> cursor without getting an error? Any workarround?
>> Please help,
>> Thanxs
>
>.
>|||One thing I see that that inside your WHILE, you need BEGIN and END:
WHILE ...
BEGIN
...
...
FETCH NEXT FROM ...
END
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
":)" <anonymous@.discussions.microsoft.com> wrote in message
news:0b9501c3adb6$987ae5b0$a101280a@.phx.gbl...
> It dont seem to work... the process just
> stays "pending"... Here is the whole script.. any help
> would be greatful:
>
>
> set nocount on
> CREATE TABLE #aux (
> [ObjectName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ObjectId] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexName] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [IndexId] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Level] [varchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Pages] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Rows] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MinimumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MaximumRecordSize] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageRecordSize] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [FowardedRecords] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Extents] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentSwitches] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AverageFreeBytes] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AveragePageDensity] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ScanDensity] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [BestCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ActualCount] [varchar] (100) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LogicalFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ExtentFragmentation] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> declare db_defrag cursor for
> select [name] from master..sysdatabases where dbid
> NOT IN (1,2,3,4,5,6) for read only
> declare @.dbname varchar(50)
> open db_defrag
> fetch next from db_defrag into @.dbname
> while @.@.fetch_status = 0
> declare @.sql varchar (300)
> SET @.sql = 'DECLARE show_defrag cursor for
> SELECT [name] FROM ' + @.dbname
> + '..sysobjects WHERE type = ''U'' for read only'
> declare @.objectname varchar(200)
> declare @.msg varchar(200)
> EXEC(@.sql)
>
> --declare show_defrag cursor for
> --select [name] from ..sysobjects where type = 'U'
> for read only
>
> open show_defrag
> fetch next from show_defrag into @.objectname
> while @.@.fetch_status = 0
> Begin
> INSERT INTO #aux
> EXEC ('DBCC SHOWCONTIG (' + @.objectname
> + ') WITH TABLERESULTS')
>
> if exists (select Objectname from #aux where
> Objectname = @.objectname and
> ExtentSwitches > Extents or
> ScanDensity < cast (95.0 as Decimal) or
> LogicalFragmentation > cast (10.0 as Decimal) or
> ExtentFragmentation > cast (10.0 as Decimal))
> Begin
> set @.msg = 'The table ' + @.objectname + ' in
> database' + @.dbname + ' is fragmented'
> Print @.msg
> --exec master..xp_logevent 51515, @.msg,
> informational
> End
> fetch next from db_defrag into @.dbname
> fetch next from show_defrag into @.objectname
> End
> deallocate db_defrag
> deallocate show_defrag
> drop table #aux
>
> set nocount off
>
>
>
> >--Original Message--
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >
> >Do the whole lot inside dynamic SQL:
> >
> >SET @.sql = 'DECLARE show_defrag cursor for SELECT "name"
> FROM ' + @.dbname +
> >'..sysobjects WHERE ...'
> >EXEC(@.sql)
> >
> >However, you don't have to loop inside the database for
> DBCC SHOWCONTIG.
> >Just use DBCC SHOWCONTIG at the database level using the
> WITH TABLERESULTS
> >option.
> >
> >
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >":)" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:5b3a01c3ad27$de17fe30$a601280a@.phx.gbl...
> >> Hey all,
> >>
> >> Can anyone explain me why this doesnt work:
> >>
> >> declare db_defrag cursor for
> >> select [name] from master..sysdatabases where dbid
> >> NOT IN (1,2,3,4,5,6) for read only
> >>
> >> declare @.dbname varchar(50)
> >>
> >> open db_defrag
> >> fetch next from db_defrag into @.dbname
> >>
> >> while @.@.fetch_status = 0
> >>
> >> *****declare show_defrag cursor for
> >> exec ('select [name] from ' + @.dbname
> >> + '..sysobjects where type =''U''') for read only ***
> how
> >> come i cannot use the variable @.dbname in the second
> >> cursor without getting an error? Any workarround?
> >> Please help,
> >>
> >> Thanxs
> >
> >
> >.
> >