none
Extrair Tipo e Tamanho do campo em um string RRS feed

  • 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

    segunda-feira, 12 de junho de 2006 17:33

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
    GO

    create 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 asc

     print ' '

      -- 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 name

      return(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 = @objid

      return(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'  = collation

       from 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.name

      end
     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
     end

     return (0) -- sp_help

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

     

    Espero ter ajudado.

    segunda-feira, 12 de junho de 2006 18:36

Todas as Respostas

  • John,

    Você pode utilizar o sp_help para retornar todos os campos e as principais informações de cada campo.

     

    Veja o exemplo:

    SP_Help Produtos

     

    segunda-feira, 12 de junho de 2006 17:45
  • 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

    segunda-feira, 12 de junho de 2006 18:06
  • 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
    GO

    create 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 asc

     print ' '

      -- 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 name

      return(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 = @objid

      return(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'  = collation

       from 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.name

      end
     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
     end

     return (0) -- sp_help

    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

     

    Espero ter ajudado.

    segunda-feira, 12 de junho de 2006 18:36