Click here to Skip to main content
13,044,543 members (60,841 online)
Rate this:
Please Sign up or sign in to vote.
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

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
insert into temp
select MAX(len(@columname)) from T_CompanyInformation
fetch next from length_cursor into @columname
 close length_cursor
 deallocate length_cursor
Posted 22-Mar-13 0:59am
phil.o 22-Mar-13 8:27am
something wrong

What exactly ?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Try This,

 AS 'TEXTFieldSize' from TableName

With Regards,
Gopinath Natarajan
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 :(
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Like this (but with better data ... essentially a copy of the structure of "T_CompanyInformation" only adding an IDENTITY column):
USE [cpqaAnswers]
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](
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".
RedDK 22-Mar-13 16:14pm
Did I misinterpret the question? "Column length"? Or length of data per column? Which is it?
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

How about
select Column_name, DATALENGTH(Column_Name), LEN(Column_Name) 

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100