look up text fields in a database

You could run a query like

SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'yourTableName'

Select allOpen in new window

and filter by DATA_TYPE.

As for the difference between varchar(MAX) and text... Microsoft deprecated the text data type after SQL Server 2005. varchar(MAX) is now the preferred data type. Internally, SQL always stores text columns as BLOBs (Binary Large OBjects) and some of the text functions are not available on them.

SELECT table_name [Table Name], column_name [Column Name]
FROM information_schema.columns where data_type = 'NTEXT'

should give you the table and column name for every text datatype.

The text datatype is deprecated which means MS will cease supporting atr some stage in the future. Convert all to varchar(Max). They're much more user friendly. You can use them with a DISTINCT statement, group by them, use in a where statement and so on.

Kelvin

I meant I don't know which tables have text fields. So I want to run a script on the DB that will return the table and field name of text fields.

Is there a blob type ?

The script I listed will give you that data (table name and column name)

Kelvin

The Blob ddata type was an Oracle data type if I recall correctly. They have Blob and Clob clob has textural data, Blob holds binary data - equivalent to varchar(Max) and Varbinary(Max) or to use the deprecated datatypes Text and Image.

Kelvin

Use this to find both TEXT and NTEXT:

select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%text'

Select allOpen in new window

NTEXT and TEXT are deprecated and should not be used going further. Strting functions don't work on TEXT types unless you cast them to VARCHAR(MAX) .

I understand, this is why I want to find the fields and change them to varchar(max). Running the script .. also, if I change the text fields to varchar(max) I just want to make sure I don't lose any data.

Also. Seems like it shows results from other databases even though I am running it only in mine. Can we show the name of the database in the results ?

You are right !

I've requested that this question be closed as follows:

Accepted answer: 0 points for amucinobluedot's comment #a41226933
Assisted answer: 100 points for Russ_Suter's comment #a41224573
Assisted answer: 400 points for Zberteoc's comment #a41226864

for the following reason:

:)

You don't have to request to close just award the points.

One more thing. Run the script bellow in Management Studio and then copy the result and paste it into a new window and execute it:

select 
    'ALTER TABLE '+TABLE_NAME+' ALTER COLUMN '+COLUMN_NAME+' '+REPLACE(DATA_TYPE,'text','')+'varchar(max)'+CHAR(13)+CHAR(10)+'GO' 
from 
    INFORMATION_SCHEMA.COLUMNS 
where 
    DATA_TYPE like '%text'

Select allOpen in new window

That was a mistake on my end. wrong button.