none
T-SQL Equivalent RRS feed

  • Question

  • SELECT OWNER, TABLE_NAME, COLUMN_NAME 
    FROM sys.all_columns
    WHERE COLUMN_NAME LIKE ('%OFFER%')
    order by TABLE_NAME
    Above is oracle to search a column "OFFER". Can somebody tell me the equivalent to this in T-SQL. Thanks.

    svk
    Tuesday, August 2, 2011 8:20 PM

Answers

  • You can also use the information_schema to determine the same in TSQL 

     

    select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    


    As MSDN says you should use sys.objects to determine the schema of a table instead of the information_schema.

    more information on information_schema.columns can be found in the below link

    http://msdn.microsoft.com/en-us/library/ms188348.aspx


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by czarvk Tuesday, August 2, 2011 8:50 PM
    Tuesday, August 2, 2011 8:35 PM

All replies

  • try the below code

    select t.name,c.name
    from sys.tables t,sys.columns c
    where t.object_id = c.object_id
    and c.name like '%offer%'
    order by t.name
    
    



    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 2, 2011 8:31 PM
  • Try this,

    SELECT SCHEMA_NAME(OBJECTPROPERTY(OBJECT_ID,'OwnerID')), OBJECT_NAME(object_id) as Table_Name, name AS Column_Name
    FROM sys.all_columns
    WHERE name LIKE ('%offer%')
    order by OBJECT_NAME(object_id) 
    

    Or you can query INFORMATION_SCHEMA.COLUMNS system view to get this information.

    SELECT TABLE_SCHEMA AS Owner, TABLE_NAME, COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE '%OFFER%'
    ORDER BY TABLE_NAME
    

     


    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Proposed as answer by Naomi NModerator Tuesday, August 2, 2011 8:35 PM
    • Marked as answer by czarvk Tuesday, August 2, 2011 8:50 PM
    • Unmarked as answer by czarvk Tuesday, August 2, 2011 8:50 PM
    Tuesday, August 2, 2011 8:32 PM
  • You can also use the information_schema to determine the same in TSQL 

     

    select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    


    As MSDN says you should use sys.objects to determine the schema of a table instead of the information_schema.

    more information on information_schema.columns can be found in the below link

    http://msdn.microsoft.com/en-us/library/ms188348.aspx


    Nothing is Permanent... even Knowledge....
    My Blog
    • Marked as answer by czarvk Tuesday, August 2, 2011 8:50 PM
    Tuesday, August 2, 2011 8:35 PM