Professional Web Applications Themes

Query to find table name? - Microsoft SQL / MS SQL Server

Query to find the table name from any given field/column name? ------------------------------------- Say I have two tables, I want to return the table name given a certain field. My proc will create a working temp table with have a fieldname and a value (N,V) pairs I will then create a new temp table that has the status int and tablename columns. I want to insert each row from the first temp table and update the status and tablename columns and I'll also want to have an int status field to trap an error if the field does not exist in ...

  1. #1

    Default Query to find table name?

    Query to find the table name from any given field/column name?


    -------------------------------------
    Say I have two tables, I want to return the table name given a certain field.

    My proc will create a working temp table with have a fieldname and a value (N,V)
    pairs

    I will then create a new temp table that has the status int and tablename
    columns.

    I want to insert each row from the first temp table and update the status and
    tablename columns and I'll also want to have an int status field to trap an
    error if the field does not exist in either table.
    -------------------------------------





    JDP@Work Guest

  2. #2

    Default Re: Query to find table name?

    You can try:

    SELECT DISTINCT TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = col ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Query to find table name?


    Try

    select 'Table' = so.name
    , 'Column' = sc.name
    from sysobjects so
    , syscolumns sc
    where so.type = 'U'
    and sc.id = so.id
    and sc.name like '%ColName%'
    order by so.name, sc.name

    HTH,
    Srinivas Sampangi
     
    name? 
    given a certain field. 
    fieldname and a value (N,V) 
    int and tablename 
    update the status and 
    status field to trap an 
    sampangi Guest

  4. #4

    Default RE: Query to find table name?

    /************************************************** ******************
    **
    ** Column names are stored in Syscolumns and related to Table
    ** names in Sysobjects by DBid.
    **
    ** your results will vary on the number of columns with a given
    ** name in tables
    **
    ** Here is a sample SP, hope this helps
    **
    ************************************************** ******************/

    CREATE PROCEDURE SP_Tablename -- DROP PROCEDURE SP_Tablename
    columnname varchar(128)
    AS
    SELECT table_name = o.name, column_name = c.name
    FROM syscolumns c
    JOIN sysobjects o on c.id = o.id
    WHERE c.name = columnname

    EXEC SP_Tablename name


    Art Guest

Similar Threads

  1. query to find primary key
    By roohbir in forum MySQL
    Replies: 4
    Last Post: March 20th, 10:00 PM
  2. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  3. How can I find a datagrid's source Table Name?
    By joel in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 3rd, 03:58 PM
  4. How to find duplicate records in the table?
    By mac in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:52 PM
  5. Does sql allow us to find out when a table has been changed?
    By Jesse Fitterer in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 1st, 03:08 AM

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