Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

or

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.

share|improve this question
1  
You'll need to use dynamic SQL for this I suspect... –  JNK Apr 23 '12 at 15:16
add comment

migrated from stackoverflow.com Apr 23 '12 at 15:18

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 9 down vote accepted

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 + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         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)''
       end
  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)
share|improve this answer
    
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 '12 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 '12 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 '12 at 17:55
add comment

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.

share|improve this answer
1  
This is also pretty simple and elegant to accomplish with PowerShell. I like your approach, Mark. +1 –  Thomas Stringer Apr 23 '12 at 17:14
    
@Shark Good point, I'll edit the powershell cmdlet in. –  Mark Storey-Smith Apr 23 '12 at 17:17
    
Also a good solution, I'll look into this as well. –  mdoyle Apr 23 '12 at 17:57
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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