Sql-server – Reference the database from a stored proc

sql servert-sql

I'm using this T-SQL to get index stats (edited for brevity):

SET @db_id = DB_ID(N'TestDB');
SET @object_id = OBJECT_ID(N'TestDB.dbo.Users');
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');

I want to be able to encapsulate this into a stored proc and call it for whichever database the stored proc is in, but the DB name is hard-coded, how can I get the database name dynamically?

Does T-SQL have anything like:

SET @db_id = DB_ID(this);

Best Answer

If db_id() is called with no parameter it returns the id of the current database - http://msdn.microsoft.com/en-us/library/ms186274.aspx

so you could just use: set @db_id=db_id()

And having read the rest of the question, you could call object_id() without the db name. If the object exists you'll get the id, if not NULL. So: SET @object_id = OBJECT_ID(N'dbo.Users');

Related Solutions

Sql-server – Suddenly Slow Execution Plan for Stored Proc

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.

Sql-server – Referencing database programmatically via T-SQL

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)

Best Answer

Related Solutions

Sql-server – Suddenly Slow Execution Plan for Stored Proc

Sql-server – Referencing database programmatically via T-SQL

Related Question