April 17, 2012

sp_datatype_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_datatype_info(int @data_type
, tinyint @ODBCVer)

MetaData:

   
create procedure sys.sp_datatype_info
(
@data_type int = 0,
@ODBCVer tinyint = 2
)
as
declare @mintype int
declare @maxtype int

set @ODBCVer = isnull(@ODBCVer, 2)
if @ODBCVer < 3 -- includes ODBC 1.0 as well
set @ODBCVer = 2
else
set @ODBCVer = 3

if @data_type = 0
begin
select @mintype = -32768
select @maxtype = 32767
end
else
begin
select @mintype = @data_type
select @maxtype = @data_type
end

select
TYPE_NAME = v.TYPE_NAME,
DATA_TYPE = v.DATA_TYPE_28, -- for backward compatibility
PRECISION = v.PRECISION_28, -- for backward compatibility
LITERAL_PREFIX = v.LITERAL_PREFIX,
LITERAL_SUFFIX = v.LITERAL_SUFFIX,
CREATE_PARAMS = v.CREATE_PARAMS_90, -- for backward compatibility
NULLABLE = v.NULLABLE,
CASE_SENSITIVE = v.CASE_SENSITIVE_28, -- for backward compatibility
SEARCHABLE = v.SEARCHABLE,
UNSIGNED_ATTRIBUTE = v.UNSIGNED_ATTRIBUTE,
MONEY = v.MONEY,
AUTO_INCREMENT = v.AUTO_INCREMENT,
LOCAL_TYPE_NAME = v.LOCAL_TYPE_NAME,
MINIMUM_SCALE = v.MINIMUM_SCALE_90, -- for backward compatibility
MAXIMUM_SCALE = v.MAXIMUM_SCALE_90, -- for backward compatibility
SQL_DATA_TYPE = v.SQL_DATA_TYPE_28, -- for backward compatibility
SQL_DATETIME_SUB = v.SQL_DATETIME_SUB_90, -- for backward compatibility
NUM_PREC_RADIX = v.NUM_PREC_RADIX,
INTERVAL_PRECISION = v.INTERVAL_PRECISION,
USERTYPE = v.USERTYPE

from
sys.spt_datatype_info_view v

where
v.DATA_TYPE_28 between @mintype and @maxtype and
v.ODBCVer = @ODBCVer

order by 2, v.MAPPED_TYPE, 12, 11, 20

No comments:

Post a Comment

Total Pageviews