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

如何在 SQLServer 数据库的所有表中找到特定类型的所有列(例如 NTEXT) ？

我正在寻找一个 SQL 查询。

53777 次浏览

小开

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

小开

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'

小开

最佳答案

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'

小开

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

小开

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