How to list all the columns in a database of a specific data type?

Working with SQL Server can be quite interesting. It’s a quite powerful tool and you can really be creative with what you can do with those SQL Queries.

If you are looking to retrieve a list of all columns in your database which match a particular datatype, you can do that using this SQL Script.

 

SELECT OBJECT_NAME(cols.OBJECT_ID) TableName, cols.name ColumnName

FROM sys.columns AS cols

JOIN sys.types AS t ON cols.user_type_id=t.user_type_id

WHERE t.name = 'varchar' --you can change this to other valid datatypes as required

ORDER BY cols.OBJECT_ID;

 

The above query comes up with the List of Table Name and the Column Name which match a specific datatype as you can see in the above script if will list all columns which has the data type varchar, but what if you want to list all columns which has a data type of varchar(50) , you can do that with the following SQL Script. This Query will list all the columns in your database which has a specific data type and also the specific max length of the column.

 

 

SELECT OBJECT_NAME(cols.OBJECT_ID) TableName, cols.name ColumnName, cols.max_length ColumnSize

FROM sys.columns AS cols

JOIN sys.types AS t ON cols.user_type_id=t.user_type_id

WHERE t.name = 'varchar' --you can change this to other valid datatypes as required

And cols.max_length = 50
ORDER BY cols.OBJECT_ID;

 

Hope that was useful!