SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Getting TEXT, NTEXT and IMAGE columns in Database

By Sheraz Mirza,

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 

Total article views: 304 | Views in the last 30 days: 14
 
Related Articles
FORUM

ntext column problem?

Large table with empty ntext column does not free space

FORUM

SQL 2000 replicate a ntext column to SQL 2005, OK ?

SQL 2000 replicate a ntext column to SQL 2005, OK ?

FORUM

Hindi text in nText column SQL Server 2005

Hindi text in nText column SQL Server 2005

FORUM

Can I encrypt ntext and nvarchar(max) columns ?

Can I encrypt ntext and nvarchar(max) columns ?

FORUM

ntext vs other datatype

discover that cannot key in ' and \ and cannot hit enter key on the particular column that use ntext...

 
Contribute