100

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SwissCoder
  • 2,514
  • 4
  • 28
  • 40

5 Answers5

145

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'
rs.
  • 26,707
  • 12
  • 68
  • 90
  • 3
    This will also include views – Daniel May 02 '17 at 08:11
  • 1
    This will also work on Azure SQL (aug 2018) and I used it to convert columns to nvarchar(max) because NText will be deprecated. `alter table [tablename] alter column [columnname] nvarchar(max)`. You can use `LEN(..)` etc. with nvarchar and not ntext. – JP Hellemons Aug 14 '18 at 09:50
  • @Daniel if you don't want views included, inner join on information_schema.tables, like so: `INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE'` – Mike P. Sep 30 '19 at 20:29
18

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'
Jim H.
  • 5,539
  • 1
  • 24
  • 23
8

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
Seven
  • 805
  • 12
  • 17
3

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' )
            )
SwissCoder
  • 2,514
  • 4
  • 28
  • 40
2

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

Colin
  • 846
  • 7
  • 16