Tuesday, January 08, 2013

Find all data columns of certain type (SQL Server)

The following SQL command will display all the columns of certain type. This works on Microsoft SQL Server. It will display table name and column name. In this case, we are displaying all columns of type datetimeoffset.
SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'datetimeoffset'
Source: link
More on datetimeoffset (link).


Brett Powell said...

Note that will return columns from views as well, you need to join to information_schema.columns to filter them.

I find sp_help very useful when writing queries in SSMS, if you set up a keyboard shortcut all you have to do is select text and it will execute sp_help '[selected_text]'

Brett Powell said...

Oops, I meant join to information_schema.tables