列出具有最大长度和最大长度的所有 SQL 列

本文介绍了列出具有最大长度和最大长度的所有 SQL 列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我试图从表中获取所有列的列表，包括它们的数据类型、数据长度和该列中最长值的长度.

I am trying to get a list of all columns from a table with their data types, data lengths and the length of the longest value in that column.

我有这个 SQL 来获取列及其数据类型和长度:

I have this SQL for getting the columns and their data types and lengths:

SELECT Object_Name(c.object_id), c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length' FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE c.object_id = OBJECT_ID('MyTable')

我有这个 SQL 来获取一个值的最大长度:

And I have this SQL for getting the maximum length of a value:

SELECT Max(Len(MyColumn)) FROM MyTable

但我不知道如何组合它们.我使用的是 SQL Server 2008.

But I can't figure out how to combine them. I am using SQL Server 2008.

推荐答案

感谢您的建议.我想出了以下解决方案.它为我提供了我需要的数据，但有兴趣看看它是否可以提高效率.

Thanks for the suggestions. I have come up with the following solution. It gets me the data i need but would be interested to see if it can be made more efficient.

declare @results table ( ID varchar(36), TableName varchar(250), ColumnName varchar(250), DataType varchar(250), MaxLength varchar(250), Longest varchar(250), SQLText varchar(250) ) INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText) SELECT NEWID(), Object_Name(c.object_id), c.name, t.Name, case when t.Name != 'varchar' Then 'NA' when c.max_length = -1 then 'Max' else CAST(c.max_length as varchar) end, 'NA', 'SELECT Max(Len(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id) FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE c.object_id = OBJECT_ID('MyTable') DECLARE @id varchar(36) DECLARE @sql varchar(200) declare @receiver table(theCount int) DECLARE length_cursor CURSOR FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA' OPEN length_cursor FETCH NEXT FROM length_cursor INTO @id, @sql WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @receiver (theCount) exec(@sql) UPDATE @results SET Longest = (SELECT theCount FROM @receiver) WHERE ID = @id DELETE FROM @receiver FETCH NEXT FROM length_cursor INTO @id, @sql END CLOSE length_cursor DEALLOCATE length_cursor SELECT TableName, ColumnName, DataType, MaxLength, Longest FROM @results