Is it possible to get the max length of a TEXT field?

I have a text field and want to know if any of the text exceeds 10,000 characters
I can do a select max(len(rtrim(convert(varchar(8000)))) on the field but I'm not able to do for more than 8000 and you can't manipulate TEXT datay type.
Any ideas?
Thanks!SELECT DATALENGTH(Col1) FROM myTable99

Is it possible to create the columns of a #Temp table base on a query result?

I’ve been trying without success something like this…

CREATE TABLE #table

(

(SELECT MAX(Column) FROM Table) varchar(50)

)

I'm working with SQL 2005; thank you for any help in advance.

Not quite like that.

You can SELECT ... INTO.

In the process, you need to provide a column Name for any computed or derived columns, and you can change the datatype.

Something like this:

Code Snippet

USE Northwind
GO

SELECT cast( max( EmployeeID ) AS decimal(6,2)) AS MaxEmp
INTO #MyTable
FROM Employees

|||

There are 2 options,

Create Table #Table (

ColumnValue Varchar(50)

);

Insert Into #Table

SELECT MAX(Column) FROM TableName;

--OR

SELECT Cast(MAX(Column) as Varchar(50)) ColumnValue Into #Table FROM TableName;

|||Thank you for the help provided so far. Another question on the same subject. How can the Column name be assigned dynamically after a query result? Thanks.
|||

You should use the aliase name. if you failed to give the aliase name for the expression sql server thow an error says that "No column was specified for column n on tablename".

Code Snippet

Select Max(Column) as MaxColumn Into NewTable From OldTable

Select Max(Column) MaxColumn into Newtable From OldTable

|||

As indicated above, No.

My apologies, I guess this statement was not clear enough.

In the process, you need to provide a column Name for any computed or derived columns...

sql