SQL Query to List Tables Containing Text, NText, or Image Columns
In some cases, you may want to retrieve a list of database tables that contain specific types of columns such as text, ntext, or image. To achieve this, you can use the following SQL query.
SQL Query:
SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
JOIN [sys].schemas s ON t.[schema_id] = s.[schema_id]
WHERE EXISTS
(
SELECT 1 FROM [sys].columns c
WHERE c.[object_id] = t.[object_id]
AND c.system_type_id IN
(
34, -- image
35, -- text
99 -- ntext
)
);
This SQL query retrieves a list of tables from the database that have columns of types image, text, or ntext. It works by joining the sys.tables and sys.schemas system views, then checks if any columns in the table match one of the specified system types for text, ntext, or image data.
