How to check if a Statistics is auto-created in a SQL Server 2000 DB using T-SQL?

Question

Hi all. A while back I had to come up with a way to clean up all indexes and user-created statistics from some tables in a SQL Server 2005 database. After a few attempts it worked, but now I gotta have it working in SQL Server 2000 databases as well. For SQL Server 2005, I used

SELECT Name FROM sys.stats
WHERE object_id = object_id(@tableName)
AND auto_created = 0

to fetch Statistics that were user-created. However, SQL 2000 doesn't have a sys.stats table. I managed to fetch the indexes and statistics in a distinguishable way from the sysindexes table, but I just couldn't figure out what the sys.stats.auto_created match is for SQL 2000. Any pointers?

BTW: T-SQL please.

Answer 1

You could join to the sysobjects (based on the id column) and check the value of the xtype column:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

(from the SQL Server 2000 table map docs)

You need something like:

SELECT SI.name FROM sysobjects AS SO, sysindexes AS SI
WHERE SO.id = SI.id
AND SO.xtype <> 'S'

Answer 2

INDEXPROPERTY has a "IsAutoStatistics" property". I also recall that statistics are stored in sysindexes too so you can get the ID from there.

Sorry, I don't have a SQL 2000 box to test or confirm this on...