Post reply

Need help regarding 'SELECT' Clause in SQL Server 2000

  • July 25, 2008 at 6:47 am

    #191530

    Hi Everyone,

    Can anybody help me to resolve my problem?

    I need to identify the 'Column Names' of specific data types in 'SELECT' CLAUSE using MSSQL SERVER 2000, I don't want to mention the column names, column names should be selected dynamically for specific data types, e.g. I have below mentioned table;

    CREATE TABLE Test1(

    Test_id INT IDENTITY(1,1),

    Test_name Varchar(150),

    Test_dsc text,

    Test_image image);

    Assuming above mentioned table structure, I want to select the 'COLUMN NAMES' in select clause whose data type NOT IN (text, image, ntext)

    Regards,

    Shahbaz.

  • July 25, 2008 at 12:41 pm

    #848651

    It's been a while since I used SQL 2000, but if I remember correctly, you should be able to select from the syscolumns table and get all the columns in a table. You should be able to write a Where clause on that to get just the ones you want. Will that do what you need?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 25, 2008 at 12:49 pm

    #848660

    You have to join sysobjects, syscolumns and systypes.

    This should give you the desired result.

  • July 25, 2008 at 1:13 pm

    #848674

    select name as column_name from syscolumns where xtype in

    (select xtype from systypes where name NOT IN (text, image, ntext))

    and id = (select id from sysobjects where name like 'Your_table_name')

    You will get the list of column name, which you can use. Someone might ahve a easy and better code.

    Thanks

    Dev

  • July 25, 2008 at 1:58 pm

    #848683

    Are you looking for tables with specific or without specific data types?

    If so, you can try this (and select table_name or anything else you need):

    SELECT *

    FROM Information_Schema.Columns

    where data_type not in ('text', 'image', 'ntext')

  • July 27, 2008 at 1:56 am

    #848876

    Just try this query to achieve your task,

    select * from INFORMATION_SCHEMA.COLUMNS

    where DATA_TYPE not in ('varchar') and table_name='venkat'

    Regards,

    Venkatesan Prabu. J

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • July 27, 2008 at 9:46 pm

    #848948

    Thanks all of you its working fine

Viewing 7 posts - 1 through 6 (of 6 total)

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