Posted By

kencyber on 12/14/09


Tagged

search find columncolumn


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

kencyber


Find a Column by Name in User Tables


 / Published in: SQL
 

Perform a search of all user tables in the current database to locate a column defined in a user table that matches a certain name. The first query looks for an exact column name and the second one returns all column names which match a keyword (using the LIKE operator).

  1. -- find a column with a specific name
  2. SELECT schema_name(o.schema_id) + '.' + o.[name] AS tablename, c.[name] AS columnname, t.[name] AS datatype, c.max_length, c.[precision], c.scale, c.is_nullable, c.is_identity
  3. FROM sys.COLUMNS c
  4. INNER JOIN sys.objects o ON o.[object_id] = c.[object_id]
  5. AND o.[type] = 'U'
  6. LEFT JOIN sys.types t ON t.system_type_id = c.system_type_id
  7. AND t.user_type_id = c.user_type_id
  8. WHERE c.[name] = 'COLUMNTOFIND'
  9.  
  10. -- find a column that matches a keyword
  11. SELECT schema_name(o.schema_id) + '.' + o.[name] AS tablename, c.[name] AS columnname, t.[name] AS datatype, c.max_length, c.[precision], c.scale, c.is_nullable, c.is_identity
  12. FROM sys.COLUMNS c
  13. INNER JOIN sys.objects o ON o.[object_id] = c.[object_id]
  14. AND o.[type] = 'U'
  15. LEFT JOIN sys.types t ON t.system_type_id = c.system_type_id
  16. AND t.user_type_id = c.user_type_id
  17. WHERE c.[name] LIKE '%COLUMNTOFIND%'
  18. ORDER BY schema_name(o.schema_id), o.[name]

Report this snippet  

You need to login to post a comment.