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

SqlSql Server-2008

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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 - Sql

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 - Sql

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

Solution 4 - Sql

I did use the following Statement to find all tables that could possibly hold binary-data/files.

SELECT 
    table_name 
FROM 
    INFORMATION_SCHEMA.TABLES T 
WHERE 
    T.TABLE_CATALOG = 'MyDatabase' AND 
    EXISTS ( 
        SELECT * 
        FROM INFORMATION_SCHEMA.COLUMNS C 
        WHERE 
            C.TABLE_CATALOG = T.TABLE_CATALOG AND 
            C.TABLE_SCHEMA = T.TABLE_SCHEMA AND 
            C.TABLE_NAME = T.TABLE_NAME AND 
            ( C.DATA_TYPE  = 'binary' OR
             C.DATA_TYPE  = 'varbinary' OR 
            C.DATA_TYPE  = 'text' OR
            C.DATA_TYPE  = 'ntext' OR
            C.DATA_TYPE  = 'image' )
            )

Solution 5 - Sql

You can use the system view INFORMATION_SCHEMA.COLUMNS. The data_type column has what you're looking for.

Categories
Recommended Sql Solutions
Recommended Sql Server-2008 Solutions

Previous Solution:

Powershell Get-ChildItem most recent file in directory

Powershell

Next Solution:

Why does my android activity always start scrolled to the bottom?

AndroidLayout

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSwissCoderView Question on Stackoverflow
Solution 1 - Sqlrs.View Answer on Stackoverflow
Solution 2 - SqlJim H.View Answer on Stackoverflow
Solution 3 - SqlSevenView Answer on Stackoverflow
Solution 4 - SqlSwissCoderView Answer on Stackoverflow
Solution 5 - SqlColinView Answer on Stackoverflow