在 SQL Server 数据库的所有表中查找特定类型的所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9678260/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find all columns of a certain type in all tables in a SQL Server database
提问by SwissCoder
How can I find all columns of a certain type (for example NTEXT) in all tables in a SQL Server database?
如何NTEXT在 SQL Server 数据库的所有表中找到某种类型（例如）的所有列？
I am looking for a SQL query.
我正在寻找 SQL 查询。
回答by rs.
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'
回答by Jim H.
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'
回答by Seven
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
回答by Colin
You can use the system view INFORMATION_SCHEMA.COLUMNS. The data_typecolumn has what you're looking for.
您可以使用系统视图INFORMATION_SCHEMA.COLUMNS。该data_type列包含您要查找的内容。
回答by SwissCoder
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' )
)
