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_90(nvarchar @procedure_name, nvarchar @procedure_owner
, nvarchar @procedure_qualifier
, nvarchar @column_name
, int @ODBCVer
, bit @fUsePattern)
MetaData:
create procedure sys.sp_sproc_columns_90 ( @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_90, TYPE_NAME = s_scov.TYPE_NAME, "PRECISION" = s_scov.PRECISION, "LENGTH" = s_scov.LENGTH_90, SCALE = s_scov.SCALE_90, 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_90, SQL_DATETIME_SUB = s_scov.SQL_DATETIME_SUB_90, CHAR_OCTET_LENGTH = s_scov.CHAR_OCTET_LENGTH, ORDINAL_POSITION = s_scov.ORDINAL_POSITION, IS_NULLABLE = s_scov.IS_NULLABLE, 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_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_90, TYPE_NAME = s_scov.TYPE_NAME, "PRECISION" = s_scov.PRECISION, "LENGTH" = s_scov.LENGTH_90, SCALE = s_scov.SCALE_90, 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_90, SQL_DATETIME_SUB = s_scov.SQL_DATETIME_SUB_90, CHAR_OCTET_LENGTH = s_scov.CHAR_OCTET_LENGTH, ORDINAL_POSITION = s_scov.ORDINAL_POSITION, IS_NULLABLE = s_scov.IS_NULLABLE, 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_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