look up text fields in a database

Is there a way to run a query to find which tables and which field in my database is a text field ?
The database has quite a few tables and if there is an easy way to do this would be great, otherwise I have to open each table and check there.

Also, is there any difference (cons/pros) between varchar(MAX) and text fields?  I am using MS SQL 2008
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
You could run a query like
SELECT COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'yourTableName'

Open 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.
0
Kelvin SparksCommented:
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
0
AleksAuthor Commented:
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 ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Kelvin
0
Kelvin SparksCommented:
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
0
ZberteocCommented:
Use this to find both TEXT and NTEXT:
select TABLE_NAME,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE like '%text'

Open 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) .
0
AleksAuthor Commented:
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.
0
AleksAuthor Commented:
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 ?
0
ZberteocCommented:
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.
Altering TEXT to VARCHAR(max) and NTEXT to NVARCHAR(MAX) will not cause any data lost.

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 ?
The INFORMATION_SCHEMA refers to the current database only so you can't have results from multiple databases.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
You are right !
0
AleksAuthor Commented:
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:

:)
0
ZberteocCommented:
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'

Open in new window

0
AleksAuthor Commented:
That was a mistake on my end. wrong button.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.