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.
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')
And I have this SQL for getting the maximum length of a value:
SELECT Max(Len(MyColumn))
FROM MyTable
But I can't figure out how to combine them. I am using SQL Server 2008.
To find the maximum value of a column, use the MAX() aggregate function; it takes a column name or an expression to find the maximum value. In our example, the subquery returns the highest number in the column grade (subquery: SELECT MAX(grade) FROM student ).
In SQL Server, you can use the COL_LENGTH() function to get the length of a column. More specifically, the function returns the defined length of the column, in bytes. The function accepts two arguments: the table name, and the column name.
SQL MIN() and MAX() FunctionsThe MIN() function returns the smallest value of the selected column. The MAX() function returns the largest value of the selected column.
select max(length(mycolumn)) from mytable; This works fine in hive QL.
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
This is something I use to profile data that might be helpful. Just change "YOUR TABLE NAME" to your table name. It is meant to show you where columns can be trimmed.
DECLARE @YourTableName sysname;
DECLARE @sql nvarchar(MAX) = ''
SET @YourTableName = YOUR TABLE NAME
CREATE TABLE #resultsTable (columnName varchar(100), columnLargestValueInData int, columnMaxLength int)
DECLARE @whileIter int = 1
DECLARE @whileTotal int
SELECT @whileTotal = COUNT(*) FROM sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(@YourTableName)
-- print 'whileTotal: ' + CONVERT(VARCHAR,@whileTotal) -- used for testing
WHILE @whileIter <= @whileTotal
BEGIN
SELECT @sql = N'INSERT INTO #resultsTable (columnName, columnLargestValueInData, columnMaxLength) SELECT ''' + sc.name + ''' AS columnName, max(len([' + sc.name + '])), ' + CONVERT(varchar,sc.max_length) + ' FROM [' + t.name + ']'
FROM sys.tables AS t
INNER JOIN sys.columns AS sc ON t.object_id = sc.object_id
INNER JOIN sys.types AS st ON sc.system_type_id = st.system_type_id
WHERE column_id = @whileIter
AND t.name = @YourTableName
AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar')
PRINT @sql
exec sp_executesql @sql
SET @whileIter += 1
END
SELECT * FROM #resultsTable
TRUNCATE TABLE #resultsTable
DROP TABLE #resultsTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With