Professional Web Applications Themes

Getting Column Properties Back, TYPE/LENGTH - Coldfusion - Advanced Techniques

Anyone know how I would get the column properties back in a table. For example, column First_Name, is a varchar of 255 and is not a primary key. Appreciate it...

  1. #1

    Default Getting Column Properties Back, TYPE/LENGTH

    Anyone know how I would get the column properties back in a table. For example,

    column First_Name, is a varchar of 255 and is not a primary key.

    Appreciate it
    blastbeat Guest

  2. #2

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    What database type/version are you using?
    mxstu Guest

  3. #3

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    It's impossible in Access and trivial in most others (querying against system
    objects), assuming you have DBA permissions.

    It's actually possilbe in Access but not as an automatic function from Cold
    Fusion. You need to manually run the Tools/yze/Doenter function and
    then Save As table, then rename the resulting Object Definition table to
    something you like, tblSchema, and query that from Cold Fusion. Problem is you
    must remember to do the setup work every time you make a change.

    JMGibson3 Guest

  4. #4

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    Microsoft SQL Server 2000. I've seen this done before, and Steve Bryant pointed
    out he does it in his CFC's for Data Manager. I know Kevin does it with DBX as
    well, available on websolete.com - killer program btw.

    JM, just to clarify, what I am creating is a utility to identify the
    differences between two tables. The user passes the database names and tables
    and it will return back any differences between them. I am mainly doing this
    for practice, but it also will be useful at my office.

    I want to let the user know if their are fields which don't match in the
    tables, including if one has a primary key and the other doesn't, and if the
    types are not the same.

    Appreciate your feedback.

    blastbeat Guest

  5. #5

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    I've only done it a few times in Oracle (and would have to whip out the manual
    again anyway). I don't have SQL Server or manuals, some quick Googling
    revealed that some joining amongst the sysobjects, syscolumns, and systypes
    tables should give you what you need. I'm sure you'll need full permissions,
    and SELECT * should get you started. At least you know what to look for if you
    have a manual.

    JMGibson3 Guest

  6. #6

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    blastbeat,

    For SQL Server 2000 take a look at the INFORMATION_SCHEMA views. For example

    SELECT * FROM INFORMATION_SCHEMA.Columns WHERE Table_Name = 'SomeTable'

    mxstu Guest

  7. #7

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    JMGibson3,

    Didn't mean to be rude there. I just noticed your comment about the the
    'sys*' tables. Afaik, MS recommended using the INFORMATION_SCHEMA views over
    the the 'sys*' tables because those tables may change from version to version.
    At least that was the recommendation for ms sql 2000. I don't know about the
    newer versions.

    mxstu Guest

  8. #8

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    Hmm, brought back 0 records. Do you mind explaining a bit more. I think you're
    on the right track and it would help me a great deal. Appreciate it!

    What I have typed into query yzer is. SELECT * FROM
    INFORMATION_SCHEMA.Columns WHERE Table_Name = 'Employees'

    There are over 50 databases on this server. I'd imagine I'd have to specify
    the db name somewhere in that query right?


    blastbeat Guest

  9. #9

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    Found my answer, SP_HELP, the system sp returns exactly what I need. Thanks for everyones help on this.
    blastbeat Guest

  10. #10

    Default Re: Getting Column Properties Back, TYPE/LENGTH

    Not rude at all. I said I had no SQL Server. I just did a quick Google to
    make sure (you never know with MS) that SQL Server publicized it's system
    tables and noted the first thing I ran across. Your info will be a tickler
    for the day I do encounter it, not to mention making life easier. The Views
    must do the joining mentioned in the Q&A I ran across (which could have been
    way back in the 90's, didn't think to look at the date).

    JMGibson3 Guest

Similar Threads

  1. SQL Server - Column Length
    By Mattastic in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 4th, 03:11 PM
  2. filter column on word length
    By mo280379 in forum Coldfusion Database Access
    Replies: 8
    Last Post: March 20th, 09:47 PM
  3. Length of column with 'text' data type
    By Blazeix in forum Coldfusion Database Access
    Replies: 5
    Last Post: June 9th, 04:51 PM
  4. Column only a certain length?
    By brendalisa in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: May 16th, 08:14 PM
  5. Replies: 7
    Last Post: October 15th, 11:05 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139