Getting TEXT, NTEXT and IMAGE columns in Database

info-59812, 2017-06-02 (first published: 2017-05-31)

As per Microsoft documentation https://docs.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql TEXT , NTEXT and IMAGE column will be removed from future , and are all alternative data tyes

TEXT will be VARCHAR(MAX)

NTEXT will be NVARCHAR(MAX)

IMAGE will be VARBINARY(MAX)

this script is useful to see all these column in whole database and will display Table name , Column Name and Data Type heading for better understanding ,

System Table sys.types is used to check system_type_id for every data type, so we can use same script to find any specific data type columns in whole database by just changing value for system_type_id IN list

in above query 34,35,and 99 are system_type_id's for TEXT, NTEXT and IMAGE data types

NOTE: above query will display one extra table "dtproperties" which is because microsoft changed "XTYPE" to "U" in object types after SQL SERVER 7. just avoid that column

select o.name TABLE_NAME, c.name COLUMN_NAME, t.name DATA_TYPE from sys.objects o JOIN sys.columns c ON c.object_id = o.object_id JOIN sys.types t ON c.system_type_id = t.system_type_id and o.type = 'U' and t.system_type_id IN (34,35,99)

Getting TEXT, NTEXT and IMAGE columns in Database

Rate

Share

Share

Rate