cs_surfman
Messages postés46Date d'inscriptionsamedi 1 mars 2003StatutMembreDernière intervention16 septembre 2008
-
12 sept. 2008 à 10:59
cs_surfman
Messages postés46Date d'inscriptionsamedi 1 mars 2003StatutMembreDernière intervention16 septembre 2008
-
16 sept. 2008 à 12:50
Bonjour à tous,
J'ai un soucis, la procédure stockée système sp_databases n'existe plus, ainsi qu'une situé dessous ou dessus qui est sp_datainfo je ne sais plus exactement le nom (si quelqu'un pouvait me redonner ce nom ainsi que le code de cette procédure...).
mon soucis est le suivant j'ai recrée sp_databases seulement en tant que procédure stockée et je voudrais le mettre en tant que procédure stockée système, comment faire ?
cs_surfman
Messages postés46Date d'inscriptionsamedi 1 mars 2003StatutMembreDernière intervention16 septembre 2008 16 sept. 2008 à 12:50
Arf je me suis trompé pour SP_DATATYPE_INFO, le bon code est :
"
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* Procedure for 8.0 server */
CREATE proc [sp_datatype_info] (@data_type int 0, @ODBCVer tinyint 2)
as
declare @mintype int
declare @maxtype int
if @ODBCVer <> 3
select @ODBCVer = 2
if @data_type = 0
begin
select @mintype = -32768
select @maxtype = 32767
end
else
begin
select @mintype = @data_type
select @maxtype = @data_type
end
select
convert(sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
d.DATA_TYPE,
convert(int,case
when d.DATA_TYPE in (6,7,-150) then d.data_precision /* FLOAT/REAL/sql_variant*/
when d.ss_dtype in (55,63,106,108) and
t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
else OdbcPrec(t.xtype, t.length, t.xprec)
end) "PRECISION",
d.LITERAL_PREFIX,
d.LITERAL_SUFFIX,
e.CREATE_PARAMS,
convert(smallint,case
when d.AUTO_INCREMENT = 1 then 0 /* IDENTITY*/
else TypeProperty (t.name, 'AllowsNull')
end) NULLABLE,
d.CASE_SENSITIVE,
d.SEARCHABLE,
d.UNSIGNED_ATTRIBUTE,
d.MONEY,
d.AUTO_INCREMENT,
convert(sysname,case
when t.xusertype > 255 then t.name
else d.LOCAL_TYPE_NAME collate database_default
end) LOCAL_TYPE_NAME,
convert(smallint,case
when d.ss_dtype in (55,63,106,108) and t.xusertype > 255 then TypeProperty (t.name, 'Scale')
else d.numeric_scale
end) MINIMUM_SCALE,
convert(smallint,case
when d.ss_dtype in (55,63,106,108) and d.AUTO_INCREMENT = 0 and t.xusertype <= 255 then @@max_precision /* DECIMAL/NUMERIC */
when d.ss_dtype in (55,63,106,108) and d.AUTO_INCREMENT = 1 then 0 /* DECIMAL/NUMERIC IDENTITY*/
else TypeProperty (t.name, 'Scale')
end) MAXIMUM_SCALE,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
NUM_PREC_RADIX = convert(int,d.RADIX),
INTERVAL_PRECISION = convert(smallint,NULL),
USERTYPE = t.usertype
from master.dbo.spt_datatype_info d
INNER JOIN systypes t on d.ss_dtype = t.xtype
LEFT OUTER JOIN master.dbo.spt_datatype_info_ext e on
t.xusertype = e.user_type
and isnull(d.AUTO_INCREMENT,0) = e.AUTO_INCREMENT
where
d.DATA_TYPE between @mintype and @maxtype
and (d.ODBCVer is null or d.ODBCVer = @ODBCVer)
and (t.xusertype <= 255 or
isnull(d.AUTO_INCREMENT,0) = 0)
order by 2, 12, 11,
case
when t.usertype=18 then 255
else t.usertype
end
"
Le code fourni plus haut et pour SQL 2005, or j'utilise SQL 2000
crn_c21
Messages postés302Date d'inscriptionsamedi 24 janvier 2004StatutMembreDernière intervention 4 février 2011 12 sept. 2008 à 14:16
Voici la proc sp_datatype_info parc ontre je ne sais comment la transformer en procédure système :
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 = 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
Vous n’avez pas trouvé la réponse que vous recherchez ?