none
Need Schema Information for INFORMATION_SCHEMA Table RRS feed

  • Question

  • I'm trying to get schema information for the INFORMATION_SCHEMA results themselves. Unfortunately, the individual data types aren't documented for the SQL CE version:

    http://technet.microsoft.com/en-us/library/ms174156.aspx

    I'm building a class for use at run-time, so design-time, ad-hoc manual queries against IS aren't sufficient. I'm not familiar enough with LINQ to be able to easily accomplish this, however.

    I've gotten this far:

    <Table(Name:="INFORMATION_SCHEMA.COLUMNS")>
    Partial Public Class Columns
      Inherits LinqToSql
    
      Private _COLUMN_NAME As String
    
      Public Sub New()
        MyBase.New()
      End Sub
    
      <Column(Name:="COLUMN_NAME", Storage:="_COLUMN_NAME")>
      Public Property ColumnName As String
        Get
          Return _COLUMN_NAME
        End Get
        Set(Value As String)
          _COLUMN_NAME = Value
        End Set
      End Property
    End Class

    This works--it displays the database's column name--but I need to know the metadata for the COLUMN_NAME column itself.

    Does anyone know how I would accomplish this, given the lack of documentation? I know that documentation exists at this level for SQL Server, but I'm not comfortable substituting.

    Thanks,
    Jeff Bowman



    • Edited by InteXX Friday, January 17, 2014 1:17 AM
    • Moved by Fred BaoModerator Friday, January 17, 2014 5:50 AM It is a thread about VB.
    • Moved by Carl Cai Friday, January 17, 2014 8:27 AM
    Friday, January 17, 2014 1:14 AM

Answers

  • I found a tool that can do this. It's called SQL Offline, by Interscape:

    http://inters.com/

    It returns the data type for each of the INFORMATION_SCHEMA view columns.

    Thanks,
    Jeff Bowman


    • Marked as answer by InteXX Friday, January 17, 2014 9:05 PM
    • Edited by InteXX Friday, January 17, 2014 9:05 PM
    Friday, January 17, 2014 9:04 PM

All replies

  • Hello,

    Since this issue is more regarding VB, I will move it to the suitable forum, there are VB experts who will help you better.

    Thanks for your understanding.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 17, 2014 5:50 AM
    Moderator
  • Fred, this question is language-agnostic. It's not about VB. I just happened to have used VB in the sample code. It's about LINQ and how to iterate across a Table's Column objects and get the metadata.

    By moving it to this forum, you've made it off-topic. It's also far less likely to be answered here, as it's going to require a LINQ expert.

    Would you move it back.

    Friday, January 17, 2014 8:13 AM
  • I am tring to help.

    I got a similar one, it shows that he could create definitions for Tables and Views successfully using the same way

    for INFORMATION_SCHEMA.ROUTINES.

    <Table(Name:="INFORMATION_SCHEMA.ROUTINES")> _
    Partial Public Class Routine
        Inherits Linq2SQL
    
        Private _SPECIFIC_CATALOG As String
        Private _SPECIFIC_SCHEMA As String
        Private _SPECIFIC_NAME As String
        Private _ROUTINE_CATALOG As String
        Private _ROUTINE_SCHEMA As String
        Private _ROUTINE_NAME As String
        Private _ROUTINE_TYPE As String
        Private _ROUTINE_BODY As String
        Private _ROUTINE_DEFINITION As String
        Private _DATATYPE As String
        Private _CHARMAXLEN As Nullable(Of Integer)
        Private _CREATED As DateTime
        Private _LAST_ALTERED As DateTime
    
        Public Sub New()
            MyBase.New()
        End Sub
    
        <Column(Name:="SPECIFIC_CATALOG", Storage:="_SPECIFIC_CATALOG")> _
        Public Property specificcatalog() As String
            Get
                Return _SPECIFIC_CATALOG
            End Get
            Set(ByVal value As String)
                _SPECIFIC_CATALOG = value
            End Set
        End Property
    
        <Column(Name:="SPECIFIC_SCHEMA", Storage:="_SPECIFIC_SCHEMA")> _
        Public Property specificschema() As String
            Get
                Return _SPECIFIC_SCHEMA
            End Get
            Set(ByVal value As String)
                _SPECIFIC_SCHEMA = value
            End Set
        End Property
    
        <Column(Name:="SPECIFIC_NAME", Storage:="_SPECIFIC_NAME")> _
        Public Property specificname() As String
            Get
                Return _SPECIFIC_NAME
            End Get
            Set(ByVal value As String)
                _SPECIFIC_NAME = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_CATALOG", Storage:="_ROUTINE_CATALOG")> _
        Public Property routinecatalog() As String
            Get
                Return _ROUTINE_CATALOG
            End Get
            Set(ByVal value As String)
                _ROUTINE_CATALOG = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_SCHEMA", Storage:="_ROUTINE_SCHEMA")> _
        Public Property routineschema() As String
            Get
                Return _ROUTINE_SCHEMA
            End Get
            Set(ByVal value As String)
                _ROUTINE_SCHEMA = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_NAME", Storage:="_ROUTINE_NAME")> _
        Public Property routinename() As String
            Get
                Return _ROUTINE_NAME
            End Get
            Set(ByVal value As String)
                _ROUTINE_NAME = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_TYPE", Storage:="_ROUTINE_TYPE")> _
        Public Property routinetype() As String
            Get
                Return _ROUTINE_TYPE
            End Get
            Set(ByVal value As String)
                _ROUTINE_TYPE = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_BODY", Storage:="_ROUTINE_BODY")> _
        Public Property routinebody() As Integer
            Get
                Return _ROUTINE_BODY
            End Get
            Set(ByVal value As Integer)
                _ROUTINE_BODY = value
            End Set
        End Property
    
        <Column(Name:="ROUTINE_DEFINITION", Storage:="_ROUTINE_DEFINITION")> _
        Public Property routinedefinition() As String
            Get
                Return _ROUTINE_DEFINITION
            End Get
            Set(ByVal value As String)
                _ROUTINE_DEFINITION = value
            End Set
        End Property
    
        <Column(Name:="DATA_TYPE", Storage:="_DATATYPE")> _
        Public Property datatype() As String
            Get
                Return _DATATYPE
            End Get
            Set(ByVal value As String)
                _DATATYPE = value
            End Set
        End Property
    
        <Column(Name:="CHARACTER_MAXIMUM_LENGTH", CanBeNull:=True, Storage:="_CHARMAXLEN", DbType:="Int")> _
        Public Property charmaxlen() As Nullable(Of Integer)
            Get
                Return _CHARMAXLEN
            End Get
            Set(ByVal value As Nullable(Of Integer))
                _CHARMAXLEN = value
            End Set
        End Property
    
        <Column(Name:="CREATED", CanBeNull:=True, Storage:="_CREATED")> _
        Public Property created() As DateTime
            Get
                Return _CREATED
            End Get
            Set(ByVal value As DateTime)
                _CREATED = value
            End Set
        End Property
    
        <Column(Name:="LAST_ALTERED", CanBeNull:=True, Storage:="_LAST_ALTERED")> _
        Public Property lastaltered() As DateTime
            Get
                Return _LAST_ALTERED
            End Get
            Set(ByVal value As DateTime)
                _LAST_ALTERED = value
            End Set
        End Property
    
    End Class

    From LinqToSQL definition for INFORMATION_SCHEMA.ROUTINES


    remember make the reply as answer and vote the reply as helpful if it helps.

    Friday, January 17, 2014 8:21 AM
  • Thank you, but I already have that. What I'm trying to get is the data types for the properties (INFORMATION_SCHEMA view columns).

    Thanks,
    Jeff Bowman

    Friday, January 17, 2014 8:22 PM
  • I found a tool that can do this. It's called SQL Offline, by Interscape:

    http://inters.com/

    It returns the data type for each of the INFORMATION_SCHEMA view columns.

    Thanks,
    Jeff Bowman


    • Marked as answer by InteXX Friday, January 17, 2014 9:05 PM
    • Edited by InteXX Friday, January 17, 2014 9:05 PM
    Friday, January 17, 2014 9:04 PM