如何在SQL Server数据库中查找所有表格中特定类型(例如NTEXT
)的所有列?
我需要一个SQL查询。
如何在SQL Server数据库中查找所有表格中特定类型(例如NTEXT
)的所有列?
我需要一个SQL查询。
SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'NTEXT'
您需要使用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'
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
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' )
)
INFORMATION_SCHEMA.COLUMNS
,data_type
列中包含您需要查找的内容。
alter table [tablename] alter column [columnname] nvarchar(max)
。您可以在nvarchar和ntext中使用LEN(..)
等。 - JP HellemonsINNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME AND t.TABLE_TYPE = 'BASE TABLE'
。 - Mike P.