Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Fairfax, VA
    Posts
    16

    Unhappy Unanswered: Get table date using sysobjects and syscolumns. Quick?

    All my online research has told me how to get info about a table field's data using the system tables, but I can't find anything that tells how to get that table field's data specifically. Could be that it's difficult to explain so difficult to search on but I know there has to be a way to do this and it can't be that difficult.

    How do I use the system tables sysobjects and syscolumns to give me the data from a specific field in a third table?

    Basically I don't want to know the field type for a tables field, I want to know that field's value.

    Let's say I have a table called tblCompanies and that table has 4 fields. idCompany, companyName, companyState, and companyCountry.

    How can I return the value as a command parameter for any one of the 4 fields using sysobjects and syscolumns?

    If I were writing dynamic SQL I would do something like this:

    set @valueToReturn = exec ('select ' + @fieldNameToReturn + ' from ' + @tableToSearch + ' where ' + @fieldToMatchOn + ' = ' + @valueToMatchOn)

    But I don't want to use dynamic SQL, I want to use the existing system tables to write a straightforward query. My nonfunctioning/English version of this would be:

    give me the value for the field name I send in as a string (@fieldNameToReturn)
    from the table I send in as a string (@tblToSearch)
    where (sysobjects.name = @tblToSearch) and (syscolumns.name = @fieldNameToReturn) and (@tblToSearch.@fieldnameToMatchOn = @valueToMatchAgainst)

    I'm using sysobjects and syscolumns because that's where I can use my variables for table name and column name to link. I just can't figure out how to get hold of my actual data table and the values in it.

    Does that make any sense to anyone? I'm sure someone has had to want something like this.

    Thank you, thank you, thank you!

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you can't do this kind of thing unless you use dynamic sql.

    http://www.sommarskog.se/dynamic_sql.html

Posting Permissions

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