Find all columns of a certain type in all tables in a SQL Server database

How can I find all columns of a certain type (for example NTEXT) in all tables in a SQL Server database?

I am looking for a SQL query.

Solution 1:

You can use following query to return fields

SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'NTEXT'

Solution 2:

You're going to need INFORMATION_SCHEMA. Try something like:

SELECT c.* from INFORMATION_SCHEMA.columns c
INNER JOIN INFORMATION_SCHEMA.tables t ON t.table_name = c.table_name
WHERE c.data_type = 'int' AND t.table_type = 'base table'

Solution 3:

Also you can try

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'ntext'
ORDER BY c.OBJECT_ID;
GO