Menu Close

TSQL – Search for table/view by column name

If you are trying to find a table or a view based on a column name here is a piece of code that will be very helpful to you.
You will get the following:
1) full column name
2) object name (including schema)
3) object type (table/view)
4) data type (nice format: varchar(6) or numeric(5,2), etc.)
5) null/not null
6) information on identity, check constraint, and default info

DECLARE @Search varchar(200)
SET @Search=’YourColumnName’ –can be a partial or a complete name

SELECT
s.name as ColumnName
,sh.name+’.’+o.name AS ObjectName
,o.type_desc AS ObjectType
,CASE
WHEN t.name IN (‘char’,’varchar’) THEN t.name+'(‘+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')' WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')' WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')' ELSE t.name END AS DataType ,CASE WHEN s.is_nullable=1 THEN 'NULL' ELSE 'NOT NULL' END AS Nullable ,CASE WHEN ic.column_id IS NULL THEN '' ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'') + ',' + ISNULL(CONVERT(varchar(10),ic.increment_value),'') +') = '+ ISNULL(CONVERT(varchar(10),ic.last_value),'null') END +CASE WHEN sc.column_id IS NULL THEN '' ELSE ' computed('+ISNULL(sc.definition,'')+')' END +CASE WHEN cc.object_id IS NULL THEN '' ELSE ' check('+ISNULL(cc.definition,'')+')' END AS MiscInfo FROM sys.columns s INNER JOIN sys.types t ON s.system_type_id=t.system_type_id and t.is_user_defined=0 INNER JOIN sys.objects o ON s.object_id=o.object_id INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id LEFT OUTER JOIN sys.identity_columns ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id LEFT OUTER JOIN sys.computed_columns sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id WHERE s.name LIKE '%'+@Search+'%'

h/t KM.
source