(DIR) Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
 (HTM) https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
 (DIR) Return to: Scripts
       *****************************************************
       #Post#: 139--------------------------------------------------
       Statisitcs and Index Information for a table 
       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
       (
       stats_id
       stats_name
       stats_keys
       )
       -- Now check out each statistics set, figure out its keys and
       --
       the end.
       while @@fetch_status >= 0
       begin
       -- Skip columnstore indexes as they provide no stats
       if indexproperty(@objid, @indname, 'iscolumnstore') <> 1
       begin
       -- First we'll figure out what the keys are.
       select @keys = index_col(@objname, @indid, 1),
       @i = 2, @thiskey = index_col(@objname, @indid, 2)
       while (@thiskey is not null )
       begin
       select @keys = @keys + ', ' + @thiskey, @i = @i + 1
       select @thiskey = index_col(@objname, @indid, @i)
       end
       -- INSERT ROW FOR INDEX
       insert into #spstattab values (@indid, @indname, @keys)
       end
       -- Next index
       fetch @curs into @indid, @indname
       end
       deallocate @curs
       -- DISPLAY THE RESULTS
       select
       'statistics_id' = stats_id,
       'statistics_name' = stats_name,
       'statistics_keys' = stats_keys
       from #spstattab
       order by stats_name
       return (0) -- sp_lib_helpstats
       GO
       EXEC sp_MS_marksystemobject 'sp_lib_helpstats'
       GO
       *****************************************************