怀疑从现有表中查找列的最大长度 [英] Doubt in finding maximum length of columns from existing table

问题描述

我想查看现有表格的每个列长度。





我正在使用游标，因为我有56列我可以不检查。



低于我给出的查询我用过但错了什么



可以任意一检查PLZ

 声明  @columname   varchar （ 50 ）
  declare  length_cursor  cursor   for  
 
 选择 Column_name 来自 INFORMATION_SCHEMA.COLUMNS 其中 TABLE_NAME = < span class =code-string>'  T_CompanyInformation' 
  open  length_cursor 
  fetch  next 从 length_cursor 到  @ columname  
 
  while   @@ Fetch_status  = 0 
 开始 
 插入 进入 temp 
（大小）
 选择 MAX（len（ @ columname ））来自 T_CompanyInformation 
 
  fetch  next 来自 length_cursor  into   @ columname  
  end  
  close  length_cursor 
  deallocate  length_cursor 

解决方案

试试Th是，

  SELECT  MAX（DATALENGTH（FeildName））
  AS  '  TEXTFieldSize' 来自 TableName 

有了问候，

Gopinath Natarajan

像这样（但有更好的数据......本质上是T_CompanyInformation结构的副本，只添加了IDENTITY列）：

 USE [cpqaAnswers] 
 GO 
 IF EXISTS（SELECT * FROM sys.objects WHERE object_id = OBJECT_ID（N''[cpqa]。[tbl_KW_T_CompanyInformationIdx]''） AND键入（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）
）
 
插入[cpqaAnswers] 。[cpqa]。[tbl_KW_T_CompanyInformationIdx] 
 SELECT * FROM [cpqaAnswers]。[cpqa]。[tbl_KW_T_CompanyInformation] 
 
 SELECT MAX [Idx] FROM [cpqaAnswers]。[cpqa]。[tbl_KW_T_CompanyInformationIdx ] 

我个人从不使用FETCH NEXT。这种索引方法适用于非索引原基的DROP，只留下具有硬索引的表。当指向伴随那些数字的非数字数据时，这当然很容易使用。

怎么样

 选择 Column_name，DATALENGTH（Column_Name），LEN（Column_Name）
 来自 INFORMATION_SCHEMA.COLUMNS  where  TABLE_NAME = '  T_CompanyInformation' 

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

解决方案

Try This,

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

With Regards,
Gopinath Natarajan

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".

How about

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

这篇关于怀疑从现有表中查找列的最大长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！