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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 SuterSenior Software DeveloperCommented:
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.
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
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 ?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

Kelvin
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
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) .
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.
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 ?
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.

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 !
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:

:)
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

AleksAuthor Commented:
That was a mistake on my end. wrong button.
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.