Timothy Klenke

Use System Queries to Browse Columns, Indexes, and FKs

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:

/* 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.

• Share This Post:
• 
• 
• 
•   Short Url: http://wblo.gs/eRM

Print | posted on Monday, February 17, 2014 1:29 PM

Copyright © Timothy Klenke

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski