I am writing a stored procedure that takes a database name as an argument and returns a table of that database's indexes and their fragmentation level. This stored procedure will live in our DBA database (the DB that contains tables the DBAs use for monitoring and optimizing things). The systems in question are all SQL Server 2008 R2 if that makes a difference.
I have the basic query worked out, but am stuck on trying to provide the indexes' actual names. To the best of my knowledge, that information is contained in each individual's sys.indexes view. My specific problem is trying to reference that view programmatically from another database's stored procedure.
To illustrate, this is the portion of the query at issue:
FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id] AND p.index_id = b.index_id AND b.index_id != 0
The query works fine when executed from the database identified by @db_id, because it is using the proper sys.indexes view. If I try to call this from the DBA database, however, it all comes up null, as the sys.indexes view is for the wrong database.
In more general terms, I need to be able to do something like this:
DECLARE @db_name NVARCHAR(255) = 'my_database'; SELECT * FROM @db_name + '.sys.indexes';
I have tried switching databases or referencing other databases using combinations of string concatenation and OBJECT_NAME/OBJECT_ID/DB_ID functions and nothing seems to work. I'd appreciate any ideas the community might have, but suspect I will have to retool this stored procedure to reside in each individual database.
Thanks in advance for any suggestions.