June 26, 2012 at 6:19 am
I am trying to build a script for dynamic rebuilding of indexes. Hefereo I am planning to use a cursor in a cursor. The first cursor gets a list of database names en id's and passes them through to the second cursor, which gets a list of tables, indexes en fragmentation_values of the indexes
In the example, I leave out the commands foor rebuilding the indexes. Instead I put print commands in to show me the values it gets
The problem is that the select statement for the second cursor uses tables from the master database as wel as from the database that is choosen. The idea is to execute the script on the master database
The script
DECLARE @Database_id INT
DECLARE @Database_name VARCHAR(100)
DECLARE @Db_name VARCHAR(100)
DECLARE @index_name VARCHAR(255)
DECLARE @table_name VARCHAR(100)
DECLARE @fragmentation FLOAT
declare @cmd VARCHAR(max)
DECLARE Db_cursor CURSOR FOR
SELECT[name]
,[database_id]
FROMsys.databases
WHERE state_desc = 'ONLINE'
OPEN Db_cursor;
FETCH NEXT FROM Db_Cursor INTO @database_name, @Database_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'USE ' + '[' + @Database_name + ']'
EXEC (@cmd)
DECLARE TableCursor CURSOR FOR
SELECTd.[name]
,i.name
,t.name
,ps.avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (@database_id, NULL, NULL, NULL, NULL) AS ps
INNER JOINsys.indexes AS i ON
i.object_id = ps.object_id
AND i.index_id = ps.index_id
INNER JOINsys.tables AS t ON
t.object_id = i.object_id
INNER JOINsys.databases d ON
d.database_id = ps.database_id
WHEREps.database_id = @database_id
AND i.name IS NOT NULL
AND ps.avg_fragmentation_in_percent <> 0
ORDER BYps.OBJECT_ID
OPEN Tablecursor;
FETCH NEXT FROM Tablecursor INTO @db_name, @index_name, @table_name, @fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
print @db_name
print @index_name
print @table_name
print @fragmentation
FETCH NEXT FROM Tablecursor INTO @db_name, @index_name, @table_name, @fragmentation
END;
CLOSE Tablecursor
DEALLOCATE Tablecursor
FETCH NEXT FROM Db_Cursor INTO @database_name, @database_id
END;
CLOSE Db_Cursor
DEALLOCATE Db_Cursor
The output is:
master
spt_valuesclust
spt_values
56.25
master
ix2_spt_values_nu_nc
spt_values
71.4286
VCDB
spt_valuesclust
spt_values
90
It's twice the master database with a two indexes and then another database, but with a index and tablename of the masterdatabase.
This means that the code
SET @cmd = 'USE ' + '[' + @Database_name + ']'
EXEC (@cmd)
Doesn't work, because it keeps using the master database
How to solve this?
June 26, 2012 at 6:51 am
info 281 (6/26/2012)
I am trying to build a script for dynamic rebuilding of indexes. Hefereo I am planning to use a cursor in a cursor. The first cursor gets a list of database names en id's and passes them through to the second cursor, which gets a list of tables, indexes en fragmentation_values of the indexesIn the example, I leave out the commands foor rebuilding the indexes. Instead I put print commands in to show me the values it gets
The problem is that the select statement for the second cursor uses tables from the master database as wel as from the database that is choosen. The idea is to execute the script on the master database
The script
DECLARE @Database_id INTDECLARE @Database_name VARCHAR(100)
DECLARE @Db_name VARCHAR(100)
DECLARE @index_name VARCHAR(255)
DECLARE @table_name VARCHAR(100)
DECLARE @fragmentation FLOAT
declare @cmd VARCHAR(max)
DECLARE Db_cursor CURSOR FOR
SELECT[name]
,[database_id]
FROMsys.databases
WHERE state_desc = 'ONLINE'
OPEN Db_cursor;
FETCH NEXT FROM Db_Cursor INTO @database_name, @Database_id
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'USE ' + '[' + @Database_name + ']'
EXEC (@cmd)
DECLARE TableCursor CURSOR FOR
SELECTd.[name]
,i.name
,t.name
,ps.avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (@database_id, NULL, NULL, NULL, NULL) AS ps
INNER JOINsys.indexes AS i ON
i.object_id = ps.object_id
AND i.index_id = ps.index_id
INNER JOINsys.tables AS t ON
t.object_id = i.object_id
INNER JOINsys.databases d ON
d.database_id = ps.database_id
WHEREps.database_id = @database_id
AND i.name IS NOT NULL
AND ps.avg_fragmentation_in_percent <> 0
ORDER BYps.OBJECT_ID
OPEN Tablecursor;
FETCH NEXT FROM Tablecursor INTO @db_name, @index_name, @table_name, @fragmentation
WHILE @@FETCH_STATUS = 0
BEGIN
print @db_name
print @index_name
print @table_name
print @fragmentation
FETCH NEXT FROM Tablecursor INTO @db_name, @index_name, @table_name, @fragmentation
END;
CLOSE Tablecursor
DEALLOCATE Tablecursor
FETCH NEXT FROM Db_Cursor INTO @database_name, @database_id
END;
CLOSE Db_Cursor
DEALLOCATE Db_Cursor
The output is:
master
spt_valuesclust
spt_values
56.25
master
ix2_spt_values_nu_nc
spt_values
71.4286
VCDB
spt_valuesclust
spt_values
90
It's twice the master database with a two indexes and then another database, but with a index and tablename of the masterdatabase.
This means that the code
SET @cmd = 'USE ' + '[' + @Database_name + ']'
EXEC (@cmd)
Doesn't work, because it keeps using the master database
How to solve this?
Instead of above command try to use fully qualified name for tables.
e.g.
SELECT * FROM master.dbo.#mytable1
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 26, 2012 at 7:07 am
That doesn't work int his case, because the databasename is comming from the first variable. I have no idea how to use a Fully qualified Name with a variable in a query
June 26, 2012 at 7:55 am
You will have to use dynamic sql for this and add the database to each object you are querying.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2012 at 10:55 am
Just as an aside, if you are looking to defragment your indexes, try this: http://ola.hallengren.com/
Leonard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply