June 11, 2012

sp_statistics_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_statistics_rowset(nvarchar @table_name
, nvarchar @table_schema)

MetaData:

   
create procedure sys.sp_statistics_rowset
(
@table_name sysname,
@table_schema sysname = null
)
as
select
TABLE_CATALOG = db_name(),
TABLE_SCHEMA = schema_name(o.schema_id),
TABLE_NAME = o.name,
CARDINALITY = convert (int,
(-- Multiple partitions could correspond to one index.
select sum(rows) from sys.partitions s_p
where x.object_id = s_p.object_id and x.index_id = s_p.index_id
)
)
from
sys.all_objects o,
sys.indexes x
where
(
(@table_schema is null and o.name = @table_name) or
object_id(quotename(@table_schema) + '.' + quotename(@table_name)) = o.object_id
) and
o.type in ('U') and
x.object_id = o.object_id and
x.index_id in (0,1) -- If there are no indexes then table stats are in a row with indid =0
order by 2, 3

No comments:

Post a Comment

Total Pageviews