How would I get the names of all the columns in a table that are of a specific type such as datetime?

Even better -- How can I do the same thing but with multiple tables joined together and then list the column name along with the table it comes from?


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
    -- 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'
| improve this answer | |

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'
| improve this answer | |

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:


Reference: https://msdn.microsoft.com/en-us/library/ms188348.aspx

| improve this answer | |
  • 4
    Please read - The case against INFORMATION_SCHEMA views from Aaron's blog. It will convince you not to use INFORMATION_SCHEMA views. – Kin Shah Jun 8 '16 at 17:58
  • I don't know sql server all that well, and I'm pretty sure @Aaron Bertrand has valid arguments against INFORMATION_SCHEMA. In an ideal world we could use INFORMATION_SCHEMA in the same way in all DBMS that supports them, but unfortunately it is often necessary to retrieve implementation specific details from the DBMS. Though, I don't see any problems using INFORMATION_SCHEMA for the OP's question. – Lennart Jun 8 '16 at 18:21
  • 1
    For consistency within SQL Server - often more important than consistency across all possible, however unlikely, DBMS platforms. – Aaron Bertrand Jun 8 '16 at 18:24

For a given table...

SELECT sc.name AS column_name, t.name AS data_type, t.max_length, t.[precision], t.scale
FROM sys.columns sc
INNER JOIN sys.types t ON sc.user_type_id = t.user_type_id
WHERE sc.object_id = OBJECT_ID('AdventureWorks2012.Person.Person')
ORDER BY sc.column_id

This will give you them in the order they are listed in the table. By using user_type you also cover for the possibility of user created data types. The last 3 fields are needed for data types that take extra parameters, eg VARCHAR(200), NUMERIC(12,3). If you see a -1 in the max_length field, then it's probably something like VARCHAR(MAX) or XML.

For multiple tables, it's tricky. You could create a table from your tables (maybe with a SELECT TOP(0) so as to not bother copying the data) and then plug it into the above. However if you have fields with the same name in those tables, then your table will not create. Remember to drop your table afterwards. You could do a temp table but they are trickier to find the object_id for.

| improve this answer | |

You can do this way:

   SELECT COLUMN_NAME FROM user_tab_columns WHERE 
     table_name = 'MYTABLE' AND DATA_TYPE = 'DATE'

| improve this answer | |
  • 1
    This is for SQL Server. – LowlyDBA Jun 8 '16 at 18:04
  • Sorry. I didn't pay attention on the tag. This is for Oracle. – Reginaldo Rigo Jun 8 '16 at 18:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.