Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: SQL Table Information

  1. #1
    girldemsuga Guest

    Default SQL Table Information

    Is there a way in MS SQL Server to use a query to get table information (Example: in MySQL, the following query can be used to get the table information describe to you:

    Describe [Table_Name_Here];

  2. #2
    Join Date
    Feb 2003
    Posts
    3,184
    Rep Power
    0

    Default

    As far as I remember you have to query the system tables. But there is probably a easier way to do it or maybe a system sp. I'm not sure what it is though. try;
    Code:
    select syscolumns.name, syscolumns.length , systypes.name as data_type, syscolumns.isnullable, syscolumns.isoutparam from syscolumns inner join sysobjects on (sysobjects.id = syscolumns.id) inner join systypes on (syscolumns.xtype = systypes.xtype) where sysobjects.name = 'MYTABLE' and systypes.status not in (3, 1) order by syscolumns.colorder

  3. #3
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    thats as close as you will get to it, all SQL Server schema is stored as Meta Data in system tables. information on the system tables is documented in help file for Query Analyzer
    Last edited by icymint3; Oct 31, 2006 at 07:38 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

  4. #4
    girldemsuga Guest

    Default

    thanks, that was a big help, gonna create a Store Procedure to make the task simple.

  5. #5
    girldemsuga Guest

    Default

    I'm here wondering if a similar approach can be used for a MS Access Table.

  6. #6
    Join Date
    Sep 2004
    Posts
    681
    Rep Power
    0

    Default

    Quote Originally Posted by girldemsuga View Post
    I'm here wondering if a similar approach can be used for a MS Access Table.
    Hmm, back in the heady days of ASP, when Microsoft's ADO was used for database access, there was a much overlooked library that you could use for performing such tasks; ADOX. With it, not only could you pull the schema from a table but actively make changes to it as well.

    It was never as well documented as some of the other MDAC components but if you search around you can still find a write-up or two about it (e.g. http://www.4guysfromrolla.com/webtech/013101-1.shtml)

  7. #7
    girldemsuga Guest

    Default

    this ADOX is interesting, i am doing a lot of research into it.
    I was also wondering if i can be used in VB.Net 2005

  8. #8
    Join Date
    Aug 2002
    Posts
    3,959
    Rep Power
    25

    Default

    Is there a way in MS SQL Server to use a query to get table information
    What type of information do you want about the table?

  9. #9
    girldemsuga Guest

    Default

    Basically what i needed was Column names, data type and Length.
    The query that OWEN provide did the trick.
    select syscolumns.name, syscolumns.length , systypes.name as data_type, syscolumns.isnullable, syscolumns.isoutparam
    from syscolumns inner join sysobjects on (sysobjects.id = syscolumns.id) inner join systypes on (syscolumns.xtype = systypes.xtype)
    where sysobjects.name = 'MYTABLE' and systypes.status not in (3, 1) order by syscolumns.colorder

  10. #10
    girldemsuga Guest

    Default

    Now is there a way to get the names of the table for a specific Database in both SQL Server and MySQL 5 ?

    The reason for me needing this information is I am planning to write a program while generate Visual Basic.NET code for that specific table.
    I already wrote one which will generate VB6 code (Insert and Update code) which made life a lot easier for me which writing Database applications.
    With, the new version I plan to write, a Class for the Specific table will be generate which should have everything needed to Insert, Update , Select and Delete Statement.
    Last edited by girldemsuga; Nov 9, 2006 at 04:10 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •