Can we modify the sp_datatype_info stored procedure to return datatypes >=3 only in sql server 2005 Is it possible with SQL Server 2000.
If it is not possible to modify/drop the procedure, is it possible to change the name resolution logic to be able to call the procedure in another (for example dbo) schema
Any other possible way to achieve this
modify procedure sys.sp_datatype_info
Vikram
My objective is to modify sp_datatype_info to return datatypes>-7. Is there any way to achieve this
Any kind of possible workaround.
Thanks in advance ...
Kuldeep
rmelnikov
Anthony Stevens
You can view the sp code by right clicking the sp in management studio and selecting modify
here is the code for sp_datatype_info. . .
modify this and execute it. . .
it will be in the schema of the current context
get the original by executing 'sys.sp_datatype_info'
get yours by executing 'myschema.sp_datatype_info'
Caveat Emptor: Dont know if there are any side effects.
=============================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create procedure [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 = convert(sysname,
case
when t.user_type_id > 255 then t.name
else d.TYPE_NAME collate database_default
end),
DATA_TYPE = convert(smallint, -- backward-compatible ODBC types
case
when (d.ss_dtype = 240) then -- CLR UDT
-4
when (d.ss_dtype = 241) then -- XML
-10
else
d.DATA_TYPE
end),
"PRECISION" = convert(int,
case
when (d.DATA_TYPE in (6,7,-150))
then d.data_precision -- FLOAT/REAL/sql_variant
when (d.ss_dtype = 241)
then 1073741823 -- XML is the same as ntext for pre-Yukon clients
when d.ss_dtype in (106,108) and t.user_type_id <= 255
then @@max_precision -- DECIMAL/NUMERIC
else OdbcPrec(t.system_type_id, t.max_length, t.precision)
end),
LITERAL_PREFIX = d.LITERAL_PREFIX,
LITERAL_SUFFIX = d.LITERAL_SUFFIX,
CREATE_PARAMS = e.CREATE_PARAMS,
NULLABLE = convert(smallint,
case
when d.AUTO_INCREMENT = 1 then 0 -- IDENTITY
else TypeProperty (t.name, 'AllowsNull')
end),
CASE_SENSITIVE = convert (smallint,
case
when d.ss_dtype = 241 then 1 -- SQL_SS_XML is always case sensitive
when d.DATA_TYPE in (1, -1, 12, -8, -9, -10) then -- char/text/varchar/nchar/nvarchar/ntext
case
when 'a' <> 'A' then 1
else 0
end
else 0
end),
SEARCHABLE = d.SEARCHABLE,
UNSIGNED_ATTRIBUTE = d.UNSIGNED_ATTRIBUTE,
MONEY = d.MONEY,
AUTO_INCREMENT = convert(smallint,
case -- money/float/real/tinyint/smallmoney/smallint/int/decimal/numeric/bigint
when d.ss_dtype in (60, 62, 59, 122, 48, 52, 56, 106, 108, 127)
then d.AUTO_INCREMENT
else null -- not applicable for other types
end),
LOCAL_TYPE_NAME = convert(sysname,
case
when t.user_type_id > 255 then t.name
else d.LOCAL_TYPE_NAME collate database_default
end),
MINIMUM_SCALE = convert(smallint,
case
when d.ss_dtype in (106,108) and t.user_type_id > 255
then TypeProperty (t.name, 'Scale')
else d.numeric_scale
end),
MAXIMUM_SCALE = convert(smallint,
case
when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 and t.user_type_id <= 255
then @@max_precision -- DECIMAL/NUMERIC
when (d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1) or (d.ss_dtype in (98, 104))
then 0 -- DECIMAL/NUMERIC IDENTITY/SQL_VARIANT/BIT
else TypeProperty (t.name, 'Scale')
end),
SQL_DATA_TYPE = convert(smallint, -- backward-compatible ODBC types
case
when (d.ss_dtype = 240) then -- CLR UDT
-4
when (d.ss_dtype = 241) then -- XML
-10
else
d.SQL_DATA_TYPE
end),
SQL_DATETIME_SUB = d.SQL_DATETIME_SUB,
NUM_PREC_RADIX = convert(int,d.RADIX),
INTERVAL_PRECISION = convert(smallint,NULL),
USERTYPE = convert(smallint, typeproperty(t.name, 'oldusertype'))
from
sys.spt_datatype_info d inner join
sys.types t on
(
-- We have to return all system types and SQL UDTs (as Shiloh does),
-- but no CLR UDTs.
d.ss_dtype = t.system_type_id and
(
d.ss_usertype = t.user_type_id or -- system UDTs & regular types
(
t.system_type_id <> 240 and t.user_type_id > 255 and -- SQL UDTs
d.ss_dtype <> 240 and d.ss_usertype = d.ss_dtype
)
)
)
left outer join
sys.spt_datatype_info_ext e on
(
t.user_type_id = e.user_type and
d.AUTO_INCREMENT = e.AUTO_INCREMENT
)
where
d.DATA_TYPE between @mintype and @maxtype and
d.ODBCVer = @ODBCVer and
(t.user_type_id <= 255 or d.AUTO_INCREMENT = 0)
order by 2, 12, 11, 20
Daniel Stroebel
My objective is to modify sp_datatype_info to return datatypes>-7. Is there any way to achiee this
Any kind of possible workaround.
Thanks in advance ...
Kuldeep