SQL SERVER隱藏系統函式sp_columns_100_rowset和sp_table_statistics2_rowset
今天突然發現2個隱藏系統函式sp_columns_100_rowset和sp_table_statistics2_rowset
sp_columns_100_rowset定義如下:
create procedure sys.sp_columns_100_rowset
(
@table_name sysname = NULL,
@table_schema sysname = NULL,
@column_name sysname = NULL
)
as
select
TABLE_CATALOG = s_cv.TABLE_CATALOG,
TABLE_SCHEMA = s_cv.TABLE_SCHEMA,
TABLE_NAME = s_cv.TABLE_NAME,
COLUMN_NAME = s_cv.COLUMN_NAME,
COLUMN_GUID = s_cv.COLUMN_GUID,
COLUMN_PROPID = s_cv.COLUMN_PROPID,
ORDINAL_POSITION = s_cv.ORDINAL_POSITION,
COLUMN_HASDEFAULT = s_cv.COLUMN_HASDEFAULT,
COLUMN_DEFAULT = s_cv.COLUMN_DEFAULT,
COLUMN_FLAGS = s_cv.COLUMN_FLAGS,
IS_NULLABLE = s_cv.IS_NULLABLE,
DATA_TYPE = s_cv.DATA_TYPE, -- Used by Katmai+ clients
TYPE_GUID = s_cv.TYPE_GUID,
CHARACTER_MAXIMUM_LENGTH= s_cv.CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH = s_cv.CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION = s_cv.NUMERIC_PRECISION,
NUMERIC_SCALE = s_cv.NUMERIC_SCALE,
DATETIME_PRECISION = s_cv.DATETIME_PRECISION,
CHARACTER_SET_CATALOG = s_cv.CHARACTER_SET_CATALOG,
CHARACTER_SET_SCHEMA = s_cv.CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME = s_cv.CHARACTER_SET_NAME,
COLLATION_CATALOG = s_cv.COLLATION_CATALOG,
COLLATION_SCHEMA = s_cv.COLLATION_SCHEMA,
COLLATION_NAME = s_cv.COLLATION_NAME,
DOMAIN_CATALOG = s_cv.DOMAIN_CATALOG,
DOMAIN_SCHEMA = s_cv.DOMAIN_SCHEMA,
DOMAIN_NAME = s_cv.DOMAIN_NAME,
DESCRIPTION = s_cv.DESCRIPTION,
COLUMN_LCID = s_cv.COLUMN_LCID,
COLUMN_COMPFLAGS = s_cv.COLUMN_COMPFLAGS,
COLUMN_SORTID = s_cv.COLUMN_SORTID,
COLUMN_TDSCOLLATION = s_cv.COLUMN_TDSCOLLATION,
IS_COMPUTED = s_cv.IS_COMPUTED,
SS_XML_SCHEMACOLLECTION_CATALOGNAME = s_cv.SS_XML_SCHEMACOLLECTION_CATALOGNAME,
SS_XML_SCHEMACOLLECTION_SCHEMANAME = s_cv.SS_XML_SCHEMACOLLECTION_SCHEMANAME,
SS_XML_SCHEMACOLLECTIONNAME = s_cv.SS_XML_SCHEMACOLLECTIONNAME,
SS_UDT_CATALOGNAME = s_cv.SS_UDT_CATALOGNAME,
SS_UDT_SCHEMANAME = s_cv.SS_UDT_SCHEMANAME,
SS_UDT_NAME = s_cv.SS_UDT_NAME,
SS_UDT_ASSEMBLY_TYPENAME= s_cv.SS_UDT_ASSEMBLY_TYPENAME,
SS_IS_SPARSE = s_cv.SS_IS_SPARSE,
SS_IS_COLUMN_SET = s_cv.SS_IS_COLUMN_SET
from
sys.spt_columns_view s_cv
where
(
(@table_schema is null and s_cv.TABLE_NAME = @table_name) or
s_cv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name))
) and
(@column_name = s_cv.COLUMN_NAME or @column_name is null)
order by 1, 2, 3, 7
是一個顯示錶列相關資訊的函式,列出了比較詳細的資訊。
sp_table_statistics2_rowset 主要用於顯示目標表的統計資訊,該函式定義如下:
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 '~~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 database_default not null,
tblschema sysname collate database_default not null,
tblname sysname collate database_default not null,
statname sysname collate database_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 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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8183550/viewspace-705837/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL SERVER 系統函式SQLServer函式
- Sql Server系列:系統函式SQLServer函式
- Sql Server函式全解(5):系統函式SQLServer函式
- Sql Server函式全解(五)之系統函式SQLServer函式
- SQL SERVER 時間和日期函式SQLServer函式
- 函式索引產生隱藏列函式索引
- Sql Server函式全解(4):日期和時間函式SQLServer函式
- Sql Server函式全解(四)日期和時間函式SQLServer函式
- Sql Server 日期函式SQLServer函式
- SQL SERVER 字串函式SQLServer字串函式
- Sql Server系列:日期和時間函式SQLServer函式
- SQL Server 裡的日期和時間函式SQLServer函式
- 【SQL基礎】T-SQL函式型別——系統函式SQL函式型別
- SQL Server常用函式整理SQLServer函式
- Sql Server系列:字串函式SQLServer字串函式
- Sql Server系列:聚合函式SQLServer函式
- Sql Server系列:排序函式SQLServer排序函式
- Sql server 分割符函式SQLServer函式
- SQL SERVER 數學函式SQLServer函式
- SQL SERVER 自定義函式SQLServer函式
- SQL Server函式總結SQLServer函式
- SQL Server CONVERT() 函式SQLServer函式
- Sql Server函式全解(1):字串函式SQLServer函式字串
- Sql Server函式全解(一)字串函式SQLServer函式字串
- 隱藏Sysbase SQL AnywhereSQL
- SQL SERVER--系統隱形殺手—阻塞與等待SQLServer
- MAC OS X系統顯示和隱藏檔案Mac
- Sql Server函式全解(2):數學函式SQLServer函式
- Mysql與Sql Server DATEDIFF函式MySqlServer函式
- Sql Server系列:自定義函式SQLServer函式
- SQL SERVER 函式中文文件SQLServer函式
- Sql Server 2005函式SQLServer函式
- 眾多開源系統使用的過濾SQL隱碼攻擊函式SQL函式
- win10系統u盤隱藏檔案怎麼取消隱藏Win10
- C++ 成員函式的過載,繼承,覆蓋和隱藏C++函式繼承
- 類成員函式的重寫,過載和隱藏的區別函式
- SQL server 表值函式 標量值函式 區別SQLServer函式
- SQL Server資料庫————模糊查詢和聚合函式SQLServer資料庫函式