在SQL Server数据库中的所有表中查找特定类型的所有列

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

如何在SQL Server数据库中的所有表中查找特定类型(例如NTEXT)的所有列?

I am looking for a SQL query.

我正在寻找一个SQL查询。

5 个解决方案

#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'

#2

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

需要INFORMATION_SCHEMA。尝试:

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'

#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

#4

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

您可以使用system view INFORMATION_SCHEMA.COLUMNS。data_type列具有您要查找的内容。

#5

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' )
            )

#1