Getting column types.

  • I would like to find out the characteristics of a table. The only issue is I would prefer to stay within my database and not require access to the sys tables in the master DB. I think that makes sense. If not let me know.

    Essentially what I am trying to determine is if the column is a varchar or int. I want to to be able to urn a query that is independent of the ' or I will have to determine them on the fly.

    Thanks,

    Neil

  • Check out the view called infoschemacolumns.

    Andy

  • I don't seem to have that view. Do you have it available?

    Thanks,

    Neil

  • select * from information_schema.columns.

    Steve Jones

    steve@dkranch.net

  • Do I have a way to view the TSQL. I am trying to join the syscolumns, sysobjects and systypes now 🙂

    Neil

  • After about 2 hours I have come up with this:

    select c.name AS ColumnName, C.Type, T.Name

    from sysobjects o, syscolumns c, systypes t

    where o.id = c.id

    AND o.type = 'U'

    AND c.xtype = t.xtype

  • Looks about right, but the system tables could change in an upgrade. Information_schema views are standard views that will stay the same.

    Steve Jones

    steve@dkranch.net

  • I guess, but how can I limit my query. I only want to query for a specific table and specific columns.

  • and o.name= 'addresses' --Add this condition to the query above. Replace 'addresses' with the tablename u want to query. So your query would look like :

    select c.name AS ColumnName, C.Type, T.Name

    from sysobjects o, syscolumns c, systypes t

    where o.id = c.id

    AND o.type = 'U'

    AND c.xtype = t.xtype

    and o.name= 'addresses'

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • I wanted to know how to limit the view. I know how to limit my query.

    Neil

  • If you are saying you need to limit the query performed when someone does

    SELECT * FROM myView

    Then you will need to add the o.[Name] AS TableName in you views output selection so you can do

    WHERE TableName = 'table1'

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply