Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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
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 1:59am
Comments
phil.o at 22-Mar-13 8:27am
   
something wrong
 
What exactly ?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try This,
 
SELECT MAX(DATALENGTH(FeildName))
 AS 'TEXTFieldSize' from TableName
 

With Regards,
Gopinath Natarajan
  Permalink  
Comments
kimberly wind at 22-Mar-13 8:23am
   
hey result still same :(
Gopinath_Rajan at 22-Mar-13 8:26am
   
what u want exactly. to find max length of particular colunm this is the way... :)
kimberly wind at 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
good
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]
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".
  Permalink  
Comments
RedDK at 22-Mar-13 16:14pm
   
Did I misinterpret the question? "Column length"? Or length of data per column? Which is it?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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

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

  Print Answers RSS
0 OriginalGriff 8,149
1 Sergey Alexandrovich Kryukov 7,287
2 DamithSL 5,614
3 Manas Bhardwaj 4,986
4 Maciej Los 4,910


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 22 Mar 2013
Copyright © CodeProject, 1999-2014
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