Posted By

kencyber on 12/14/09

Tagged

search
find

Versions (?)

Last Edited at 12/14/09 02:31pm

Statistics

Viewed 863 times

Favorited by 1 user(s)

Related snippets

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

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

-- 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]