implement this query inside a cursor I'm having problem ( I have problems with cursor since ever and I really need to understand how the hell it works ).

I would like to run this command in all databases:

SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc


I can easily do this with a lot of ways, but I want to pratice cursors.

I made this, but it gives me sintax error:

declare @dbname varchar(max)

declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into dbname

while @@fetch_status=0


use @dbname
SELECT db_name() as  Dtabase_name,
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

close dbcursor
deallocate dbcursor


Where should I put the database variable for the loop in a cursor?

Thanks

EDIT1:

With this, it runs forever. god dammit:

declare @dbname varchar(max)
declare @string varchar(max)


declare dbcursor cursor for
select [name] from sys.databases

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status=0


select @string =
'
use '+@dbname+'
SELECT db_name() as  Dtabase_name,
dbtables.[name] as ''Table'',
dbindexes.[name] as ''Index'',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'

exec sp_executesql @string

close dbcursor
deallocate dbcursor
You need dynamic SQL for this, since you need to change the database context (and USE does not accept variables). Try this:

declare @dbname sysname, 
  @exec nvarchar(4000),
  @sql nvarchar(max);

    SET @sql = N'SELECT db_name() as Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) 
   AS indexstats
INNER JOIN sys.tables dbtables 
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas 
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes 
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

declare dbcursor cursor LOCAL FAST_FORWARD for
select [name] from sys.databases
-- WHERE state = 0 /* online */
-- and database_id &gt; 4 /* primary system dbs */

open dbcursor
fetch next from dbcursor into @dbname

while @@fetch_status = 0
begin
  set @exec = QUOTENAME(@dbname) + N'.sys.sp_executesql';
  exec @exec @sql;
  fetch next from dbcursor into @dbname
end

close dbcursor; deallocate dbcursor


You can also do this without a cursor:

declare   @cmd nvarchar(max),
      @sql nvarchar(max) = N'';

SET @cmd = N'SELECT db_name() as  Database_name,
dbtables.[name] as [Table],
dbindexes.[name] as [Index],
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) 
  AS indexstats
INNER JOIN sys.tables dbtables 
  on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas 
  on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes 
  ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc;';

SELECT @sql += N'EXEC ' + QUOTENAME(name)
  + N'.sys.sp_executesql @cmd;'
  FROM sys.databases
  -- WHERE state = 0 /* online */
  -- and database_id &gt; 4 /* primary system dbs */


EXEC sys.sp_executesql @sql, N'@cmd nvarchar(max)', @cmd;


You could also look at my sp_msForEachDB replacement.