June 8, 2012

sp_special_columns (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_special_columns(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier
, char @col_type
, char @scope
, char @nullable
, int @ODBCVer)

MetaData:

   
create procedure sys.sp_special_columns
(
@table_name sysname, -- Wildcard pattern matching IS NOT supported.
@table_owner sysname = null, -- Wildcard pattern matching IS NOT supported.
@table_qualifier sysname = null,
@col_type char(1) = 'R',
@scope char(1) = 'T', -- Not used in the search criteria.
@nullable char(1) = 'U',
@ODBCVer int = 2
)
as
declare @index_id int
declare @table_id int
-- quotename() returns up to 258 chars
declare @full_table_name nvarchar(517) -- 258 + 1 + 258
declare @scopeout smallint

if @col_type not in ('R','V') or @col_type is null
begin
raiserror (15251,-1,-1,'col_type','''R'' or ''V''')
return
end

if @scope = 'C'
select @scopeout = 0
else if @scope = 'T'
select @scopeout = 1
else
begin
raiserror (15251,-1,-1,'scope','''C'' or ''T''')
return
end

if @nullable not in ('U','O') or @nullable is null
begin
raiserror (15251,-1,-1,'nullable','''U'' or ''O''')
return
end

if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename(@table_name)
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename(@table_owner)
end
else
begin
select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name)
end
end

-- Get Object ID
select @table_id = object_id(@full_table_name)

if (@table_id is null) -- Return empty resultset if table doesn't exist
begin
select
SCOPE = convert(smallint,0),
COLUMN_NAME = convert(sysname,NULL),
DATA_TYPE = convert(smallint,0),
TYPE_NAME = convert(sysname,NULL),
"PRECISION" = convert(int,0),
"LENGTH" = convert(int,0),
SCALE = convert(smallint,0),
PSEUDO_COLUMN = convert(smallint,0)
where
1=0
return
end

if @col_type = 'V'
begin -- if ROWVER, just run that query
select
SCOPE = convert(smallint,NULL),
COLUMN_NAME = convert(sysname,c.name),
DATA_TYPE = convert(smallint, -2),
TYPE_NAME = t.name,
"PRECISION" = convert(int,8),
"LENGTH" = convert(int,8),
SCALE = convert(smallint, NULL),
PSEUDO_COLUMN = convert(smallint,1)
from
sys.types t,
sys.all_columns c
where
c.object_id = @table_id and
t.name = 'timestamp' and
t.user_type_id = c.system_type_id and
t.user_type_id = c.user_type_id
return
end

-- ROWID, now find the id of the 'best' index for this table

if @nullable = 'O' -- Don't include any indexes that contain nullable columns.
select
@index_id = MIN(x.index_id)
from
sys.indexes x,
sys.all_columns c,
sys.all_columns c2
where
x.is_unique = 1 and -- If Unique Index
x.object_id = @table_id and
x.index_id > 0 and -- Eliminate Table Row
c.object_id = x.object_id and
c2.object_id = c.object_id and
c.name = index_col(@table_name,x.index_id,c2.column_id)
group by
x.index_id HAVING SUM(convert (int, c.is_nullable)) = 0
else -- Include indexes that are partially nullable.
select
@index_id = MIN(x.index_id)
from
sys.indexes x
where
x.is_unique = 1 and -- If Unique Index
x.object_id = @table_id and
x.index_id > 0 -- Eliminate Table Row

select
SCOPE = @scopeout,
COLUMN_NAME = convert(sysname,c.name),
DATA_TYPE = convert(smallint,
case
when (d.ss_dtype = 240) then -- CLR UDT
-4
when (d.ss_dtype = 241) then -- XML
-10
when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
-1
when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
-10
when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
-4
when d.ss_dtype IN (40,41,42,43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
-9 -- SQL_WVARCHAR
else
d.DATA_TYPE
end),
TYPE_NAME = convert(sysname,case
when (t.system_type_id = 240 or t.user_type_id > 255) then t.name
else d.TYPE_NAME collate catalog_default
end),
"PRECISION" = convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision -- FLOAT/REAL
when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
2147483647
else OdbcPrec(c.system_type_id,c.max_length,c.precision)
end),
"LENGTH" = convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
2147483647
when d.ss_dtype IN (40,41,42,43) then
OdbcPrec(c.system_type_id,c.max_length,c.precision)*2 -- DATE/TIME/DATETIME2/DATETIMEOFFSET
else
isnull(d.length, c.max_length)
end),
SCALE = convert(smallint,case
when d.ss_dtype IN (40,41,42,43) then null -- DATE/TIME/DATETIME2/DATETIMEOFFSET
else OdbcScale(c.system_type_id,c.scale)
end),
PSEUDO_COLUMN = convert(smallint,1)
from
sys.columns c inner join
sys.indexes x on
(
x.object_id = c.object_id and
x.object_id = @table_id and
x.index_id = @index_id
) inner join
sys.types t on
(
t.user_type_id = c.user_type_id
) inner join
sys.spt_datatype_info d on
(
d.ss_dtype = c.system_type_id and
d.ODBCVer = @ODBCVer and
d.AUTO_INCREMENT = c.is_identity
) inner join
sys.columns c2 on -- Self-join to generate list of index columns and to extract datatype names.
(
INDEX_COL(@full_table_name,@index_id,c2.column_id) = c.name and
c2.object_id = x.object_id
)

sp_showcolv (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_showcolv(varbinary @colv)

MetaData:

   
create procedure sys.sp_showcolv
@colv varbinary(2953)
as
set nocount on

declare @result table (colidx smallint, replnick binary(6), version int)

declare @i smallint
set @i= 0
while (@i < DATALENGTH(@colv)/12)
begin
declare @colidx smallint
set @colidx= cast(substring(@colv, @i*12+2,1) as smallint)
set @colidx= @colidx*256 + cast(substring(@colv, @i*12+1,1) as smallint)

declare @replnick binary(6)
set @replnick= (select substring(@colv, @i*12+3, 6))

declare @version int
set @version= cast(substring(@colv, @i*12+12,1) as int)
set @version= @version*256 + cast(substring(@colv, @i*12+11,1) as int)
set @version= @version*256 + cast(substring(@colv, @i*12+10,1) as int)
set @version= @version*256 + cast(substring(@colv, @i*12+9,1) as int)

insert into @result(colidx, replnick, version) select @colidx+1, @replnick, @version
set @i= @i+1
end
select * from @result order by colidx

sp_setOraclepackageversion (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_setOraclepackageversion(nvarchar @publisher)

MetaData:

   
CREATE PROCEDURE sys.sp_setOraclepackageversion
(
@publisher sysname
)
AS
BEGIN
set nocount on
DECLARE @cmd nvarchar(4000)
,@retcode int
,@publisher_type sysname

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo
@publisher = @publisher,
@rpcheader = @cmd OUTPUT,
@publisher_type = @publisher_type OUTPUT
IF @retcode <> 0
RETURN (@retcode)

-- Reject unsupported publisher types
IF @publisher_type NOT IN (N'ORACLE', N'ORACLE GATEWAY')
BEGIN
RAISERROR (21645, 16, -1, @publisher_type)
RETURN (1)
END

SELECT @publisher = UPPER(@publisher)
,@cmd = @cmd + N'sys.sp_MSrepl_setOraclepackageversion'

EXEC @retcode = @cmd
@publisher

RETURN (@retcode)
END

sp_setsubscriptionxactseqno (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_setsubscriptionxactseqno(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, varbinary @xact_seqno)

MetaData:

 CREATE PROCEDURE sys.sp_setsubscriptionxactseqno  
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@xact_seqno varbinary(16)
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @retcode int,
@current_xact_seqno varbinary(16),
@sub_streams_count int

-- Security Check
EXEC @retcode = sys.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

-- Parameter Checks : @publisher
IF @publisher IS NULL
BEGIN
-- The parameter @publisher cannot be NULL.
RAISERROR (14043, 16, -1, '@publisher', 'sp_setsubscriptionxactseqno')
RETURN 1
END

EXEC @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

-- Parameter Checks : @publisher_db
IF @publisher_db IS NULL
BEGIN
-- The parameter @publisher_db cannot be NULL.
RAISERROR (14043, 16, -1, '@publisher_db', 'sp_setsubscriptionxactseqno')
RETURN 1
END

EXEC @retcode = sys.sp_validname @publisher_db
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

-- Parameter Check: @publication
IF @publication IS NULL
BEGIN
-- The parameter @publisher cannot be NULL.
RAISERROR (14043, 16, -1, '@publication', 'sp_setsubscriptionxactseqno')
RETURN 1
END

EXEC @retcode = sys.sp_validname @publication
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

-- Parameter Check: @xact_seqno
IF @xact_seqno IS NULL
BEGIN
-- The parameter @xact_seqno cannot be NULL.
RAISERROR (14043, 16, -1, '@xact_seqno', 'sp_setsubscriptionxactseqno')
RETURN 1
END

-- check to make sure we are at a subscription database
IF OBJECT_ID('MSreplication_subscriptions', 'U') IS NULL
BEGIN
-- The subscription on the Subscriber does not exist.
RAISERROR (20017, 16, -1)
RETURN 1
END

-- check if publication is independent agent if not @publication must be all
IF UPPER(@publication) != N'ALL'
AND NOT EXISTS (SELECT *
FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication = @publication)
AND EXISTS (SELECT *
FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication = N''
AND independent_agent = 0)
BEGIN
-- The subscription to publication '%s' was not found but a shared agent does exist. To specify a subscription to a publication that is replicated via a shared agent specify '%s' for the publication name.
RAISERROR(22579, 16, -1, @publication, N'ALL')
RETURN 1
END

-- Peer-To-Peer subscriptions do not support this feature
IF OBJECT_ID(N'MSpeer_lsns', 'U') is not NULL
BEGIN
IF EXISTS(SELECT *
FROM MSpeer_lsns
WHERE originator = UPPER(@publisher)
AND originator_db = @publisher_db
AND originator_publication = @publication)
BEGIN
-- Peer-To-Peer publications do not support 'sp_setsubscriptionxactseqno'. Please change the '@publication' parameter value.
RAISERROR(20646, 16, -1, '''sp_setsubscriptionxactseqno''', '@publication')
RETURN 1
END
END

-- if there are mstreams ensure that the last commit xact_seqno
-- match for all streams before allowing this process to cont...
IF (SELECT COUNT(*)
FROM MSreplication_subscriptions msrs1
JOIN MSreplication_subscriptions msrs2
ON UPPER(msrs1.publisher) = UPPER(msrs2.publisher)
AND msrs1.publisher_db = msrs2.publisher_db
AND msrs1.publication = msrs2.publication
AND CAST(SUBSTRING(msrs1.transaction_timestamp, 1, 15) AS binary(15)) != CAST(SUBSTRING(msrs2.transaction_timestamp, 1, 15) AS binary(15))
WHERE UPPER(msrs1.publisher) = UPPER(@publisher)
AND msrs1.publisher_db = @publisher_db
AND (msrs1.publication = @publication
OR (msrs1.publication = N''
AND msrs1.independent_agent = 0
AND UPPER(@publication) = N'ALL'))) > 0
BEGIN
-- The distribution agent must be run in single subscription stream mode prior to resetting the subscription xact_seqno.
RAISERROR (21836, 16, -1)
RETURN 1
END

SELECT @current_xact_seqno = transaction_timestamp
FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND (publication = @publication
OR (publication = N''
AND independent_agent = 0
AND UPPER(@publication) = N'ALL'))

SELECT @sub_streams_count = @@ROWCOUNT

IF @current_xact_seqno IS NULL
BEGIN
-- There is no subscription on Publisher '@publisher', publisher database '@publisher_db', publication '@publication'.
RAISERROR(14135, 11, -1, @publisher, @publisher_db, @publication)
RETURN 1
END

UPDATE MSreplication_subscriptions
SET transaction_timestamp = CAST(@xact_seqno AS binary(15)) + CAST(SUBSTRING(transaction_timestamp, 16, 1) AS binary(1))
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND (publication = @publication
OR (publication = N''
AND independent_agent = 0
AND UPPER(@publication) = N'ALL'))
IF @@ERROR <> 0
RETURN 1


SELECT @current_xact_seqno AS "ORIGINAL XACT_SEQNO",
@xact_seqno AS "UPDATED XACT_SEQNO",
@sub_streams_count AS "SUBSCRIPTION STREAM COUNT"

RETURN 0
END

sp_scriptupdproc (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_scriptupdproc(int @artid
, tinyint @mode
, tinyint @publishertype
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_scriptupdproc   
(
@artid int -- id of the article we are processing
,@mode tinyint = 1 -- 1 = static scripting, 2 = dynamic scripting
,@publishertype tinyint=1 -- 1 = mssqlserver, 2 = heterogeneous
,@publisher sysname=NULL -- May only be non-NULL if @publishertype = 2
)
as
begin
declare @retcode int
--
-- security check
--
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
return (1)
end
--
-- call core function
--
exec @retcode = sys.sp_scriptupdproccore
@artid = @artid
,@format = 1 -- CALL format
,@mode = @mode
,@publishertype = @publishertype
,@publisher = @publisher
return @retcode
end

sp_server_info (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_server_info(int @attribute_id)

MetaData:

   
create procedure sys.sp_server_info
(
@attribute_id int = null
)
as
select * from
(
select
*
from
sys.spt_server_info

union

select
ATTRIBUTE_ID = 16,
ATTRIBUTE_NAME = convert(varchar(60), 'IDENTIFIER_CASE') collate catalog_default,
ATTRIBUTE_VALUE = convert(varchar(255),
case when 'a' <> 'A' then 'SENSITIVE' else 'MIXED' end) collate catalog_default

union

select
ATTRIBUTE_ID = 18,
ATTRIBUTE_NAME = convert(varchar(60), 'COLLATION_SEQ') collate catalog_default,
ATTRIBUTE_VALUE = convert(varchar(255),
'charset=' + convert(varchar(255), ServerProperty('sqlcharsetname')) +
case when 0 = convert(int, ServerProperty('sqlsortorder'))
then ' collation=' + isnull(convert(varchar(255), ServerProperty('collation')), ' ')
else ' sort_order=' + convert(varchar(64), ServerProperty('sqlsortordername')) +
' charset_num=' + rtrim(convert(char(4), convert(int, ServerProperty('sqlcharset')))) +
' sort_order_num=' + rtrim(convert(char(4), convert(int, ServerProperty('sqlsortorder')))) end)
collate catalog_default
) as t
where @attribute_id is null or @attribute_id = attribute_id

sp_special_columns_100 (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_special_columns_100(nvarchar @table_name
, nvarchar @table_owner
, nvarchar @table_qualifier
, char @col_type
, char @scope
, char @nullable
, int @ODBCVer)

MetaData:

   
create procedure sys.sp_special_columns_100
(
@table_name sysname, -- Wildcard pattern matching IS NOT supported.
@table_owner sysname = null, -- Wildcard pattern matching IS NOT supported.
@table_qualifier sysname = null,
@col_type char(1) = 'R',
@scope char(1) = 'T', -- Not used in the search criteria.
@nullable char(1) = 'U',
@ODBCVer int = 2
)
as
declare @index_id int
declare @table_id int
-- quotename() returns up to 258 chars
declare @full_table_name nvarchar(517) -- 258 + 1 + 258
declare @scopeout smallint

if @col_type not in ('R','V') or @col_type is null
begin
raiserror (15251,-1,-1,'col_type','''R'' or ''V''')
return
end

if @scope = 'C'
select @scopeout = 0
else if @scope = 'T'
select @scopeout = 1
else
begin
raiserror (15251,-1,-1,'scope','''C'' or ''T''')
return
end

if @nullable not in ('U','O') or @nullable is null
begin
raiserror (15251,-1,-1,'nullable','''U'' or ''O''')
return
end

if @table_qualifier is not null
begin
if db_name() <> @table_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
if @table_owner is null
begin -- If unqualified table name
select @full_table_name = quotename(@table_name)
end
else
begin -- Qualified table name
if @table_owner = ''
begin -- If empty owner name
select @full_table_name = quotename(@table_owner)
end
else
begin
select @full_table_name = quotename(@table_owner) + '.' + quotename(@table_name)
end
end

-- Get Object ID
select @table_id = object_id(@full_table_name)
if (@table_id is null) -- Return empty resultset if table doesn't exist
begin
select
SCOPE = convert(smallint,0),
COLUMN_NAME = convert(sysname,NULL),
DATA_TYPE = convert(smallint,0),
TYPE_NAME = convert(sysname,NULL),
"PRECISION" = convert(int,0),
"LENGTH" = convert(int,0),
SCALE = convert(smallint,0),
PSEUDO_COLUMN = convert(smallint,0)
where
1=0
return
end

if @col_type = 'V'
begin -- if ROWVER, just run that query
select
SCOPE = convert(smallint,NULL),
COLUMN_NAME = convert(sysname,c.name),
DATA_TYPE = convert(smallint, -2),
TYPE_NAME = t.name,
"PRECISION" = convert(int,8),
"LENGTH" = convert(int,8),
SCALE = convert(smallint, NULL),
PSEUDO_COLUMN = convert(smallint,1)
from
sys.types t,
sys.all_columns c
where
c.object_id = @table_id and
t.name = 'timestamp' and
t.user_type_id = c.system_type_id and
t.user_type_id = c.user_type_id
return
end

-- ROWID, now find the id of the 'best' index for this table

if @nullable = 'O' -- Don't include any indexes that contain nullable columns.
select
@index_id = MIN(x.index_id)
from
sys.indexes x,
sys.all_columns c,
sys.all_columns c2
where
x.is_unique = 1 and -- If Unique Index
x.object_id = @table_id and
x.index_id > 0 and -- Eliminate Table Row
c.object_id = x.object_id and
c2.object_id = c.object_id and
c.name = index_col(@table_name,x.index_id,c2.column_id)
group by
x.index_id HAVING SUM(convert (int, c.is_nullable)) = 0
else -- Include indexes that are partially nullable.
select
@index_id = MIN(x.index_id)
from
sys.indexes x
where
x.is_unique = 1 and -- If Unique Index
x.object_id = @table_id and
x.index_id > 0 -- Eliminate Table Row

select
SCOPE = @scopeout,
COLUMN_NAME = convert(sysname,c.name),
DATA_TYPE = d.DATA_TYPE,
TYPE_NAME = convert(sysname,case
when (t.system_type_id = 240 or t.user_type_id > 255) then t.name
else d.TYPE_NAME collate catalog_default
end),
"PRECISION" = convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision -- FLOAT/REAL
when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => same precision as varbinary(max)
0
else OdbcPrec(c.system_type_id,c.max_length,c.precision)
end),
"LENGTH" = convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then -- decimal/numeric types
OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => same length as varbinary(max)
0
else
isnull(d.length, c.max_length)
end),
SCALE = convert(smallint, OdbcScale(c.system_type_id,c.scale)),
PSEUDO_COLUMN = convert(smallint,1)
from
sys.columns c inner join
sys.indexes x on
(
x.object_id = c.object_id and
x.object_id = @table_id and
x.index_id = @index_id
) inner join
sys.types t on
(
t.user_type_id = c.user_type_id
) inner join
sys.spt_datatype_info d on
(
d.ss_dtype = c.system_type_id and
d.ODBCVer = @ODBCVer and
d.AUTO_INCREMENT = c.is_identity
) inner join
sys.columns c2 on -- Self-join to generate list of index columns and to extract datatype names.
(
INDEX_COL(@full_table_name,@index_id,c2.column_id) = c.name and
c2.object_id = x.object_id
)

sp_sparse_columns_100_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_sparse_columns_100_rowset(nvarchar @table_name
, nvarchar @table_schema
, nvarchar @column_name
, int @schema_type)

MetaData:

   
create procedure sys.sp_sparse_columns_100_rowset
(
@table_name sysname = NULL,
@table_schema sysname = NULL,
@column_name sysname = NULL,
@schema_type int = 1
)
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_sparse_columns_view s_cv

where
(@table_name is null or s_cv.TABLE_NAME = @table_name) and
(@table_schema is null or schema_id(@table_schema) = s_cv.schema_id) and
(@column_name = s_cv.COLUMN_NAME or @column_name is null) and
(
(@schema_type=1) or
(@schema_type=2 and (s_cv.SS_IS_SPARSE=1 and objectproperty(s_cv.object_id, 'tablehascolumnset')=1))
)

order by 1, 2, 3, 7

sp_spaceused (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_spaceused(nvarchar @objname
, varchar @updateusage)

MetaData:

 create procedure sys.sp_spaceused -- - 2003/05/19 14:00  
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
as

declare @id int -- The object id that takes up space
,@type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint

--
-- Check to see if user wants usages updated.
--

if @updateusage is not null
begin
select @updateusage=lower(@updateusage)

if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
--
-- Check to see that the objname is local.
--
if @objname IS NOT NULL
begin

select @dbname = parsename(@objname, 3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if @dbname is null
select @dbname = db_name()

--
-- Try to find the object.
--
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname)

-- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 -- ITT_ServiceQueue

--
-- Does the object exist?
--
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

-- Is it a table, view or queue?
IF @type NOT IN ('U ','S ','V ','SQ','IT')
begin
raiserror(15234,-1,-1)
return (1)
end
end

--
-- Update usages if user specified to do so.
--

if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end

set nocount on

--
-- If @id is null, then we want summary data.
--
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles

select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) Then 0
When a.type <> 1 and p.index_id < 2 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id

-- unallocated space could not be negative --
select
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2) + ' MB'),
'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2) + ' MB')

--
-- Now calculate the summary data.
-- reserved: sum(reserved) where indid in (0, 1, 255)
-- data: sum(data_pages) + sum(text_used)
-- index: sum(used) where indid in (0, 1, 255) - data
-- unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
--
select
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end

--
-- We want a particular object.
--
else
begin
--
-- Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
* For non-clustered indices they are counted towards the index pages
--
SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE 0
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;

--
-- Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
--
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,207,211,212,213,214,215,216,221,222)) > 0
BEGIN
--
-- Now calculate the summary data. Row counts in these internal tables don't
-- contribute towards row count of original table.
--
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222) AND p.object_id = it.object_id;
END

SELECT
name = OBJECT_NAME (@id),
rows = convert (char(20), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')

end


return (0) -- sp_spaceused

sp_showrowreplicainfo (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_showrowreplicainfo(nvarchar @ownername
, nvarchar @tablename
, uniqueidentifier @rowguid
, nvarchar @show)

MetaData:

 create procedure sys.sp_showrowreplicainfo  
(@ownername sysname = NULL, @tablename sysname = NULL, @rowguid uniqueidentifier, @show nvarchar(20) = 'both')
as
set nocount on

-- Security check
if (1 <> is_member('db_owner') and
not exists (select * from dbo.sysmergearticles a join dbo.MSmerge_contents c
on a.nickname=c.tablenick
where c.rowguid=@rowguid and
1 = {fn ISPALUSER(a.pubid)}))
begin
RAISERROR (15247, 11, -1)
return (1)
end

-- some constants
-- this stored proc is for debugging purposes, thus no need for localizing them
declare @dbname sysname
select @dbname= db_name()

declare @missingcolname sysname
set @missingcolname= '<Missing column>'

declare @anonymousname sysname
set @anonymousname= '<Anonymous subscriber>'

declare @unknownname sysname
set @unknownname= '<Unknown server name>'

declare @mergename sysname
set @mergename= '<Merge nickname>'

declare @toohighlineageversion nvarchar(128)
set @toohighlineageversion= 'Problem found: Version is higher than the one of the first entry.'

declare @toohighcolvversion nvarchar(128)
set @toohighcolvversion= 'Problem found: Version is higher than highest version in lineage.'

declare @navalue sysname
set @navalue= '<n/a>'

declare @unknownvalue nvarchar(9)
set @unknownvalue= '<unknown>'

declare @qualified_tablename nvarchar(300)

if @tablename is NULL
begin
set @tablename= (select top 1 object_name(objid) from dbo.sysmergearticles where
nickname = (select tablenick from dbo.MSmerge_contents where rowguid = @rowguid))
end

if @tablename is NULL
begin
set @tablename= (select top 1 object_name(objid) from dbo.sysmergearticles where
nickname = (select tablenick from dbo.MSmerge_tombstone where rowguid = @rowguid))
end

if @tablename is null
begin
raiserror (20513, 16, 1, @dbname)
return 1
end

-- check whether given table exists
if not exists (select * from sys.objects where type = 'U' and name = @tablename)
begin
raiserror (20507, 16, 1, @tablename, @dbname)
return 1
end

if @ownername is not null
begin
select @qualified_tablename= quotename(@ownername) + '.' + quotename(@tablename)

-- check whether table belongs to the given owner
if object_id(@qualified_tablename, 'U') is NULL
begin
raiserror (20507, 16, 1, @qualified_tablename, @dbname)
return 1
end
end
else
select @qualified_tablename= @tablename

-- get tableid and tablenick from tablename
declare @tableid int
declare @tablenick int
set @tableid= object_id(@qualified_tablename)
select @tablenick = (select top 1 nickname from dbo.sysmergearticles where objid = @tableid)
if @tablenick is null
begin
raiserror (20027, 16, 1, @tablename)
return 1
end

-- check whether there is an entry for this row in either MSmerge_contents or MSmerge_tombstone
declare @incontents int
declare @lineage varbinary(311)
select @lineage= lineage from dbo.MSmerge_contents where rowguid = @rowguid
if @lineage is not null
begin
set @incontents= 1
end
else
begin
select @lineage= lineage from dbo.MSmerge_tombstone where rowguid = @rowguid
if @lineage is not null
begin
set @incontents= 0
end
else
begin
raiserror(21511,10,1)
return 0
end
end

-- create temporary table for information about lineage and colv entries
create table #results ( type nchar(7) null,
rowversion_table nchar(17) null,
server_name sysname null,
[db_name] sysname null,
db_nickname binary(6) not null,
current_state nvarchar(9) null,
colid smallint null,
colname sysname null,
version int not null,
comment nvarchar(255) null,
position smallint null)

-- insert lineage information into temptable
insert into #results (position, db_nickname, version) exec sys.sp_showlineage @lineage=@lineage
update #results set type= 'lineage'
if (@incontents = 1)
begin
update #results set rowversion_table = 'MSmerge_contents'
end
else
begin
update #results set rowversion_table = 'MSmerge_tombstone'
end

if (@incontents = 1) and (lower(@show collate SQL_Latin1_General_CP1_CS_AS) in ('both', 'columns'))
begin
-- insert colv information into temptable
declare @colv varbinary(2953)
select @colv= colv1 from dbo.MSmerge_contents where rowguid=@rowguid
if @colv is not null
begin
insert into #results (colid, db_nickname, version) exec sys.sp_showcolv @colv=@colv
update #results set type= 'colv' where type is null

-- translate colids into column names
if (select top 1 missing_col_count from dbo.sysmergearticles where nickname = @tablenick) = 0
begin
-- no missing cols: position of entries in colv correspond to colid in dbo.sysmergearticles
update #results set colname = c.name from sys.columns c where #results.colid = c.column_id and c.object_id = @tableid
end
else
begin
-- missing cols: colv has entries for columns that do not exist in this db
declare @colname sysname
declare @ismissing int
declare @missingsofar int
declare @colid int
declare @missingcols varbinary(128)
select @missingcols= (select top 1 missing_cols from dbo.sysmergearticles where nickname = @tablenick)
set @missingsofar= 0
select @colid= (select min(colid) from #results where colname is null and colid is not null)
while @colid is not null
begin
-- is this column missing?
exec @ismissing= sys.sp_MStestbit @missingcols, @colid
if @ismissing <> 0
begin
update #results set colname= @missingcolname, server_name= @navalue, [db_name]= @navalue
where colid = @colid
set @missingsofar= @missingsofar + 1
end
else
begin
select @colname= (select name from sys.columns where object_id = @tableid and column_id = (@colid - @missingsofar))
update #results set colname= @colname where colid = @colid
end

select @colid= (select min(colid) from #results where colname is null and colid is not null)
end
end
end
end

-- transform null comment to empty strings
update #results set comment= ''

-- translate nicknames in temptable into real db names; set server names, too
declare @subid uniqueidentifier
declare @servername sysname
declare @replnick binary(6)
declare @mergenickmin binary(1)
declare @mergenickmax binary(1)
declare @mergenicktail binary(5)

set @mergenickmin= 0x01
set @mergenickmax= 0x32 -- there are not more than 50 relevant entries in a lineage
set @mergenicktail= 0x0000000000

update #results set [db_name]= @mergename, server_name= @navalue where
substring(db_nickname,1,1) >= @mergenickmin and
substring(db_nickname,1,1) <= @mergenickmax
and substring(db_nickname,2,5) = @mergenicktail

select @replnick= (select top 1 db_nickname from #results where [db_name] is null)
while @replnick is not null
begin
select @subid= (select top 1 s.subid
from dbo.sysmergesubscriptions s where replnickname = @replnick)

select @dbname= (select [db_name] from dbo.sysmergesubscriptions where subid = @subid)
if @dbname is null
begin
set @dbname=@anonymousname
set @servername= @unknownname
end
else
begin
select @servername = subscriber_server from dbo.sysmergesubscriptions where subid = @subid
end

update #results set [db_name]= @dbname, server_name= @servername
where db_nickname = @replnick and [db_name] is null
select @replnick= (select top 1 db_nickname from #results where [db_name] is null)
end

-- indicate which lineage entries stand for the current state of the row
declare @firstreplnick binary(6)
select @firstreplnick= (select db_nickname from #results where type = 'lineage' and position = 1)
if (
substring(@firstreplnick,1,1) < @mergenickmin or
substring(@firstreplnick,1,1) > @mergenickmax or
substring(@firstreplnick,2,5) <> @mergenicktail
)
begin
update #results set current_state= 'y' where type = 'lineage' and position = 1
end
else if @firstreplnick = 0x010000000000
begin
-- lineage format of SQL2000 and earlier
update #results set current_state= @navalue where type = 'lineage' and position = 1
update #results set current_state= 'y' where type = 'lineage' and position = 2
update #results set current_state= @unknownvalue where type = 'lineage' and position > 2
end
else
begin
declare @cRelevantEntries int
set @cRelevantEntries= cast(substring(@firstreplnick,1,1) as int)
update #results set current_state= @navalue where type = 'lineage' and position = 1
update #results set current_state= 'y' where type = 'lineage' and position > 1 and position <= @cRelevantEntries + 1
end
update #results set current_state= 'n' where current_state is null

-- record lineage versions that are higher than the version in the first slot
update #results set comment= @toohighlineageversion
where type = 'lineage' and
position <> 1 and
version > (select version from #results where position = 1)

-- record colv versions that are higher than highest lineage version
update #results set comment= @toohighcolvversion
where type = 'colv' and
not exists (select version from #results r where type = 'lineage' and r.version >= #results.version)

-- deliver results
if lower(@show collate SQL_Latin1_General_CP1_CS_AS) in ('both', 'row')
begin
select server_name, [db_name], db_nickname, version, current_state, rowversion_table, comment from #results where type = 'lineage' order by position
end

if lower(@show collate SQL_Latin1_General_CP1_CS_AS) in ('both', 'columns')
begin
select server_name, db_name, db_nickname, version, colname, comment from #results where type = 'colv' order by position
end

drop table #results
return 0

Total Pageviews