June 11, 2012

sp_sproc_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_sproc_columns_100(nvarchar @procedure_name
, nvarchar @procedure_owner
, nvarchar @procedure_qualifier
, nvarchar @column_name
, int @ODBCVer
, bit @fUsePattern)

MetaData:

   
create procedure sys.sp_sproc_columns_100
(
@procedure_name nvarchar(390) = '%',
@procedure_owner nvarchar(384) = null,
@procedure_qualifier sysname = null,
@column_name nvarchar(384) = null,
@ODBCVer int = 2,
@fUsePattern bit = 1 -- To allow users to explicitly disable all pattern matching.
)
as
declare @group_num_lower smallint
declare @group_num_upper smallint
declare @semi_position int
declare @full_procedure_name nvarchar(775) -- 390 + 1 + 384
declare @procedure_id int

if @procedure_qualifier is not null
begin
if db_name() <> @procedure_qualifier
begin
if @procedure_qualifier = ''
begin
-- in this case, we need to return an empty result set
-- because the user has requested a database with an empty name
select @procedure_name = ''
select @procedure_owner = ''
end
else
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end
end

if @procedure_name is null
begin -- If procedure name not supplied, match all
select @procedure_name = '%'
end

-- first we need to extract the procedure group number, if one exists
select @semi_position = charindex(';',@procedure_name)
if (@semi_position > 0)
begin -- If group number separator (;) found
select @group_num_lower = convert(int,substring(@procedure_name, @semi_position + 1, 2))
select @group_num_upper = @group_num_lower
select @procedure_name = substring(@procedure_name, 1, @semi_position -1)
end
else
begin -- No group separator, so default to all groups
select @group_num_lower = 0
select @group_num_upper = 32767
end

if @procedure_owner is null
begin -- If unqualified procedure name
select @full_procedure_name = quotename(@procedure_name)
end
else
begin -- Qualified procedure name
if @procedure_owner = ''
begin -- If empty owner name
select @full_procedure_name = quotename(@procedure_owner)
end
else
begin
select @full_procedure_name = quotename(@procedure_owner) + '.' + quotename(@procedure_name)
end
end

select @procedure_id = object_id(@full_procedure_name)


if (@fUsePattern = 1) -- Does the user want it?
begin
if ((isnull(charindex('%', @full_procedure_name),0) = 0) and
(isnull(charindex('[', @procedure_name),0) = 0) and
(isnull(charindex('[', @procedure_owner),0) = 0) and
(isnull(charindex('_', @full_procedure_name),0) = 0) and
-- VSTS 60696, pattern matching is also supported on @column_name
(isnull(charindex('%', @column_name),0) = 0) and
(isnull(charindex('_', @column_name),0) = 0) and
(isnull(charindex('[', @column_name),0) = 0) and
(@procedure_id is not null))
begin
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
end

if @fUsePattern = 0
begin
-- -- Debug output, do not remove it.
print '-- -- -- -- -- -- *'
print 'No pattern matching.'
print isnull(convert(sysname, @procedure_id), '@procedure_id = null')
print isnull(@full_procedure_name, '@full_procedure_name = null')
print isnull(@procedure_name, '@procedure_name = null')
print isnull(@procedure_owner, '@procedure_owner = null')
print isnull(@procedure_qualifier, '@procedure_qualifier = null')
print isnull(@column_name, '@column_name = null')
print isnull(@ODBCVer, '@ODBCVer = null')
print '-- -- -- -- -- -- *'
--

--
-- FAST!
--
select
PROCEDURE_QUALIFIER = s_scov.PROCEDURE_QUALIFIER,
PROCEDURE_OWNER = s_scov.PROCEDURE_OWNER,
PROCEDURE_NAME = convert(nvarchar(134),
s_scov.PROCEDURE_NAME +';'+ ltrim(str(s_scov.procedure_number,5))),
COLUMN_NAME = s_scov.COLUMN_NAME,
COLUMN_TYPE = s_scov.COLUMN_TYPE,
DATA_TYPE = s_scov.DATA_TYPE,
TYPE_NAME = s_scov.TYPE_NAME,
"PRECISION" = s_scov.PRECISION,
"LENGTH" = s_scov.LENGTH,
SCALE = s_scov.SCALE,
RADIX = s_scov.RADIX,
NULLABLE = s_scov.NULLABLE,
REMARKS = s_scov.REMARKS,
COLUMN_DEF = s_scov.COLUMN_DEF,
SQL_DATA_TYPE = s_scov.SQL_DATA_TYPE,
SQL_DATETIME_SUB = s_scov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_scov.CHAR_OCTET_LENGTH,
ORDINAL_POSITION = s_scov.ORDINAL_POSITION,
IS_NULLABLE = s_scov.IS_NULLABLE,
SS_TYPE_CATALOG_NAME = s_scov.SS_TYPE_CATALOG_NAME,
SS_TYPE_SCHEMA_NAME = s_scov.SS_TYPE_SCHEMA_NAME,
SS_UDT_CATALOG_NAME = s_scov.SS_UDT_CATALOG_NAME,
SS_UDT_SCHEMA_NAME = s_scov.SS_UDT_SCHEMA_NAME,
SS_UDT_ASSEMBLY_TYPE_NAME = s_scov.SS_UDT_ASSEMBLY_TYPE_NAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_scov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_scov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
SS_XML_SCHEMACOLLECTION_NAME = s_scov.SS_XML_SCHEMACOLLECTION_NAME,
SS_DATA_TYPE = s_scov.SS_DATA_TYPE
from
sys.spt_sproc_columns_odbc_view s_scov
where
s_scov.object_id = @procedure_id and
s_scov.odbcver = @ODBCVer and
(@column_name is null or s_scov.COLUMN_NAME = @column_name) and
(s_scov.procedure_number between @group_num_lower and @group_num_upper)

UNION ALL

select -- procedure return value & table valued functions
PROCEDURE_QUALIFIER = s_scrvov.PROCEDURE_QUALIFIER,
PROCEDURE_OWNER = s_scrvov.PROCEDURE_OWNER,
PROCEDURE_NAME = convert(nvarchar(134),
s_scrvov.PROCEDURE_NAME +';'+ ltrim(str(s_scrvov.procedure_number,5))),
COLUMN_NAME = s_scrvov.COLUMN_NAME,
COLUMN_TYPE = s_scrvov.COLUMN_TYPE,
DATA_TYPE = s_scrvov.DATA_TYPE,
TYPE_NAME = s_scrvov.TYPE_NAME,
"PRECISION" = s_scrvov.PRECISION,
"LENGTH" = s_scrvov.LENGTH,
SCALE = s_scrvov.SCALE,
RADIX = s_scrvov.RADIX,
NULLABLE = s_scrvov.NULLABLE,
REMARKS = s_scrvov.REMARKS,
COLUMN_DEF = s_scrvov.COLUMN_DEF,
SQL_DATA_TYPE = s_scrvov.SQL_DATA_TYPE,
SQL_DATETIME_SUB = s_scrvov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_scrvov.CHAR_OCTET_LENGTH,
ORDINAL_POSITION = s_scrvov.ORDINAL_POSITION,
IS_NULLABLE = s_scrvov.IS_NULLABLE,
SS_TYPE_CATALOG_NAME = s_scrvov.SS_TYPE_CATALOG_NAME,
SS_TYPE_SCHEMA_NAME = s_scrvov.SS_TYPE_SCHEMA_NAME,
SS_UDT_CATALOG_NAME = s_scrvov.SS_UDT_CATALOG_NAME,
SS_UDT_SCHEMA_NAME = s_scrvov.SS_UDT_SCHEMA_NAME,
SS_UDT_ASSEMBLY_TYPE_NAME = s_scrvov.SS_UDT_ASSEMBLY_TYPE_NAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
SS_XML_SCHEMACOLLECTION_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_NAME,
SS_DATA_TYPE = s_scrvov.SS_DATA_TYPE
from
sys.spt_sproc_columns_return_values_odbc_view s_scrvov
where
s_scrvov.object_id = @procedure_id and
(
@column_name is null or
@column_name = '@RETURN_VALUE' or
@column_name = '@TABLE_RETURN_VALUE'
) and
(s_scrvov.procedure_number between @group_num_lower and @group_num_upper)

order by 1, 2, 3, 18
end
else
begin
-- -- Debug output, do not remove it.
print '-- -- -- -- -- -- *'
print 'THERE IS pattern matching!'
print isnull(convert(sysname, @procedure_id), '@procedure_id = null')
print isnull(@full_procedure_name, '@full_procedure_name = null')
print isnull(@procedure_name, '@procedure_name = null')
print isnull(@procedure_owner, '@procedure_owner = null')
print isnull(@procedure_qualifier, '@procedure_qualifier = null')
print isnull(@column_name, '@column_name = null')
print isnull(@ODBCVer, '@ODBCVer = null')
print '-- -- -- -- -- -- *'
--

--
-- SLOOOOOW!
--
if @procedure_owner is null
select @procedure_owner = '%'

select
PROCEDURE_QUALIFIER = s_scov.PROCEDURE_QUALIFIER,
PROCEDURE_OWNER = s_scov.PROCEDURE_OWNER,
PROCEDURE_NAME = convert(nvarchar(134),
s_scov.PROCEDURE_NAME +';'+ ltrim(str(s_scov.procedure_number,5))),
COLUMN_NAME = s_scov.COLUMN_NAME,
COLUMN_TYPE = s_scov.COLUMN_TYPE,
DATA_TYPE = s_scov.DATA_TYPE,
TYPE_NAME = s_scov.TYPE_NAME,
"PRECISION" = s_scov.PRECISION,
"LENGTH" = s_scov.LENGTH,
SCALE = s_scov.SCALE,
RADIX = s_scov.RADIX,
NULLABLE = s_scov.NULLABLE,
REMARKS = s_scov.REMARKS,
COLUMN_DEF = s_scov.COLUMN_DEF,
SQL_DATA_TYPE = s_scov.SQL_DATA_TYPE,
SQL_DATETIME_SUB = s_scov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_scov.CHAR_OCTET_LENGTH,
ORDINAL_POSITION = s_scov.ORDINAL_POSITION,
IS_NULLABLE = s_scov.IS_NULLABLE,
SS_TYPE_CATALOG_NAME = s_scov.SS_TYPE_CATALOG_NAME,
SS_TYPE_SCHEMA_NAME = s_scov.SS_TYPE_SCHEMA_NAME,
SS_UDT_CATALOG_NAME = s_scov.SS_UDT_CATALOG_NAME,
SS_UDT_SCHEMA_NAME = s_scov.SS_UDT_SCHEMA_NAME,
SS_UDT_ASSEMBLY_TYPE_NAME = s_scov.SS_UDT_ASSEMBLY_TYPE_NAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_scov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_scov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
SS_XML_SCHEMACOLLECTION_NAME = s_scov.SS_XML_SCHEMACOLLECTION_NAME,
SS_DATA_TYPE = s_scov.SS_DATA_TYPE
from
sys.spt_sproc_columns_odbc_view s_scov
where
s_scov.PROCEDURE_NAME like @procedure_name and
(@column_name is null or s_scov.COLUMN_NAME like @column_name) and
s_scov.odbcver = @ODBCVer and
(s_scov.procedure_number between @group_num_lower and @group_num_upper) and
schema_name(s_scov.schema_id) like @procedure_owner

UNION ALL

select -- procedure return value & table valued functions
PROCEDURE_QUALIFIER = s_scrvov.PROCEDURE_QUALIFIER,
PROCEDURE_OWNER = s_scrvov.PROCEDURE_OWNER,
PROCEDURE_NAME = convert(nvarchar(134),
s_scrvov.PROCEDURE_NAME +';'+ ltrim(str(s_scrvov.procedure_number,5))),
COLUMN_NAME = s_scrvov.COLUMN_NAME,
COLUMN_TYPE = s_scrvov.COLUMN_TYPE,
DATA_TYPE = s_scrvov.DATA_TYPE,
TYPE_NAME = s_scrvov.TYPE_NAME,
"PRECISION" = s_scrvov.PRECISION,
"LENGTH" = s_scrvov.LENGTH,
SCALE = s_scrvov.SCALE,
RADIX = s_scrvov.RADIX,
NULLABLE = s_scrvov.NULLABLE,
REMARKS = s_scrvov.REMARKS,
COLUMN_DEF = s_scrvov.COLUMN_DEF,
SQL_DATA_TYPE = s_scrvov.SQL_DATA_TYPE,
SQL_DATETIME_SUB = s_scrvov.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = s_scrvov.CHAR_OCTET_LENGTH,
ORDINAL_POSITION = s_scrvov.ORDINAL_POSITION,
IS_NULLABLE = s_scrvov.IS_NULLABLE,
SS_TYPE_CATALOG_NAME = s_scrvov.SS_TYPE_CATALOG_NAME,
SS_TYPE_SCHEMA_NAME = s_scrvov.SS_TYPE_SCHEMA_NAME,
SS_UDT_CATALOG_NAME = s_scrvov.SS_UDT_CATALOG_NAME,
SS_UDT_SCHEMA_NAME = s_scrvov.SS_UDT_SCHEMA_NAME,
SS_UDT_ASSEMBLY_TYPE_NAME = s_scrvov.SS_UDT_ASSEMBLY_TYPE_NAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
SS_XML_SCHEMACOLLECTION_NAME = s_scrvov.SS_XML_SCHEMACOLLECTION_NAME,
SS_DATA_TYPE = s_scrvov.SS_DATA_TYPE
from
sys.spt_sproc_columns_return_values_odbc_view s_scrvov
where
s_scrvov.PROCEDURE_NAME like @procedure_name and
schema_name(s_scrvov.schema_id) like @procedure_owner and
(
@column_name is null or
@column_name = '@RETURN_VALUE' or
@column_name = '@TABLE_RETURN_VALUE'
) and
(s_scrvov.procedure_number between @group_num_lower and @group_num_upper)

order by 1, 2, 3, 18
end

No comments:

Post a Comment

Total Pageviews