How to get the lenght of the field

[sow12]

Hello:

I would like to check all usertable to find the field datatype is ntext and the length of this field is more than 1000 char.

I need to get tabel_name, column_name, Data_type, and the lenght of the field is more than 1000 char.

Do you have the sample for this?

Thanks,

SS

 

[ESquared]

There are no fields in SQL Server. I'll proceed on the risky assumption that you meant columns.

However, the ntext data type has no user-defined maximum length. It's got a server maximum length of something like 2.4 billion characters.

The answer to the question as you've stated it then is every ntext column in your database since every ntext column has a "length" of more than 1000 characters. You can see all those nifty columns in your database like so:

SELECT *
FROM Information_Schema.Columns
WHERE Data_Type = 'ntext'

Now, if you mean to find rows that have ntext data more than 1000 characters, you'll have to run a query against each table that has one or more ntext columns.

What actually are you looking for?

 

[sow12]

Hello:

Thanks for the message.

For example, here is some of my table.

TableOne
Column Name: Id DescriptionOne TitleOne
Datatype: int ntext char
Data 1 abcbdddd....>3000 char

TableTwo
Column Name: Id DescriptionTwo TitleTwo
Datatype: int ntext char
Data 1 sddfghhhertt....>2000 char

TableThree
Column Name: Id DescriptionThree TitleThree
Datatype: int ntext char
Data 1 dfffggh....<1000 char

I would like to get tabel name(TableOne,TableTwo) , culumn_name(DescriptionOne ,DescriptionTwo) whose type is ntext, datatype, DescriptionOne data string(abcbdddd....) and DescriptionTwo(sddfghhhertt.... data string which is more than 2000 char.


I have query:

select table_name, column_name, data_type from information_schema.column where data_type = 'text' and table_name in (select name from systobjects where xtype = 'u')

but how to get each datastring whose length is more than 2000 char?

Thanks,

SS

 

[macleod1021]

Select * from Table Where LEN(column_name) > 2000

im in ur stakz, overflowin ur heapz!

 

[ESquared]

You can't use len on an ntext column.

select * from yourtable where datalength(blah) > 4000 -- wide characters so twice the bytes

 

[sow12]

Hello:

Thanks for the help.

I do not need LEN(column_name). I need to find the lenght of data string.

Thanks

 

[bborissov]

SELECT datalength(blah) 
       FROM YourTable

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[sow12]

Hello:

I need to get table_name, column_name, Data_type, and the lenght of the data string is more than 1000 char together. How to get it?

SELECT datalength(blah)
FROM YourTable

only could get the length of the data string, but how to get table_name, column_name together?

Do you have the sample for this?

Thanks,

 

[ESquared]

There are many rows in each table. What you're asking doesn't make sense. You need to study the suggestions we're giving you and learn what they do and put it together.

What if you have a table with 25,000 rows and 5,000 of them have rows with data longer than 1000 characters?

Let's say it's table EinOwnUthing and your ntext column is ForSfeedMibeRain. You could try this:

SELECT
   'EinOwnUthingAS' TableName,
   'ForSfeedMibeRain' AS ColumnName,
   'ntext' AS DataType, -- this was a given, right?
   DataLength(ForSfeedMibeRain) / 2 As NTextLength
FROM EinOwnUthing
WHERE
   DataLength(ForSfeedMibeRain) > 4000

Now you get 5000 rows and 5000 lengths. What do you want to do with those? Show the max? The average? The count? The simple fact that there are any at all? (What do you want to show then for the length?)

Please provide sample data and the output you expect. For the next columns just put 'ntext here'.

 

[ESquared]

oops that was supposed to be:

[tt]'EinOwnUthing' AS TableName[/tt]

Also, you said:

I do not need LEN(column_name). I need to find the lenght of data string.

What in tarnation do you think DataLength(column_name) does? It gives lengths of strings in columns!!!