The Object Explorer (F8) in SQL Server Management Studio is quite limiting
when you need to search for a table or column. In fact, there are no search
capabilities at all. All you can do with the simple tree user interface is
drill down, scroll and scan through the tables and columns. You have to
navigate to different nodes in the tree and/or open popup windows if you what to
see the column names, default values, or indexes.
System queries to the rescue! I've written (and tweaked over the years) a small
query against the system tables that allows me to easily search a database for
any table or column name. The results show a list of all tables and columns
matching the search criteria along with data type, null constraint, index and
foreign key information.
I don’t even use the Object Explorer any more. Instead I just use this
query. It is a saved file I have on my machine, which I pin to the taskbar.
Most days I just use this query and the Object Explorer never gets opened. I
prevent the Object Explorer from opening automatically by the
Tool-Options-Environment-Startup settings.
So here is the query:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
/* 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' 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
|
Once the query is open and I point to the correct database (Ctrl+U) then
enter the table or column name filter values. They are wildcard searches so
that I can search for any part of the table or column name.
Generally I’ll only enter a table name to search on, then F5 to run it. The
query results will report the full table definition. If the results are too
long, or its just easier to do, I’ll use the column name filter.
There are few commented out filters that I like to keep handy. The first
limits the query to report only primary keys. The second searches for just date
columns or bit columns. The last commented out filter searches for foreign key
children that reference a table or column.
The columns in the query report table and column name, SQL Server data type,
and the null constraint. Also the default value for the column is reported. If
the column is an Identity, the value “=Identity” is displayed in the Default
column. Calculated columns also are displayed in the Default column with a “=”
prefix and the calculation defintion.
The result set also has a column for the index the column belongs to. If the
column’s index is a primary key the value “PK” appears in the Index column.
Otherwise the index will reported as “IX2”, “IX3”, and so on for all the indexes
defined on the table. Unique indexes get reported with “UQ” instead of “IX”.
If the column is a member of a composite index the index marker will be followed
by a dash and the position number of the column in the index. For example an
Order Line Item table may have the primary key columns OrderID and LineItemID,
which would be marked with “PK-1” and “PK-2” respectively.
On a side note, I think it is really critical that the tool you use to
discover what columns exist in the database report any indexes defined against
them. The first step in query performance tuning is to ensure that the columns
you are using to filter on do belong to an index. That is why this query
reports indexes along with column definitions as if they are first class
properties of the column.
The last column is the foreign key. The table and column name of the parent
column being referenced is reported.
Note that database columns (i.e. rows in the result set) can be reported
duplicate times if they belong to multiple indexes or foreign keys. Maybe I’ll
get around to fixing that one day.
This query work great for quick searches of columns in a database. However,
this is just a starting point. I will often modify the this query adding extra
nested filters or new columns. This column search query is a gateway script
that can be leveraged to do much more powerful things using the system
tables.
For example there is a commented out column in the select clause to report
the .NET framework’s CLR type instead of the SQL Server data type. I will often
uncomment this and append a space, the column name, and a semicolon to create
quick field or property definitions in C# classes.
I will also routinely use this to generate SQL scripts to drop or rebuild
default constraints, indexes or foreign keys. For example, I’ll add the
following column to the select list to generate script to drop Default
Constraints in the database: “'Alter Table ' + obj.name + ' Drop Constraint ' +
def.name”.
System queries are a really powerful tool to have in your tool belt to work
effectively with SQL Server (or any relational) database system. I hope you
find this query not only helpful for column searching, but also to learn and
build system queries of your own.