Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010
    Posts
    19

    Red face Unanswered: How to find the Column Name of a Table from its Column Value ???

    How to find the Column Name of a Table from its Column Value ???

    For Example:

    select * from country where location='america'

    This will fetch me the column name and the colum values which has america.

    All i want is i don't know the column name but with the help of column value i want to find the column name in a table.

    Pls help me,...

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    But you know the table name, right? You can join sys.columns, sys.tables, and sys.types to get column name and type for all columns in a specific (or all) tables. This can be used to greate a dynamic SQL statement (nvarchar(max) string) which you in turn invoke with sp_executesql.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Oct 2010
    Posts
    19

    Red face

    example pls..???

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    What about trying yourself, and show us what you've got if you cannot make it work, or at tell us what you don't understand?
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Oct 2010
    Posts
    19

    Red face

    create procedure ex1
    as
    declare @tblname varchar(100)
    select @tblName = 'billingbatch'

    declare @objid int
    declare @sysobj_type char(2)

    select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@tblName)

    select name from syscolumns where id = @objid.

    after executing this procedure i got all the column name but what i want is to get filtering with the help of column value to get column name.. here i m getting all the column name.

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You'll have to do some fixup for yourself, as I don't have a SQL Server at hand now.
    Code:
    declare
      @sql nvarchar(max);
    set
      @sql = '
        select * from ['+@table+']
        where
          1=0';
        select
          @sql = @sql + ' or ['+sc.name+'] like '''+@text_to_find+''''
        from
          sys.tables st
            inner join
          sys.columns sc on
              sc.object_id = st.object_id
            inner join
          sys.types styp on
              sc.sytem_type_id = styp.system_type_id and
              sc.user_type_id = styp.user_type_id
        where
          styp.system_type_id in ('+@datatypes+')';
    exec sp_executesql @sql
    You may want to create a procedure out of it, or at leat @datatypes, @text_to_find and @table has to be a paramter, or replaced. This should be enough for you to make it I think.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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