June 11, 2012

sp_table_statistics2_rowset (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_table_statistics2_rowset(nvarchar @table_name
, nvarchar @table_schema
, nvarchar @table_catalog
, nvarchar @stat_name
, nvarchar @stat_schema
, nvarchar @stat_catalog)

MetaData:

   
create procedure sys.sp_table_statistics2_rowset
(
@table_name sysname,
@table_schema sysname = null,
@table_catalog sysname = null,
@stat_name sysname = null,
@stat_schema sysname = null,
@stat_catalog sysname = null
)
as
begin
set nocount on

-- check in parameters
if ((@table_catalog is not null) and (db_name() <> @table_catalog)
or (@stat_catalog is not null) and (db_name() <> @stat_catalog))
begin -- If qualifier doesn't match current database
RAISERROR(20001, 16, 1); -- '~~Rush_5~~'
return
end

declare @objid int, @schid int

select @schid = schema_id(@table_schema)
select @objid = object_id(
isnull(quotename(@table_catalog ), '') + '.' +
isnull(quotename(@table_schema), '') + '.' +
quotename(@table_name))

-- - create temp table
create table #spstattab
(
tblcatalog sysname collate catalog_default not null,
tblschema sysname collate catalog_default not null,
tblname sysname collate catalog_default not null,
statname sysname collate catalog_default not null
)

-- I don't like this copy&paste stuff, but here it boosts performance by 50%.
if @table_name is not null
begin
insert into #spstattab
select
tblcatalog = db_name(),
tblschema = schema_name(o.schema_id),
tblname = o.name,
statname = st.name
from
sys.all_objects o inner join
sys.stats st on
(
@objid = o.object_id and
st.object_id = o.object_id and
o.type in ('U') and
(@stat_name is null or @stat_name = st.name) and
(@stat_schema is null or @stat_schema = schema_name(o.schema_id))
)
end
else
begin
insert into #spstattab
select
tblcatalog = db_name(),
tblschema = schema_name(o.schema_id),
tblname = o.name,
statname = st.name
from
sys.all_objects o inner join
sys.stats st on
(
(@table_schema is null or @schid = o.schema_id) and
st.object_id = o.object_id and
o.type in ('U') and
(@stat_name is null or @stat_name = st.name) and
(@stat_schema is null or @stat_schema = schema_name(o.schema_id))
)
end

-- Fast forward CURSOR OVER THE temp table, apply the order clause here but not
-- in the code above used for filling temp table with data.
declare ff_csr cursor local fast_forward for
select tblcatalog, tblschema, tblname, statname from #spstattab order by 1,2,3,4

declare
@tblcatalog sysname,
@tblschema sysname,
@tblname sysname,
@statname sysname,
@qtbl nvarchar(4000),
@rowsetcount int

set @rowsetcount = 0

open ff_csr
fetch ff_csr into @tblcatalog, @tblschema, @tblname, @statname
while @@fetch_status >= 0
begin
set @rowsetcount = @rowsetcount + 1
set @qtbl = quotename(@tblcatalog) + '.' + quotename(@tblschema) + '.' + quotename(@tblname)

--
-- This is the real stuff.
--
dbcc show_statistics(@qtbl, @statname) with stat_header join density_vector

fetch ff_csr into @tblcatalog, @tblschema, @tblname, @statname
end
close ff_csr
deallocate ff_csr

-- - drop temp table
drop table #spstattab
if @rowsetcount = 0
begin
select
TABLE_CATALOG = convert(sysname, null),
TABLE_SCHEMA = convert(sysname, null),
TABLE_NAME = convert(sysname, null),
STATISTICS_CATALOG = convert(sysname, null),
STATISTICS_SCHEMA = convert(sysname, null),
STATISTICS_NAME = convert(sysname, null),
STATISTICS_TYPE = convert(smallint,0),
COLUMN_NAME = convert(sysname, null),
COLUMN_GUID = convert(uniqueidentifier, null),
COLUMN_PROPID = convert(int, null),
ORDINAL_POSITION = convert(int, null),
SAMPLE_PCT = convert(smallint, null),
LAST_UPDATE_TIME = convert(datetime, null),
NO_OF_RANGES = convert(int, null),
COLUMN_CARDINALITY = convert(bigint, null),
TUPLE_CARDINALITY = convert(bigint, null),
TABLE_CARDINALITY = convert(bigint, null),
AVG_COLUMN_LENGTH = convert(int, null)
where 1=0
end

set nocount off
end

No comments:

Post a Comment

Total Pageviews