Max data length of a NTEXT column

Question

Post reply

Max data length of a NTEXT column

jonathan_hwang

Ten Centuries

Points: 1249
More actions
June 25, 2004 at 11:50 am

#108252

I got a cool project to figure out what are the max data length of a particular NTEXT column within one of our table.
The problem is...we have many tables that contains NTEXT type column and we would like to find out what is the max data length for any particular table per column basis.
IS there a easy way to figure this out.... We tried to use
DATALENGTH function. But it is taking forever... Can we look that up under any system table? Or any other tricky functions?
JON
DBA/OLAP Developer -
San Francisco, CA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 1

You can try extracting the information from

select * from information_schema.columns

Regards

Peter

jonathan_hwang Ten Centuries Points: 1249 More actions · Answer 2

Peter, The info Schema view will only tell me the max stroage capacity of a particular data type.

For example:

Ntext -->2147483646

varchar --> 255

My requirement is the follow: Our team need to know... we have columns within our production tables that uses Ntext data type. We would like to measure the data length of each ntext column row and find out what the max ntext row is.

thanks

DBA/OLAP Developer -
San Francisco, CA

fperkins Ten Centuries Points: 1029 More actions · Answer 3

Perhaps something in here would push you in the right direction:

http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=629

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 4

Yes, sorry Jonathon. But you could use the following script to find the maximum record length that could be returned by a query (using datalength) with reference to your current data:

declare @columns varchar(8000), @tablename varchar(255)

select @columns = '', @tablename = 'banks'

select @columns = @columns + ' + max(datalength([' + column_name + ']))' from information_schema.columns where table_name = @tablename

select @columns = substring(@columns, 4, 8000)

select 'select ' + @columns + ' from ' + @tablename

exec ('select ' + @columns + ' from ' + @tablename)

Unfortunately this won't work if there are a lot of columns in a table. varchar(8000) isn't large enough for @columns.

Regards

Peter

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

you say datalength is taking too long ? do you mean the query takes too long or the whole process of going through them takes too long?

i assume your doing something like

select max(datalength(field1)) from table1

how about running the following overnight

declare @tbname nvarchar(100)

declare @flname nvarchar(100)

declare @strsql as nvarchar(1000)

declare @maxlen as bigint

declare curs1 cursor for select sysobjects.name,syscolumns.name where sysobjects.id=syscolumns.id and sysobjects.xtype='u'

open curs1

fetch next from curs1 into @tbname,@flname

while @@fetch_status=0

begin

set @strsql='select @,tbname,@flname,max(datalength(['+@flname+']) from '+@tbname

exec sp_executesql @strsql

fetch next from curs1 into @tbname,@flname

end

close curs1

deallocate curs1

hopefully you should get a list with tablename,fieldname,max datalength

MVDBA