February 4, 2010 at 2:47 pm
I've got this request come through and I have no idea how to get a list of user-defined statistics out of a database.
Anyone able to help here? Btw, couldn't find a SQL 2000 forum so posting in this one.
Microsoft SQL Server 2000 Column Statistics
Microsoft SQL Server 2000 introduced a feature to create user-defined statistics on columns within a table. This feature is not supported by PeopleSoft PeopleTools. If you added user-defined statistics to any columns in your PeopleSoft application, it may cause errors to occur during the upgrade steps that alter tables. PeopleSoft recommends that you drop all user-defined statistics on columns of PeopleSoft tables before proceeding with your upgrade.
February 4, 2010 at 3:36 pm
Try this for each table in the DB
sp_statistics 'your table name''
Read up on sp_statistics (Books On Line)
Returns a list of all indexes and statistics on a specified table or indexed view.
or use this:
http://msdn.microsoft.com/en-us/library/aa260297(SQL.80).aspx
Pay particular attention to the value contained in the Type column
February 4, 2010 at 3:44 pm
Thanks for that Ron, is there an easier way though to pull this information out as there's over 2000 tables?
February 4, 2010 at 3:53 pm
You could query sysobjects for type 'U' (User Table) and run your command through a cursor to return the results of the statistics query for every table.
The following example will give you an example just change the code to query the statistics rather than the space:
February 4, 2010 at 5:06 pm
This is the query I have running off SQL 2008 with a copy of the SQL 2000 database.
If I run it on SQL 2000 SP3a I get:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.tables'.
It's returning a heap of data but mostly it appears to be rubbish. Can anyone explain it please?
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
table_qualifier sysname
,table_owner varchar(255)
,table_name varchar(255)
,non_unique varchar(255)
,index_qualifier varchar(255)
,index_name varchar(255)
,[type] varchar(255)
,seq_in_index varchar(255)
,column_name varchar(255)
,collation varchar(255)
,cardinality varchar(255)
,pages varchar(255)
,filter_condition varchar(255)
)
INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
DECLARE c1 CURSOR FOR
SELECT t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
SET @table_name = REPLACE(@table_name, 'dbo.','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_statistics @table_name;
print @table_name
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
select * from #temp_Table
where [type] = 0
DROP TABLE #temp_Table
February 5, 2010 at 12:02 am
The SQL 2000 forums are further down the page. They are there. I've requested a mod to move this thread there.
For statistics on SQL 2000, you can query sysindexes (that's where they were stored) and use the IndexProperty function to see if a row is an index or a statistic.
What information exactly are you looking for?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 5, 2010 at 1:09 pm
JamesNZ
get a list of user-defined statistics out of a database.
Now this will give you all the statistics, other than those created is Auto_statistics is set ON. Those you should not have to be worried about.
Now which ones were created by the Vendor, or by personnel in your company, hard to say. Another caveat ... A user could create a statistic and start its name with "_WA_" and the sp_statistics routine will treat it as an Auto_created statistic..
I modified the code you presented and ran the code in Query Analyzer
DECLARE @table_name VARCHAR(500)
CREATE TABLE #temp_Table (
table_qualifier sysname
,table_owner varchar(255)
,table_name varchar(255)
,non_unique varchar(255)
,index_qualifier varchar(255)
,index_name varchar(255)
,[type] varchar(255)
,seq_in_index varchar(255)
,column_name varchar(255)
,collation varchar(255)
,cardinality varchar(255)
,pages varchar(255)
,filter_condition varchar(255))
DECLARE c1 CURSOR FOR
SELECT Name
FROM sysobjects WHERE Xtype = 'U'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_Table EXEC sp_statistics @table_name;
print @table_name
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
select table_owner,table_name,index_name, type from #temp_Table
where [type]= 0 --type defines the item 0 =statistic, 1=clustered index 2=Hashed index, 3=Other
--DROP TABLE #temp_Table
A sample of the returned information
Table Index
Owner Name Name Type
dboOrders NULL 0
dboProducts NULL 0
dboOrder Details NULL 0
dboCustomerCustomerDemoNULL 0
dboCustomerDemographicsNULL 0
dboChapter3_CustomersNULL 0
Hopefully this will clear up some confusion .. if NOT post back again
February 7, 2010 at 1:59 pm
Thanks very much for that, I'll post back with any questions 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply