Central stored procedure to execute in calling database context

One way would be to make a system procedure in master and then create a wrapper in your maintenance database. Note that this will only work for one database at a time.

First, in master:

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(),
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
    -- shouldn't s.partition_number be part of the output as well?
  FROM sys.tables AS t
  INNER JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(), t.[object_id], 
      i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  -- probably also want to filter on minimum page count too
  -- do you really care about a table that has 100 pages?
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

Now, in your maintenance database, create a wrapper that uses dynamic SQL to set the context correctly:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,      -- can't really be NULL, right?
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
    EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';

  EXEC sp_executesql 
    @sql,
    N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
    @tableName, @indexID, @partNumber, @Mode;
END
GO

(The reason the database name can't really be NULL is because you can't join to things like sys.objects and sys.indexes since they exist independently in each database. So perhaps have a different procedure if you want instance-wide information.)

Now you can call this for any other database, e.g.

EXEC YourMaintenanceDatabase.dbo.GetFragStats 
  @DatabaseName = N'AdventureWorks2012',
  @TableName    = N'SalesOrderHeader';

And you can always create a synonym in each database so you don't even have to reference the name of the maintenance database:

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

Another way would be to use dynamic SQL, however this, too, will only work for one database at a time:

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT
    DatabaseName    = @DatabaseName,
    TableName       = t.name,
    IndexName       = i.name,
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID, i.index_id)
    AND t.name = COALESCE(@tableName, t.name)
  CROSS APPLY
    ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
        DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;';

  EXEC sp_executesql @sql, 
    N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
      @partNumber INT, @Mode NVARCHAR(20)',
    @DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO

Yet another way would be to create a view (or table-valued function) to union the table and index names of all your databases, however you'd have to hard-code the database names into the view, and maintain them as you add/remove databases that you want to allow to be included in this query. This would, unlike the others, allow you to retrieve stats for multiple databases at once.

First, the view:

CREATE VIEW dbo.CertainTablesAndIndexes
AS
  SELECT 
    db = N'AdventureWorks2012',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM AdventureWorks2012.sys.tables AS t
  INNER JOIN AdventureWorks2012.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  UNION ALL

  SELECT 
    db = N'database2',
    t.[object_id],
    [table] = t.name,
    i.index_id,
    [index] = i.name
  FROM database2.sys.tables AS t
  INNER JOIN database2.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  -- ... UNION ALL ...
  ;
GO

Then the procedure:

CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName NVARCHAR(128) = NULL,
  @tableName    NVARCHAR(128) = NULL,
  @indexID      INT           = NULL,
  @partNumber   INT           = NULL,
  @Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(s.database_id),
    TableName       = v.[table],
    IndexName       = v.[index],
    IndexID         = s.index_id,
    PercentFragment = s.avg_fragmentation_in_percent,
    TotalFrags      = s.fragment_count,
    PagesPerFrag    = s.avg_fragment_size_in_pages,
    NumPages        = s.page_count,
    IndexType       = s.index_type_desc
  FROM dbo.CertainTablesAndIndexes AS v
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
    AND v.index_id = COALESCE(@indexID, v.index_id)
    AND v.[table] = COALESCE(@tableName, v.[table])
    AND v.db = COALESCE(@DatabaseName, v.db)
  ORDER BY 
    DatabaseName, TableName, IndexName, PercentFragment DESC;
END
GO

Well, there's bad news, good news with a catch, and some really good news.

The bad news

T-SQL objects execute in the database where they reside. There are two (not very useful) exceptions:

stored procedures with names prefixed with sp_ and that exist in the [master] database (not a great option: one DB at a time, adding something to [master], possibly adding Synonyms to each DB, which has to be done for each new DB)
temporary stored procedures -- local and global (not a practical option as they have to be created each time and leave you with the same issues that you have with the sp_ stored proc in [master].

The good news (with a catch)

Many (perhaps most?) folks are aware of the builtin functions to get some really common meta-data:

DB_NAME()
OBJECT_NAME()
OBJECT_SCHEMA_NAME()

Using these functions can eliminate the need for the JOINs to sys.databases (though this one is not really a problem), sys.objects (preferred over sys.tables which excludes Indexed Views), and sys.schemas (you were missing that one, and not everything is in the dbo schema ;-). But even with removing three out of the four JOINs, we are still functionally the same place, right? Wrong-o!

One of the nice features of the OBJECT_NAME() and OBJECT_SCHEMA_NAME() functions is that they have an optional second parameter for @database_id. Meaning, while JOINing to those tables (except for sys.databases) is database-specific, using these functions gets you server-wide information. Even OBJECT_ID() allows for server-wide info by giving it a fully-qualified object name.

By incorporating these meta-data functions into the main query, we can simplify while at the same time expand beyond the current database. The first-pass of refactoring the query gives us:

SELECT  DB_NAME(stat.database_id) AS [DatabaseName],
        OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
        OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
        ind.name AS [IndexName],
        stat.index_id AS [IndexID],
        stat.avg_fragmentation_in_percent AS [PercentFragment],
        stat.fragment_count AS [TotalFrags],
        stat.avg_fragment_size_in_pages AS [PagesPerFrag],
        stat.page_count AS [NumPages],
        stat.index_type_desc AS [IndexType]
FROM sys.dm_db_index_physical_stats(@DatabaseID, @TableID, 
        @IndexID, @PartitionNumber, @Mode) stat
INNER JOIN sys.indexes ind
        ON ind.[object_id] = stat.[object_id]
       AND ind.[index_id] = stat.[index_id]
WHERE stat.avg_fragmentation_in_percent > 10
ORDER BY DatabaseName, TableName, IndexName, PercentFragment DESC;

And now for the "catch": there is no meta-data function to get Index names, let alone a server-wide one. So is that it? Are we at 90% complete and still stuck needing to be in a particular databases to get sys.indexes data? Do we really need to create a stored procedure to use Dynamic SQL to populate, each time our main proc runs, a temp table of all sys.indexes entries across all databases so that we can JOIN to it? NO!

The really good news

So along comes a little feature that some folks love to hate, but when used properly, can do some amazing things. Yep: SQLCLR. Why? Because SQLCLR functions can obviously submit SQL statements, but by the very nature of submitting from app code, it is Dynamic SQL. So unlike T-SQL functions, SQLCLR functions can inject a database name into the query before executing it. Meaning, we can create our own function to mirror the ability of OBJECT_NAME() and OBJECT_SCHEMA_NAME() to take a database_id and get the info for that database.

The following code is that function. But it takes a database name instead of ID so that it doesn't need to do the extra step of looking it up (which makes it a little less complicated and a little faster).

public class MetaDataFunctions
{
    [return: SqlFacet(MaxSize = 128)]
    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true,
        SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString IndexName([SqlFacet(MaxSize = 128)] SqlString DatabaseName,
        SqlInt32 ObjectID, SqlInt32 IndexID)
    {
        string _IndexName = @"<unknown>";

        using (SqlConnection _Connection =
                                    new SqlConnection("Context Connection = true;"))
        {
            using (SqlCommand _Command = _Connection.CreateCommand())
            {
                _Command.CommandText = @"
SELECT @IndexName = si.[name]
FROM   [" + DatabaseName.Value + @"].[sys].[indexes] si
WHERE  si.[object_id] = @ObjectID
AND    si.[index_id] = @IndexID;
";

                SqlParameter _ParamObjectID = new SqlParameter("@ObjectID",
                                               SqlDbType.Int);
                _ParamObjectID.Value = ObjectID.Value;
                _Command.Parameters.Add(_ParamObjectID);

               SqlParameter _ParamIndexID = new SqlParameter("@IndexID", SqlDbType.Int);
                _ParamIndexID.Value = IndexID.Value;
                _Command.Parameters.Add(_ParamIndexID);

                SqlParameter _ParamIndexName = new SqlParameter("@IndexName",
                                                  SqlDbType.NVarChar, 128);
                _ParamIndexName.Direction = ParameterDirection.Output;
                _Command.Parameters.Add(_ParamIndexName);

                _Connection.Open();
                _Command.ExecuteNonQuery();

                if (_ParamIndexName.Value != DBNull.Value)
                {
                    _IndexName = (string)_ParamIndexName.Value;
                }
            }
        }

        return _IndexName;
    }
}

If you will notice, we are using the Context Connection, which is not only fast, but also works in SAFE Assemblies. Yep, this works in an Assembly marked as SAFE~~, so it (or variations of it) should even work on Azure SQL Database V12~~ ^{(support for SQLCLR was removed, rather abruptly, from Azure SQL Database in April, 2016)}.

So our second-pass refactoring of the main query gives us the following:

SELECT  DB_NAME(stat.database_id) AS [DatabaseName],
        OBJECT_SCHEMA_NAME(stat.[object_id], stat.database_id) AS [SchemaName],
        OBJECT_NAME(stat.[object_id], stat.database_id) AS [TableName],
        dbo.IndexName(DB_NAME(stat.database_id), stat.[object_id], stat.[index_id])
                     AS [IndexName],
        stat.index_id AS [IndexID],
        stat.avg_fragmentation_in_percent AS [PercentFragment],
        stat.fragment_count AS [TotalFrags],
        stat.avg_fragment_size_in_pages AS [PagesPerFrag],
        stat.page_count AS [NumPages],
        stat.index_type_desc AS [IndexType]
FROM sys.dm_db_index_physical_stats(@DatabaseID, @TableID, 
        @IndexID, @PartitionNumber, @Mode) stat
WHERE stat.avg_fragmentation_in_percent > 10
ORDER BY DatabaseName, TableName, IndexName, PercentFragment DESC;

That's it! Both this SQLCLR Scalar UDF and your maintenance T-SQL Stored Procedure can live in the same centralized [maintenance] database. AND, you don't have to process one database at a time; now you have meta-data functions for all dependent info that is server-wide.

P.S. There is no .IsNull checking of input parameters in the C# code since the T-SQL wrapper object should be created with the WITH RETURNS NULL ON NULL INPUT option:

CREATE FUNCTION [dbo].[IndexName]
                   (@DatabaseName [nvarchar](128), @ObjectID [int], @IndexID [int])
RETURNS [nvarchar](128) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [{AssemblyName}].[MetaDataFunctions].[IndexName];

Additional notes:

The method described here can also be used to solve other, very similar problems of missing cross-database meta-data functions. The following Microsoft Connect suggestion is an example of one such case. And, seeing that Microsoft has closed it as "Won't Fix", it is clear that they are not interested providing built-in functions like OBJECT_NAME() to meet this need (hence the Workaround that is posted on that Suggestion :-).

Add metadata function to get object name from hobt_id
To learn more about using SQLCLR, please take a look at the Stairway to SQLCLR series I am writing on SQL Server Central (free registration is required; sorry, I don't control the policies of that site).
The IndexName() SQLCLR function shown above is available, pre-compiled, in an easy-to-install script on Pastebin. The script enables the "CLR Integration" feature if it is not already enabled, and the Assembly is marked as SAFE. It is compiled against .NET Framework version 2.0 so that it will work in SQL Server 2005 and newer (i.e. all versions that support SQLCLR).

SQLCLR Meta-data function for cross-database IndexName()
If anyone is interested in the IndexName() SQLCLR function and over 320 other functions and stored procedures, it is available in the SQL# library (which I am the author of). Please note that while there is a Free version, the Sys_IndexName function is only available in the Full version (along with a similar Sys_AssemblyName function).

Central stored procedure to execute in calling database context

Tags:

Sql Server

Stored Procedures

Sql Server 2008

Maintenance

Index

Related

Recent Posts