Post reply

How to list allcolumn names and their data types from a view?

  • February 10, 2006 at 3:48 pm

    #93265

    I have a view which contains 100+ columns from 8 various tables. 

    I need to list all 100+ columns with their data types because I am looking for fields with ntext data types that will require a cast.

    I can view this list in SQL Query Analyzer by clicking on that view name and expanding the columns. 

    But since I have several views that will have to go through the same type of  screening, I'd like to find out if there is a script that will list columns with their datatypes given the viewname.

    Thanks

  • February 10, 2006 at 4:02 pm

    #620298

    You could run this in Query Analyzer. That gives you the option of making a CSV or Excel or just viewing in the results pane.

    use database_name

    go

    sp_help view_name



    Michelle

  • February 10, 2006 at 4:35 pm

    #620301

    Thank you, Michelle.  That works fine.

  • February 13, 2006 at 10:57 am

    #620493

    you might also want to look at some of the built in views;INFORMATION_SCHEMA has a lot of nice data.

    in my database, all my views start with VW_; change your WHERE statement accordingly:

    SELECT  * FROM INFORMATION_SCHEMA.COLUMNS where table_name like 'VW_%'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

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