DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 139--------------------------------------------------
       Statisitcs and Index Information for a table 
   DIR By: srinivasma_exceldbp
       Date: February 3, 2015, 8:51 pm
       ---------------------------------------------------------
       USE [master]
       GO
       SET ANSI_NULLS ON
       GO
       SET QUOTED_IDENTIFIER ON
       GO
       USE master
       GO
       IF OBJECT_ID('sp_lib_helpstats') > 0
       BEGIN
       DROP PROC sp_lib_helpstats
       END
       GO
       /*
       USAGE
       use adventureworks2012
       go
       exec sp_lib_helpstats  'dbo.employees','STATS'
       exec sp_lib_helpstats  'dbo.employees','ALL'
       exec sp_lib_helpstats  'Production.Product', 'STATS'
       exec sp_lib_helpstats  'Production.Product', 'ALL'
       Description 
       table
       
       Server 
       Database 
       Input Parameters: tablename, result options - 'STATS', 'ALL'
       
       Output Parameter: None
       Return Value 
       Other Outputs 
       Created Date 
       reated_By 
       Modified Date 
       Modified By 
       Modified Version:
       Modifications 
       Called By 
       Calling 
       */
       create procedure sp_lib_helpstats
       @objname nvarchar(776),
       @results nvarchar(5) = 'STATS'
       stats, 'STATS' returns just stats
       as
       ---       Returns the statistics ID along with the statistics
       name and keys
       ---       to provide information useful when looking at
       sys.dm_db_stats_properties
       -- PRELIM
       set nocount on
       declare 
       @indid int,
       @indname sysname,
       @keys nvarchar(2078),-- string build index key list, length =
       (16*max_id_length)+(15*2)
       @dbname
       @i int,
       @thiskey sysname,
       @curs
       -- Check to see that the object names are local to the current
       database.
       select @dbname = parsename(@objname,3)
       if @dbname is null
       select @dbname = db_name()
       else if @dbname <> db_name()
       begin
       raiserror(15250,-1,-1)
       return (1)
       end
       -- Check to see the the table exists and initialize @objid.
       select @objid = object_id(@objname, 'local')
       if @objid is NULL
       begin
       raiserror(15009,-1,-1,@objname,@dbname)
       return (1)
       end
       If UPPER(@results) <> 'STATS' and UPPER(@results)<> 'ALL'
       begin
       raiserror(N'Invalid option: %s', 1, 1, @results)
       return (1)
       end
       If UPPER(@results) = 'STATS'
       begin
       set @curs = cursor local fast_forward READ_ONLY for
       select stats_id, name from sys.stats
       where object_id = @objid
       and IndexProperty(@objid, name, 'IsStatistics') = 1 -- User
       created & auto-created stats
       end
       else
       begin
       set @curs = cursor local fast_forward READ_ONLY for
       select stats_id, name from sys.stats
       where object_id = @objid -- Indexes, User created &
       auto-created stats
       end
       open @curs
       fetch @curs into @indid, @indname
       -- IF NO STATISTICS, QUIT
       if @@fetch_status < 0
       begin
       deallocate @curs
       If UPPER(@results) = 'STATS'
       begin
       raiserror(15574,-1,-1) --'Object does not have any
       statistics.'
       end
       else
       begin
       raiserror(15575,-1,-1) --'Object does not have any indexes or
       statistics.'
       end
       return (0)
       end
       -- create temp table
       CREATE TABLE #spstattab
       (
gopher.createaforum.com:70 /forums/esdbp/p/131:132: port field too long