0

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
  • I think I need to wrap everything in a single statement and use execute sql, right? – Racer SQL Jan 9 '18 at 18:58
  • If you're doing it just to learn, does it matter how long it takes to run? There are obviously better methods if you care how long it takes... – LowlyDBA Jan 9 '18 at 19:20
  • 1
    @LowlyDBA How you call index_physical_stats is a lot less relevant to runtime than whether you call it. – Aaron Bertrand Jan 9 '18 at 19:29
  • This runs forever because you forgot to wrap the inside of the loop with begin / fetch next / end. In other words, it's not a loop, and you are repeating the same command against the same database until you decide to end the bleeding and hit stop. Surely there are examples of cursor code out there that you can borrow from, rather than try to piece it together from memory. – Aaron Bertrand Jan 9 '18 at 19:31
2

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 > 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 > 4 /* primary system dbs */


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

You could also look at my sp_msForEachDB replacement.

  • Ooh thanks @Aaron. I didn't know I can use Database.sys.sp_executesql. This is really useful. – Racer SQL Jan 9 '18 at 19:05
  • Oh thank you for the edit. Yes I could do this without a cursor, with sp_msforeachdb and some more. I just have problems do understand how cursor works. I just, can't understand. It's like magic to me. – Racer SQL Jan 9 '18 at 19:09
  • 1
    @RacerSQL Please do not use sp_msforeachdb. – Aaron Bertrand Jan 9 '18 at 19:10
  • Oh I Know, I read a post about it a long time ago. I Just have a question. I'm reading about sp_executesql and I', having problems understanding this. when you say EXEC sys.sp_executesql @sql, N'@cmd nvarchar(max)', @cmd;, this N'@cmd nvarchar(max)' is to specify that @cmd is a nvarchar(max) right? – Racer SQL Jan 10 '18 at 11:44
  • I mean, I can't just use EXEC SYS.SP_EXECUTESQL @SQL, @CMD'.Am I right? – Racer SQL Jan 10 '18 at 11:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.