March 21, 2018 at 12:03 pm
I need to execute this query for Multiple DB to check Index and store in a table, below query only execute for one Database.
Insert into master.dbo.[Index_Frag] (
Database_Name
,Eschema
,TableName
,IndexName
,[Avg_Fragm]
,page_count
)SELECT db_name(database_id) database_Name, dbschemas.[name] as ESchema,
dbtables.[name] as TableName,
dbindexes.[name] as Indexname,
indexstats.avg_fragmentation_in_percent AS Avg_Fragm,
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
and indexstats.avg_fragmentation_in_percent >30
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent
March 22, 2018 at 12:39 am
Hi,
you can use cursor to query all your databases on your sql server:
Here is a very good link:
http://www.sqlservercentral.com/blogs/vivekssqlnotes/2011/07/21/cursor-in-sql-server/
Kind regards,
Andreas
March 22, 2018 at 7:22 am
Nita Reddy - Wednesday, March 21, 2018 12:03 PMI need to execute this query for Multiple DB to check Index and store in a table, below query only execute for one Database.Insert into master.dbo.[Index_Frag] (
Database_Name
,Eschema
,TableName
,IndexName
,[Avg_Fragm]
,page_count
)SELECT db_name(database_id) database_Name, dbschemas.[name] as ESchema,
dbtables.[name] as TableName,
dbindexes.[name] as Indexname,
indexstats.avg_fragmentation_in_percent AS Avg_Fragm,
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
and indexstats.avg_fragmentation_in_percent >30
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent
I came up with a single query that would provide everything you need, except the name of the indexes on the tables. Unfortunately there doesn't seem to be a system function for getting that information without actually being in the database where the indexes reside.
So, I agree, use a cursor to loop through the databases on the server. You also need to make your code dynamic SQL in order to make it work.
March 22, 2018 at 9:48 am
Wondering if it might be useful to use sp_msforeachdb, which is an undocumented stored procedure that can do that kind of thing. You use ? in your query wherever you need a database name. It would be a slightly different form of dynamic SQL, but the operation of it would be the same. The table you insert into would have to have the DB name as part of the name because of the changing execution context, because you'll be doing a USE ? within the query you supply to that sproc. If you google it, there's plenty of material about it online.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 22, 2018 at 10:32 am
Central Management Server or Regate Multiscript can do this as well.
March 23, 2018 at 11:39 pm
use @databasename is your answer, so in your case it will be something like( you will need to pass database name):
USE ' + @databasename + '
Insert into master.dbo.[Index_Frag] (
Database_Name
,Eschema
,TableName
,IndexName
,[Avg_Fragm]
,page_count
)
SELECT '''+@databasename+''',
.....................
FROM sys.dm_db_index_physical_stats (db_id(''' + @databasename + '''), NULL, NULL, NULL, NULL)
........................
March 26, 2018 at 1:39 pm
You could also just use your original query inside a cursor / dynamic sql pairing, actually executing the sql is left as an exercise for the reader
declare dbfetcher cursor for select quotename([name]) as dbname from sys.databases where database_id > 4;
declare @insertstmt varchar(1000);
declare @dbname sysname;
-- heh maybe somebody has named a database as "DUH;drop database name" lol
open dbfetcher
fetch next from dbfetcher into @dbname;
while (@@fetch_status = 0)
begin
set @insertstmt = ' use ' + @dbname + ';
go
Insert into master.dbo.[Index_Frag] (
Database_Name
,Eschema
,TableName
,IndexName
,[Avg_Fragm]
,page_count
)SELECT db_name(database_id) database_Name, dbschemas.[name] as ESchema,
dbtables.[name] as TableName,
dbindexes.[name] as Indexname,
indexstats.avg_fragmentation_in_percent AS Avg_Fragm,
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
and indexstats.avg_fragmentation_in_percent >30
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent;
go'
print @insertstmt
---------------------------------------------------------------------------------------
-- right about here is where you would execute the dynamic sql stored in @insertstmt --
-- please read about developing and testing dynamic sql before finishing this proc. --
---------------------------------------------------------------------------------------
fetch next from dbfetcher into @dbname
end
close dbfetcher
deallocate dbfetcher
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply