Skip to content

Instantly share code, notes, and snippets.

@TimothyK

TimothyK/ColumnSearch.sql

Last active July 6, 2021 04:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save TimothyK/9040368 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save TimothyK/9040368 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. TimothyK revised this gist Nov 10, 2020. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions ColumnSearch.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -9,7 +9,7 @@ Select
    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' Else Cast(col.system_type_id as varchar)
    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'
    @@ -36,7 +36,7 @@ Where (obj.type = 'U') --'U'=User Table, 'V'=View
    --And (idx.is_primary_key = 1)

    --Data Type fields
    --And (col.system_type_id = 61) --date/time columns only
    --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
  2. TimothyK revised this gist Feb 16, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion ColumnSearch.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    /* Script: Column Search
    ** Author: Timothy Klenke
    ** Date: 2014-02-16
    ** License: Public domain, unlienced
    ** License: Public domain, unlicenced
    **/

    Select
  3. TimothyK renamed this gist Feb 16, 2014. 1 changed file with 0 additions and 0 deletions.
    0 ColumnSearch → ColumnSearch.sql
    View file
    File renamed without changes.
  4. TimothyK created this gist Feb 16, 2014.
    48 changes: 48 additions & 0 deletions ColumnSearch
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,48 @@
    /* Script: Column Search
    ** Author: Timothy Klenke
    ** Date: 2014-02-16
    ** License: Public domain, unlienced
    **/

    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' 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 = 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