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_table_type_columns_100(nvarchar @table_name, nvarchar @table_owner
, nvarchar @table_qualifier
, nvarchar @column_name
, int @ODBCVer
, bit @fUsePattern)
MetaData:
create procedure sys.sp_table_type_columns_100 ( @table_name nvarchar(384), @table_owner nvarchar(384) = null, @table_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 @full_table_name nvarchar(769) -- 384 + 1 + 384 declare @table_id int if (@ODBCVer is null) or (@ODBCVer <> 3) select @ODBCVer = 2 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 -- "ALL" is represented by NULL value. if @table_name = '%' select @table_name = null if @table_owner = '%' select @table_owner = null if @table_qualifier = '%' select @table_qualifier = null if @column_name = '%' select @column_name = null -- Empty string means nothing, so use invalid identifier. -- A quoted space will never match any object name. if @table_owner = '' select @table_owner = ' ' select @full_table_name = isnull(quotename(@table_owner), '') + '.' + isnull(quotename(@table_name), '') select @table_id = tt.type_table_object_id from sys.table_types tt where tt.name = @table_name and schema_name(tt.schema_id) = @table_owner if (@fUsePattern = 1) -- Does the user want it? begin if ((isnull(charindex('%', @full_table_name),0) = 0) and (isnull(charindex('_', @full_table_name),0) = 0) and (isnull(charindex('[', @table_name),0) = 0) and (isnull(charindex('[', @table_owner),0) = 0) and (isnull(charindex('%', @column_name),0) = 0) and (isnull(charindex('_', @column_name),0) = 0) and (@table_id <> 0)) 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 @fUsePattern print isnull(convert(sysname, @table_id), '@table_id = null') print isnull(@full_table_name, '@full_table_name = null') print isnull(@table_owner, '@table_owner = null') print isnull(@table_name, '@table_name = null') print isnull(@column_name, '@column_name = null') print '-- -- -- -- -- -- *' -- select TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER, TABLE_OWNER = convert(sysname,schema_name(tt.schema_id)), TABLE_NAME = convert(sysname,tt.name), COLUMN_NAME = s_cov.COLUMN_NAME, DATA_TYPE = s_cov.DATA_TYPE, TYPE_NAME = s_cov.TYPE_NAME, "PRECISION" = s_cov.PRECISION, "LENGTH" = s_cov.LENGTH, SCALE = s_cov.SCALE, RADIX = s_cov.RADIX, NULLABLE = s_cov.NULLABLE, REMARKS = s_cov.REMARKS, COLUMN_DEF = s_cov.COLUMN_DEF, SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE, SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB, CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH, ORDINAL_POSITION = s_cov.ORDINAL_POSITION, IS_NULLABLE = s_cov.IS_NULLABLE, SS_IS_SPARSE = convert(smallint, 0), SS_IS_COLUMN_SET = convert(smallint, 0), SS_IS_COMPUTED = s_cov.SS_IS_COMPUTED, SS_IS_IDENTITY = s_cov.SS_IS_IDENTITY, SS_UDT_CATALOG_NAME = s_cov.SS_UDT_CATALOG_NAME, SS_UDT_SCHEMA_NAME = s_cov.SS_UDT_SCHEMA_NAME, SS_UDT_ASSEMBLY_TYPE_NAME = s_cov.SS_UDT_ASSEMBLY_TYPE_NAME, SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_cov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME, SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_cov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME, SS_XML_SCHEMACOLLECTION_NAME = s_cov.SS_XML_SCHEMACOLLECTION_NAME, SS_DATA_TYPE = s_cov.SS_DATA_TYPE from sys.spt_columns_odbc_view s_cov inner join sys.table_types tt on ( s_cov.object_id = tt.type_table_object_id ) where s_cov.object_id = @table_id and (@column_name is null or s_cov.COLUMN_NAME = @column_name) and s_cov.ODBCVER = @ODBCVer order by 17 end else begin -- -- Debug output, do not remove it. print '-- -- -- -- -- -- *' print 'THERE IS pattern matching!' print @fUsePattern print isnull(convert(sysname, @table_id), '@table_id = null') print isnull(@full_table_name, '@full_table_name = null') print isnull(@table_owner, '@table_owner = null') print isnull(@table_name, '@table_name = null') print isnull(@column_name, '@column_name = null') print '-- -- -- -- -- -- *' -- select TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER, TABLE_OWNER = convert(sysname,schema_name(tt.schema_id)), TABLE_NAME = convert(sysname,tt.name), COLUMN_NAME = s_cov.COLUMN_NAME, DATA_TYPE = s_cov.DATA_TYPE, TYPE_NAME = s_cov.TYPE_NAME, "PRECISION" = s_cov.PRECISION, "LENGTH" = s_cov.LENGTH, SCALE = s_cov.SCALE, RADIX = s_cov.RADIX, NULLABLE = s_cov.NULLABLE, REMARKS = s_cov.REMARKS, COLUMN_DEF = s_cov.COLUMN_DEF, SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE, SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB, CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH, ORDINAL_POSITION = s_cov.ORDINAL_POSITION, IS_NULLABLE = s_cov.IS_NULLABLE, SS_IS_SPARSE = convert(smallint, 0), SS_IS_COLUMN_SET = convert(smallint, 0), SS_IS_COMPUTED = s_cov.SS_IS_COMPUTED, SS_IS_IDENTITY = s_cov.SS_IS_IDENTITY, SS_UDT_CATALOG_NAME = s_cov.SS_UDT_CATALOG_NAME, SS_UDT_SCHEMA_NAME = s_cov.SS_UDT_SCHEMA_NAME, SS_UDT_ASSEMBLY_TYPE_NAME = s_cov.SS_UDT_ASSEMBLY_TYPE_NAME, SS_XML_SCHEMACOLLECTION_CATALOG_NAME = s_cov.SS_XML_SCHEMACOLLECTION_CATALOG_NAME, SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = s_cov.SS_XML_SCHEMACOLLECTION_SCHEMA_NAME, SS_XML_SCHEMACOLLECTION_NAME = s_cov.SS_XML_SCHEMACOLLECTION_NAME, SS_DATA_TYPE = s_cov.SS_DATA_TYPE from sys.spt_columns_odbc_view s_cov inner join sys.table_types tt on ( s_cov.object_id = tt.type_table_object_id ) where s_cov.ODBCVER = @ODBCVer and (@table_name is null or tt.name like @table_name) and (@table_owner is null or schema_name(tt.SCHEMA_ID) like @table_owner) and (@column_name is null or s_cov.COLUMN_NAME like @column_name) order by 2, 3, 17 end
No comments:
Post a Comment