Search for columns and add descriptions to columns

ColumnSearch.sql

/* Script: Column Search with Descriptions
** Author: Timothy Klenke
** Date: 2014-02-27
** License: Public domain, unlicenced
**/
 
Select 
--** Schema Name
	--sch.name As 'Schema',
--** TableName
	obj.name As 'Table',
--** Column Name
	--** Add Comma
	--Case ROW_NUMBER() Over(Partition By obj.name Order By ISNULL(idx.index_id, 99999), idx.index_column_id, col.column_id) When 1 Then '' Else ', ' End +
	--** Add table alias to column name
	--'alias.' +
	col.name As 'Column'
--** Description (extended property)
	, IsNull((Select value From fn_listextendedproperty('MS_Description', 'SCHEMA', sch.name, Case obj.type When 'U' Then 'TABLE' When 'V' Then 'VIEW' End, obj.name, 'COLUMN', col.name)), '') As Description
--** SQL Server Data Type
	, 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'
--** CLR 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
--** Nullable
	, Case col.is_nullable When 0 Then 'not null' Else '' End As 'Nullable'
--** Default
	, 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 '(=@@DBTS+1)' Else '' End As 'Default'
--** Index
	, 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'
--** Foreign Key
	, 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'
--** Set Column Description stored procedure call
	--, 'exec #SetColumnDescription ''' + sch.name + ''', ''' + obj.name + ''', ''' + col.name + ''', ''' + REPLACE(IsNull(Cast((Select value From fn_listextendedproperty('MS_Description', 'SCHEMA', sch.name, Case obj.type When 'U' Then 'TABLE' When 'V' Then 'VIEW' End, obj.name, 'COLUMN', col.name)) As varchar(2000)), ''), '''', '''''') + '''' As SetColumnDescriptionCommand

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 (obj.name Like '%%')
	And (col.name Like '%%')

--Required Fields without a default
	--And (col.is_nullable = 0) And (def.object_id Is Null) And (col.system_type_id <> 189)

--Primary Keys only
	--And (idx.is_primary_key = 1)
--Indexes
	--And (idx.index_id Is Not Null)

--Missing Description
	--And Not Exists(Select value From fn_listextendedproperty('Description', 'SCHEMA', sch.name, Case obj.type When 'U' Then 'TABLE' When 'V' Then 'VIEW' End, obj.name, 'COLUMN', col.name))

--Data Type fields
	--And (col.system_type_id = 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
	, IsNull(idx.index_id, 99999), idx.index_column_id  --List indexed columns first
	, Case When (col.is_nullable = 0) And (def.object_id Is Null) And (col.system_type_id <> 189) Then 0 Else 1 End --Show required columns without a default after indexed columns
 	, col.column_id  --Regular column order as defined in table (comment this line out to list alphabetically instead)
	, col.name


/* Stored Procedure for setting column descriptions 
Create Procedure #SetColumnDescription(@schemaName sysname, @tableName sysname, @columnName sysname, @description sql_variant)
As
	If Exists (Select 1 From fn_listextendedproperty('MS_Description', 'SCHEMA', @schemaName, 'TABLE', @tableName, 'COLUMN', @columnName))
		exec sp_DropExtendedProperty 'MS_Description', 'SCHEMA', @schemaName, 'TABLE', @tableName, 'COLUMN', @columnName

	If (Not @description Is Null) And (Not @description = '')
		exec sp_AddExtendedProperty 'MS_Description', @description, 'SCHEMA', @schemaName, 'TABLE', @tableName, 'COLUMN', @columnName
GO

--Paste SetColumnDescriptionCommand column here
GO

Drop Procedure #SetColumnDescription
GO
*/

/* View and Trigger definitions

Select tbl.name As TableName, tr.name As TriggerName, tr.is_disabled, sm.definition
From sys.triggers tr
	Inner Join sys.tables tbl On (tr.parent_id = tbl.object_id)
	Inner Join sys.sql_modules sm On (tr.object_id = sm.object_id)
Where (tbl.name Like '%%')
	And (tr.name Like '%%')
Order By tbl.name, tr.name

Select vw.name As ViewName, sm.definition
From sys.views vw
	Inner Join sys.sql_modules sm On (vw.object_id = sm.object_id)
Where (vw.name Like '%%')
Order By vw.name

*/

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