How to check if statistics exist on a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to check if statistics exist on a column

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-31 : 07:20:37

Amit writes "When I am trying to alter a column in a table, I get an error that statistics "<column name>" depends on "<column name>"

Therefore I try to run this command

Drop Statistics <TableName.ColumnName>

But all columns do not have statistics so i get this error.

"Cannot drop the statistics 'email_message.secondary_id', because it does not exist in the system catalog."

Is there a way to check if statistics exist on a column before trying to remove it ?

It has to be an automated process therefore I cannot do it manually.

Please Help !"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-31 : 07:24:28

If Col_Length('TableName','ColumnName') is null
--Column Does not exist
else
--Column exists

Madhivanan

Failing to plan is Planning to fail

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-31 : 07:26:49

This should do it:

CREATE TABLE #statsinfo(statname varchar(128), cols varchar(2100))
INSERT INTO #statsinfo EXEC sp_helpstats 'myTable'
DECLARE @sql varchar(8000)
SET @sql=''
SELECT @sql=@sql + 'DROP STATISTICS myTable.' + statname + '; ' from #statsinfo WHERE cols LIKE '%myColumn%'
EXEC(@sql)
DROP TABLE #statsinfo

That will drop all the statistics that include the column you want to alter. Of course it won't recreate the stats afterwards, but it's an easy modification to add such a feature.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources