How to select column names by data type
Query sys.columns
and sys.types
like this:
select object_name(c.object_id) TableName, *
FROM sys.columns c
join sys.types t ON c.system_type_id = t.system_type_id
where t.name = 'datetime'
How would I get the names of all the columns in a table that are of a specific type such as datetime?
(output as schema_name | table_name | column_name | data_type
):
SELECT Object_Schema_name(c.object_id) as [SCHEMA_NAME]
,object_NAME(c.object_id) AS TABLE_NAME
,c.NAME AS COLUMN_NAME
,t.NAME AS DATA_TYPE
-- add / remove columns as per need
--,c.max_length AS MAX_LENGTH
FROM sys.all_columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
where Object_Schema_name(c.object_id) <> 'sys' -- not sys schema
-- filters
and t.name = 'Datetime'
-- and object_NAME(c.object_id) = 'someTableName'
You could use INFORMATION_SCHEMA.COLUMNS
. The documentation says that TABLE_SCHEMA
is not reliable for objects, but I assume it works for tables and columns:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = ?
Reference: https://msdn.microsoft.com/en-us/library/ms188348.aspx