Usuário com melhor resposta
Extrair Tipo e Tamanho do campo em um string

Pergunta
-
Pessoal, gostaria de saber se existe uma query que retorne o tipo e o tamnho do campo ex.:
select FuncaoFieldType(nomedocampo) from nomedatabela
select FuncaoFieldSize(nomedocampo) from nomedatabela
retornando respectivamente String e 255
desde já agradeço
Respostas
-
john,
Tudo bem o Junior lhe passou uma strored procedure de sistema do SQl Server 2000, por default ela esta dentro do MASTER , agora você pode personalizar essa stored procedure de acordo com suas necessidades isso utilizando até outro nome para a stored procedure Abaixo a mesma completa adpte de acordo com sua necessidade mas ela ira funcionar somente para o sql server para ver outras schemas de tabelas em outros banco vai ter que fazer uma para cada um:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOcreate proc sp_helpPersonalizada
@objname nvarchar(776) = NULL -- object name we're after
as
-- PRELIMINARY
set nocount on
declare @dbname sysname-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
declare @no varchar(35), @yes varchar(35), @none varchar(35)
select @no = name from master.dbo.spt_values where type = 'B' and number = 0
select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
select @none = name from master.dbo.spt_values where type = 'B' and number = 2-- If no @objname given, give a little info about all objects.
if @objname is null
begin
-- DISPLAY ALL SYSOBJECTS --
select
'Name' = o.name,
'Owner' = user_name(uid),
'Object_type' = substring(v.name,5,31)
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
order by Object_type desc, Name ascprint ' '
-- DISPLAY ALL USER TYPES
select
'User_type' = name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' = TypeProperty(name, 'precision'),
'Scale' = TypeProperty(name, 'scale'),
'Nullable' = case when TypeProperty(name, 'AllowsNull') = 1
then @yes else @no end,
'Default_name' = isnull(object_name(tdefault), @none),
'Rule_name' = isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype > 256
order by namereturn(0)
end-- Make sure the @objname is local to the current database.
select @dbname = parsename(@objname,3)if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return(1)
end-- @objname must be either sysobjects or systypes: first look in sysobjects
declare @objid int
declare @sysobj_type char(2)
select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @objid = xusertype from systypes where name = @objname-- IF NOT IN SYSTYPES, GIVE UP
if @objid is null
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return(1)
end-- DATA TYPE HELP (prec/scale only valid for numerics)
select
'Type_name' = name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' = TypeProperty(name, 'precision'),
'Scale' = TypeProperty(name, 'scale'),
'Nullable' = case when allownulls=1 then @yes else @no end,
'Default_name' = isnull(object_name(tdefault), @none),
'Rule_name' = isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype = @objidreturn(0)
end-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
select
'Name' = o.name,
'Owner' = user_name(uid),
'Type' = substring(v.name,5,31),
'Created_datetime' = o.crdate
from sysobjects o, master.dbo.spt_values v
where o.id = @objid and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'print ' '
-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
declare @numtypes nvarchar(80)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'-- INFO FOR EACH COLUMN
print ' '
select
'Column_name' = name,
'Type' = type_name(xusertype),
'Computed' = case when iscomputed = 0 then @no else @yes end,
'Length' = convert(int, length),
'Prec' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(id, name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),OdbcScale(xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then @no else @yes end,
'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim')
when 1 then @no
when 0 then @yes
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When status & 0x20 = 0 Then @no
Else @yes END,
'Collation' = collation
from syscolumns where id = @objid and number = 0 order by colid-- IDENTITY COLUMN?
if @sysobj_type in ('S ','U ','V ','TF')
begin
print ' '
declare @colname sysname
select @colname = name from syscolumns where id = @objid
and colstat & 1 = 1
select
'Identity' = isnull(@colname,'No identity column defined.'),
'Seed' = ident_seed(@objname),
'Increment' = ident_incr(@objname),
'Not For Replication' = ColumnProperty(@objid, @colname, 'IsIDNotForRepl')
-- ROWGUIDCOL?
print ' '
select @colname = null
select @colname = name from syscolumns where id = @objid and number = 0
and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')
end
end-- DISPLAY PROC PARAMS
if @sysobj_type in ('P ') --RF too?
begin
-- ANY PARAMS FOR THIS PROC?
if exists (select id from syscolumns where id = @objid)
begin
-- INFO ON PROC PARAMS
print ' '
select
'Parameter_name' = name,
'Type' = type_name(xusertype),
'Length' = length,
'Prec' = case when type_name(xtype) = 'uniqueidentifier' then xprec
else OdbcPrec(xtype, length, xprec) end,
'Scale' = OdbcScale(xtype,xscale),
'Param_order' = colid,
'Collation' = collationfrom syscolumns where id = @objid
end
end-- DISPLAY TABLE INDEXES & CONSTRAINTS
if @sysobj_type in ('S ','U ')
begin
print ' '
execute sp_objectfilegroup @objid
print ' '
execute sp_helpindex @objname
print ' '
execute sp_helpconstraint @objname,'nomsg'
if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0
begin
raiserror(15647,-1,-1) -- 'No views with schemabinding reference this table.'
end
else
begin
select distinct 'Table is referenced by views' = obj.name from sysobjects obj, sysdepends deps
where obj.xtype ='V' and obj.id = deps.id and deps.depid = @objid
and deps.deptype = 1 group by obj.nameend
end
else if @sysobj_type in ('V ')
begin
-- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID
print ' '
raiserror(15469,-1,-1) -- No constraints defined
print ' '
raiserror(15470,-1,-1) --'No foreign keys reference this table.'
execute sp_helpindex @objname
endreturn (0) -- sp_help
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOEspero ter ajudado.
Todas as Respostas
-
-
Prezado Junior, se eu entendi bem, eu teria de criar uma stored procedure no banco, me corrija se eu estiver enganado.
neste caso, para o software q estou desenvolvendo, não seria o ideal pois, o aplicativo abrirá qualquer tipo de banco para fazer uma importação.
mas será que não existe algo assim que venha diretamente de uma string como um "count, avg ou max" fazem por exemplo?
abraços
-
john,
Tudo bem o Junior lhe passou uma strored procedure de sistema do SQl Server 2000, por default ela esta dentro do MASTER , agora você pode personalizar essa stored procedure de acordo com suas necessidades isso utilizando até outro nome para a stored procedure Abaixo a mesma completa adpte de acordo com sua necessidade mas ela ira funcionar somente para o sql server para ver outras schemas de tabelas em outros banco vai ter que fazer uma para cada um:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GOcreate proc sp_helpPersonalizada
@objname nvarchar(776) = NULL -- object name we're after
as
-- PRELIMINARY
set nocount on
declare @dbname sysname-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
declare @no varchar(35), @yes varchar(35), @none varchar(35)
select @no = name from master.dbo.spt_values where type = 'B' and number = 0
select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
select @none = name from master.dbo.spt_values where type = 'B' and number = 2-- If no @objname given, give a little info about all objects.
if @objname is null
begin
-- DISPLAY ALL SYSOBJECTS --
select
'Name' = o.name,
'Owner' = user_name(uid),
'Object_type' = substring(v.name,5,31)
from sysobjects o, master.dbo.spt_values v
where o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
order by Object_type desc, Name ascprint ' '
-- DISPLAY ALL USER TYPES
select
'User_type' = name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' = TypeProperty(name, 'precision'),
'Scale' = TypeProperty(name, 'scale'),
'Nullable' = case when TypeProperty(name, 'AllowsNull') = 1
then @yes else @no end,
'Default_name' = isnull(object_name(tdefault), @none),
'Rule_name' = isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype > 256
order by namereturn(0)
end-- Make sure the @objname is local to the current database.
select @dbname = parsename(@objname,3)if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return(1)
end-- @objname must be either sysobjects or systypes: first look in sysobjects
declare @objid int
declare @sysobj_type char(2)
select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@objname)-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
if @objid is null
begin
-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
select @objid = xusertype from systypes where name = @objname-- IF NOT IN SYSTYPES, GIVE UP
if @objid is null
begin
select @dbname=db_name()
raiserror(15009,-1,-1,@objname,@dbname)
return(1)
end-- DATA TYPE HELP (prec/scale only valid for numerics)
select
'Type_name' = name,
'Storage_type' = type_name(xtype),
'Length' = length,
'Prec' = TypeProperty(name, 'precision'),
'Scale' = TypeProperty(name, 'scale'),
'Nullable' = case when allownulls=1 then @yes else @no end,
'Default_name' = isnull(object_name(tdefault), @none),
'Rule_name' = isnull(object_name(domain), @none),
'Collation' = collation
from systypes
where xusertype = @objidreturn(0)
end-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO
select
'Name' = o.name,
'Owner' = user_name(uid),
'Type' = substring(v.name,5,31),
'Created_datetime' = o.crdate
from sysobjects o, master.dbo.spt_values v
where o.id = @objid and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'print ' '
-- DISPLAY COLUMN IF TABLE / VIEW
if @sysobj_type in ('S ','U ','V ','TF','IF')
begin-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
declare @numtypes nvarchar(80)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'-- INFO FOR EACH COLUMN
print ' '
select
'Column_name' = name,
'Type' = type_name(xusertype),
'Computed' = case when iscomputed = 0 then @no else @yes end,
'Length' = convert(int, length),
'Prec' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(id, name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(xtype), @numtypes) > 0
then convert(char(5),OdbcScale(xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then @no else @yes end,
'TrimTrailingBlanks' = case ColumnProperty(@objid, name, 'UsesAnsiTrim')
when 1 then @no
when 0 then @yes
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When status & 0x20 = 0 Then @no
Else @yes END,
'Collation' = collation
from syscolumns where id = @objid and number = 0 order by colid-- IDENTITY COLUMN?
if @sysobj_type in ('S ','U ','V ','TF')
begin
print ' '
declare @colname sysname
select @colname = name from syscolumns where id = @objid
and colstat & 1 = 1
select
'Identity' = isnull(@colname,'No identity column defined.'),
'Seed' = ident_seed(@objname),
'Increment' = ident_incr(@objname),
'Not For Replication' = ColumnProperty(@objid, @colname, 'IsIDNotForRepl')
-- ROWGUIDCOL?
print ' '
select @colname = null
select @colname = name from syscolumns where id = @objid and number = 0
and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')
end
end-- DISPLAY PROC PARAMS
if @sysobj_type in ('P ') --RF too?
begin
-- ANY PARAMS FOR THIS PROC?
if exists (select id from syscolumns where id = @objid)
begin
-- INFO ON PROC PARAMS
print ' '
select
'Parameter_name' = name,
'Type' = type_name(xusertype),
'Length' = length,
'Prec' = case when type_name(xtype) = 'uniqueidentifier' then xprec
else OdbcPrec(xtype, length, xprec) end,
'Scale' = OdbcScale(xtype,xscale),
'Param_order' = colid,
'Collation' = collationfrom syscolumns where id = @objid
end
end-- DISPLAY TABLE INDEXES & CONSTRAINTS
if @sysobj_type in ('S ','U ')
begin
print ' '
execute sp_objectfilegroup @objid
print ' '
execute sp_helpindex @objname
print ' '
execute sp_helpconstraint @objname,'nomsg'
if (select count(*) from sysdepends where depid = @objid and deptype = 1) = 0
begin
raiserror(15647,-1,-1) -- 'No views with schemabinding reference this table.'
end
else
begin
select distinct 'Table is referenced by views' = obj.name from sysobjects obj, sysdepends deps
where obj.xtype ='V' and obj.id = deps.id and deps.depid = @objid
and deps.deptype = 1 group by obj.nameend
end
else if @sysobj_type in ('V ')
begin
-- VIEWS DONT HAVE CONSTRAINTS, BUT PRINT THESE MESSAGES BECAUSE 6.5 DID
print ' '
raiserror(15469,-1,-1) -- No constraints defined
print ' '
raiserror(15470,-1,-1) --'No foreign keys reference this table.'
execute sp_helpindex @objname
endreturn (0) -- sp_help
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOEspero ter ajudado.