(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 *****************************************************