May 9, 2011 at 10:18 am
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
May 9, 2011 at 10:25 am
EXEC('USE [DatabaseOne]; DROP STATISTICS [dbo].[TableOne].[StatisticOne]')
May 9, 2011 at 10:38 am
*blushing a little bit* I should have known this.
Thanks for the help. That worked.
Chris Umbaugh
Data Warehouse / Business Intelligence Consultant
twitter @ToledoSQL
May 9, 2011 at 10:39 am
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