ColumnSearch.sql

/* Script: Column Search
** Author: Timothy Klenke
** Date: 2014-02-16
** License: Public domain, unlicenced
**/

Select 
	--sch.name As 'Schema',
	obj.name As 'Table',
	col.name As 'Column'
	, Case col.system_type_id
		WHEN 34 THEN 'image' WHEN 35 THEN 'text' WHEN 36 THEN 'uniqueidentifier' WHEN 40 THEN 'date' WHEN 41 THEN 'time(' + Cast(col.scale as varchar) + ')' WHEN 42 THEN 'datetime2(' + Cast(col.scale as varchar) + ')' WHEN 43 THEN 'datetimeoffset(' + Cast(col.scale as varchar) + ')' WHEN 48 THEN 'tinyint' WHEN 52 THEN 'smallint' WHEN 56 THEN 'int' WHEN 58 THEN 'smalldatetime' WHEN 59 THEN 'real' WHEN 60 THEN 'money' WHEN 61 THEN 'datetime' WHEN 62 THEN 'float' WHEN 98 THEN 'sql_variant' WHEN 99 THEN 'ntext' WHEN 104 THEN 'bit' WHEN 106 THEN 'decimal(' + Cast(col.precision as varchar) + ',' + Cast(col.scale as varchar) + ')' WHEN 108 Then 'numeric(' + Cast(col.precision as varchar) + ',' + Cast(col.scale as varchar) + ')' WHEN 122 THEN 'smallmoney' WHEN 127 THEN 'bigint' WHEN 165 THEN 'varbinary(' + Case col.max_length When -1 Then 'max' Else Cast(col.max_length as varchar) End + ')' WHEN 167 THEN 'varchar(' + Case col.max_length When -1 Then 'max' Else Cast(col.max_length as varchar) End + ')' WHEN 173 THEN 'binary(' + Cast(col.max_length as varchar) + ')' WHEN 175 THEN 'char(' + Cast(col.max_length as varchar) + ')' WHEN 189 THEN 'rowversion' WHEN 231 THEN 'nvarchar(' + Case col.max_length When -1 Then 'max' Else Cast(col.max_length/2 as varchar) End + ')' WHEN 239 THEN 'nchar(' + Cast(col.max_length/2 as varchar) + ')' WHEN 241 THEN 'xml' WHEN 240 Then 'geography' Else Cast(col.system_type_id as varchar)
	End As 'Data Type'
	--, Case col.system_type_id WHEN 34 THEN 'Binary' WHEN 35 THEN 'String' WHEN 36 THEN 'GUID' WHEN 40 THEN 'Date' WHEN 41 THEN 'Time' WHEN 42 THEN 'DateTime2' WHEN 43 THEN 'DateTimeOffset' WHEN 48 THEN 'Byte' WHEN 52 THEN 'Int16' WHEN 56 THEN 'Int32' WHEN 58 THEN 'DateTime' WHEN 59 THEN 'Single' WHEN 60 THEN 'Decimal' WHEN 61 THEN 'DateTime' WHEN 62 THEN 'Double' WHEN 98 THEN 'Object' WHEN 99 THEN 'String' WHEN 104 THEN 'Boolean' WHEN 106 THEN 'Decimal' WHEN 108 Then 'Decimal' WHEN 122 THEN 'Decimal' WHEN 127 THEN 'Int64' WHEN 165 THEN 'Binary' WHEN 167 THEN 'String' WHEN 173 THEN 'Binary' WHEN 175 THEN 'String' WHEN 189 THEN 'Binary' WHEN 231 THEN 'String' WHEN 239 THEN 'String' WHEN 241 THEN 'Xml' Else 'Object' End As ClrType
	, Case col.is_nullable When 0 Then 'not null' Else '' End As 'Nullable'
	, Case When (Not def.object_id Is Null) Then Case When substring(def.definition,0,3) = '((' Then substring(def.definition,3, len(def.definition)-4) Else substring(def.definition,2, len(def.definition)-2) End	When (col.is_identity=1) Then '=Identity' When (col.is_computed=1) Then '=' + calccol.definition When col.system_type_id = 189 Then '=ver' Else '' End As 'Default'
	, IsNull(Case When (idx.is_primary_key = 1) Then 'PK' When (idx.is_unique = 1) Then 'UQ' + Cast(idx.index_id As varchar) Else 'IX' + Cast(idx.index_id As varchar) End	+ Case When idx.ColumnCount = 1 Then '' Else '-' + CAST(idx.index_column_id as varchar) End, '') As 'Index'
	, Case When (fk.object_id Is Null) Then '' Else Object_Name(fk.referenced_object_id) + '.' + Col_Name(fc.referenced_object_id, fc.referenced_column_id) End As 'FK'

From Sys.Objects obj
	Inner Join Sys.Columns col On (obj.object_id = col.object_id)
	Inner Join Sys.schemas sch On (obj.schema_id = sch.schema_id)
	Left Join Sys.default_constraints def On (col.default_object_id = def.object_id) 
	Left Join (sys.Foreign_Keys fk Inner Join Sys.Foreign_Key_Columns fc On (fk.object_id = fc.Constraint_Object_id)) On ((fk.parent_object_id = col.object_id) And (fc.parent_column_id = col.column_id))
	Left Join (Select i.name, i.object_id, i.is_primary_key, i.is_unique, i.index_id, ic.column_id, ic.index_column_id, cnt.ColumnCount From sys.Indexes i Inner Join Sys.Index_Columns ic On (i.object_id = ic.object_id) And (i.index_id = ic.index_id) Inner Join (Select object_id, index_id, Count(*) As ColumnCount From Sys.Index_Columns Group By object_id, index_id) cnt On (i.object_id = cnt.object_id) And (i.index_id = cnt.index_id)) idx On (obj.object_id = idx.object_id) And (col.column_id = idx.column_id)
	Left Join sys.computed_columns calccol On (col.object_id = calccol.object_id) And (col.column_id = calccol.column_id)

Where (obj.type = 'U') --'U'=User Table, 'V'=View
	
--Table Name and Column Name
	And (sch.name = 'dbo')
	And (obj.name Like '%%')
	And (col.name Like '%%')

--Primary Keys only
	--And (idx.is_primary_key = 1)

--Data Type fields
	--And (col.system_type_id In (40, 41, 42, 43, 58, 61))   --date/time columns only
	--And (col.system_type_id = 104)  --flag (bit) columns only

--Dependents Of search
	--And (Object_Name(fk.referenced_object_id) = 'Products')
	--And (Col_Name(fc.referenced_object_id, fc.referenced_column_id) = 'ProductID')

Order By obj.name
 	, col.column_id
	, col.name

Back link: https://codemonkeyprojectiles.com/blog/2014/SysQuery/Browse/