DROP STATISTICS - Specifying a Database

  • Hello,

    I need to conditionally drop a STATISTIC in a database other than the one from which the stored procedure executing the DROP STATISTIC command resides. However I am getting an error.

    In database [DatabaseOne] I have the following Statistic [StatisticOne] ON [DatabaseOne].[dbo].[TableOne] ...

    In database [DatabaseTwo] I have a stored procedure [ProcOne] which must (among other things) drop the statistic in DatabaseOne named StatisticOne. So I have tried the following:

    USE DatabaseTwo

    GO

    CREATE PROCEDURE [dbo].[ProcOne]

    AS

    ...

    if exists (select * from DatabaseOne.sys.stats where name = N'StatisticOne' and object_id = object_id(N'[DatabaseOne].[dbo].[TableOne]'))

    DROP STATISTICS [DatabaseOne].[dbo].[TableOne].[StatisticOne]

    However, I get the following error message at the DROP STATISTIC:

    'DROP STATISTICS' does not allow specifying the database name as a prefix to the object name.

    The same approach works on indexes but the DROP INDEX statement is structured differently.

    Has anyone out there encountered this issue before that could pass on some advice?

    Chris Umbaugh
    Data Warehouse / Business Intelligence Consultant
    twitter @ToledoSQL

  • EXEC('USE [DatabaseOne]; DROP STATISTICS [dbo].[TableOne].[StatisticOne]')

  • *blushing a little bit* I should have known this.

    Thanks for the help. That worked.

    Chris Umbaugh
    Data Warehouse / Business Intelligence Consultant
    twitter @ToledoSQL

  • I have to have to use it myself... but that's the way it works right now!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply