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';


USE @db_name;

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.

  • 1
    You'll need to use dynamic SQL for this I suspect...
    – JNK
    Apr 23, 2012 at 15:16

3 Answers 3


Dynamic SQL comes in handy for these types of administrative tasks. Here is a snippet from a stored procedure I wrote that not only gets the defragmentation levels, but also generates the code to do the defragmentation:

select @SQL = 
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       db_id(''' + @DatabaseName + '''),
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id

exec (@SQL)
  • Based on JNK's comment I altered my query to employ dynamic SQL. It works, of course. I have a separate query to generate the defrag code. I'll give this a run and how it looks. Accepting this as the answer, however.
    – mdoyle
    Apr 23, 2012 at 16:40
  • One thing to note...this runs on SQL Server 2008R2 Enterprise Edition. The "rebuild with (online = on)" is not supported on standard edition.
    – datagod
    Apr 23, 2012 at 16:59
  • 1
    Sweet, I have enough rep to up-vote your answer now. :-) This is a slick query, but I like having the code-generating query separate. I like running the results to text and being able to cut-n-paste into a job. Very elegant solution all the same--thanks!
    – mdoyle
    Apr 23, 2012 at 17:55

The alternative to dynamic SQL is SQLCMD, which can be invoked from the command line, an agent job step, the Invoke-Sqlcmd Powershell cmdlet or enabled in SSMS. Your example in SQLCMD syntax would be:

:SETVAR DatabaseName MyDatabase

SELECT * FROM $(DatabaseName).sys.indexes;

SQLCMD mode is one of those features I wish I'd known about earlier. Handy in lots of situations.

  • 1
    This is also pretty simple and elegant to accomplish with PowerShell. I like your approach, Mark. +1 Apr 23, 2012 at 17:14
  • @Shark Good point, I'll edit the powershell cmdlet in. Apr 23, 2012 at 17:17
  • Also a good solution, I'll look into this as well.
    – mdoyle
    Apr 23, 2012 at 17:57
  • This is nice, is there a good reason why this is not supported in TSQL?
    – tbone
    Jun 7, 2016 at 20:46

It is usually difficult to reference one set of tables from a procedure contained in a different DB. If you install your procedure in Master, as a system procedure, then it can be used in other DB contexts without trying to refer back to itself.

I think that: if your procedure starts with 'sp_' then it becomes universally visible, and if you define it in the 'sys.sp_%' schema, then it can be used in other DB contexts.

This would provide an alternate way to operate in multiple DBs without having to plug in the DB_name dynamically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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