Find a Column by Name in User Tables - SQL Snipplr Social Repository

Revision: 21481

at December 14, 2009 14:31 by kencyber

Initial Code

-- find a column with a specific name
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
from sys.columns c
inner join sys.objects o ON o.[object_id] = c.[object_id]
	AND o.[type] = 'U'
left join sys.types t ON t.system_type_id = c.system_type_id
	AND t.user_type_id = c.user_type_id
where c.[name] = 'COLUMNTOFIND'

-- find a column that matches a keyword
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
from sys.columns c
inner join sys.objects o ON o.[object_id] = c.[object_id]
	AND o.[type] = 'U'
left join sys.types t ON t.system_type_id = c.system_type_id
	AND t.user_type_id = c.user_type_id
where c.[name] LIKE '%COLUMNTOFIND%'
order by schema_name(o.schema_id), o.[name]

Initial URL

Initial Description

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).

Initial Title

Find a Column by Name in User Tables

Initial Tags

search, find

Initial Language

SQL

Choose a language for easy browsing: