T-SQL to find all columns in DB of a certain data type

This script finds all columns which are of a certain data type including any user defined data types.

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, 
    t.name, c.max_length, c.precision, c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'tinyint' --you can change text to other datatypes
--and OBJECT_NAME(c.OBJECT_ID) = 'Class_card'
ORDER BY c.OBJECT_ID;
GO
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: