June 11, 2012

sp_table_type_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_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

Total Pageviews