May 29, 2009 at 12:09 pm
Hi,
I am trying to drop columns of several tables as a part of refactoring. Before dropping a column I dropped its FK constraints. But there are Index and statistics that I know very little about which I need to drop.
I found a stored procedure example in this forum which drops the constraints and index but not statistics.
Drop Statistics table.statisticsName is the script I think. But how to find statistics for a particular column and drop them. Please let me know.
Thanks in Advance,
Nithya.
December 8, 2010 at 1:26 pm
This should do it for you.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspDropStatsOnColumn]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspDropStatsOnColumn]
go
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[uspDropStatsOnColumn](
@strTableName nvarchar(100) = 'aowCompositeFact'
, @strColumnName nvarchar(100) = 'SystemSourceID'
, @blnPrintOnly bit = 1
) as
/*
exec uspDropStatsOnColumn
*/
set nocount on
declare @StatsInfo table (strStatName varchar(128), strColumns varchar(2100))
insert into @StatsInfo
exec sp_helpstats @strTableName
declare @strSQL varchar(8000); set @strSQL=''
select @strSQL = @strSQL + 'DROP STATISTICS ' + @strTableName + '.' + strStatName+ '; '
+ char(13) from @StatsInfo WHERE strColumns LIKE '%' + @strColumnName + '%'
if @blnPrintOnly = 1
print @strSQL
else
exec(@strSQL)
GO
February 8, 2012 at 6:46 am
One small addition - include brackets around the table name.
...
SELECT @strSQL =@strSQL + 'DROP STATISTICS [' + @strTableName + '].' +
strStatName+ '; ' + char(13)
...
Others include adding a variable as a [server].[db].[schema] param, but I thought that to be more specialized. The need for brackets on the table name occurs often enough to account for it.
-- Steve
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply