Doubt in finding maximum length of columns from existing table

Question

0.00/5 (No votes)

See more:

i would like to check each column length of the existing table.

am using cursor since i have 56 columns i can't check so.

below i given query which i used but something wrong

can any one check plz

SQL

declare @columname varchar(50)
declare length_cursor cursor for 

select Column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T_CompanyInformation'
open length_cursor 
fetch next from length_cursor into @columname

while @@Fetch_status =0
begin 
insert into temp
(size)
select MAX(len(@columname)) from T_CompanyInformation

fetch next from length_cursor into @columname
 end
 close length_cursor
 deallocate length_cursor

Posted 22-Mar-13 0:59am

kimberly wind

Add a Solution

Comments

phil.o 22-Mar-13 8:27am

something wrong

What exactly ?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Answer 1 · 2013-03-22T08:08:00

Solution 3

How about

SQL

select Column_name, DATALENGTH(Column_Name), LEN(Column_Name) 
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T_CompanyInformation'

Posted 22-Mar-13 8:08am

Corporal Agarn

RedDk · Answer 2 · 2013-03-22T07:54:00

Like this (but with better data ... essentially a copy of the structure of "T_CompanyInformation" only adding an IDENTITY column):

USE [cpqaAnswers]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tbl_KW_T_CompanyInformationIdx]') AND type in (N'U'))
DROP TABLE [cpqa].[tbl_KW_T_CompanyInformationIdx]
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_KW_T_CompanyInformationIdx](
		[Idx][int]IDENTITY(1,1),
			[KW_01][nvarchar](MAX),
				[KW_02][nvarchhar](MAX)
	)
	
INSERT INTO [cpqaAnswers].[cpqa].[tbl_KW_T_CompanyInformationIdx]
	SELECT * FROM [cpqaAnswers].[cpqa].[tbl_KW_T_CompanyInformation]
	
SELECT MAX[Idx] FROM [cpqaAnswers].[cpqa].[tbl_KW_T_CompanyInformationIdx]

Personally I never use FETCH NEXT. This indexing approach lends itself to DROP of the non-indexed primordium leaving only tables with hard indices. Which of course are easy to use when pointing to non-numerical data that accompanies those "digits".

Gopinath_Rajan · Answer 3 · 2013-03-22T02:18:00

Solution 1

Try This,

SQL

SELECT MAX(DATALENGTH(FeildName))
 AS 'TEXTFieldSize' from TableName

With Regards,
Gopinath Natarajan

Posted 22-Mar-13 2:18am

Gopinath_Rajan

Comments

kimberly wind 22-Mar-13 8:23am

hey result still same :(

Gopinath_Rajan 22-Mar-13 8:26am

what u want exactly. to find max length of particular colunm this is the way... :)

kimberly wind 22-Mar-13 8:32am

i have table it contains more than 50 columns. while i do data import am getting error string binary turncated. so they reason obvious is length of the column i have created in table. so need to alter the size of column . so i have to check the legnth of each columns that i imported to database then only i can able to insert in table . but checking max length for 50 columns is little tough . so i tried this method :(