Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Question Unanswered: Query for the STRUCTURE of Sql

    Im trying to write an sql statement that queries for the structure of an SQL table (i need the datatype of each field).

    I have code that queries for the field names...

    Code:
    set rs=Server.CreateObject("ADODB.recordset")
    	rs.Open sqlfood, MyConn
    	while not rs.eof
    		response.write rs("ID")
    		rs.movenext
    		wend
    		for each f in rs.Fields
    			Response.Write(f.Name & "<br>")
    		next
    But how do i get the rest of the information?

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT *
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 'daTable' = TABLE_NAME
    -PatP

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    Quote Originally Posted by Pat Phelan
    Code:
    SELECT *
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE 'daTable' = TABLE_NAME
    -PatP
    Thank you! This isn't exactly what i needed, BUT it gave me enough information to figure out how to do what i wanted to do.


    Code:
    	set rs=Server.CreateObject("ADODB.recordset")
    	rs.Open sqlfood, MyConn
    	while not rs.eof
    		response.write rs("Column_Name") & ": type="& rs("data_Type") &"<br>"
    		rs.movenext
    		wend
    		for each f in rs.Fields
    ' gives the names of the schema fields to response.write above			
    Response.Write(f.Name & ":")
    		next

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    Code:
    try this too
    SELECT  b.name,  c.name as TypeName, 
          b.length,  b.isnullable
    FROM sysobjects a 
    INNER JOIN 
    syscolumns b ON a.id = b.id 
    INNER JOIN
    systypes c ON b.xusertype = c.xusertype 
    WHERE a.id = object_id(N'[dbo].[emptable]') 
    ORDER BY b.colId

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bklr, microsoft has told us since sql 2000 not to use the system tables, but to use INFORMATION_SCHEMA views instead

    microsoft may change the system tables, but the INFORMATION_SCHEMA views are always guaranteed to be correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It has?
    Personally I still use them as I have found that they contain some extra nuggets of information that I have not been able to find in the information_schema...

    In SQL 2000 or earlier I use sysobjects, in future releases the sys schema was introduced and so I use that...
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I see it a bit like using ANSI compatible syntax. Do it when you can, but don't avoid the T-SQL extensions on principle. Ditto with this topic - as George says, not everything is in INFORMATION_SCHEMA.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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