SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


i need the max length value of every column in every table in a database


i need the max length value of every column in every table in a database

Author
Message
Snargables
Snargables
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 805
I'm creating some dynamic sql to loop through each table and column and basically generate some dynamis sql that will get the max length of each column in each table in a database. Then store it locally into a table like the below.


create table TableColLengths
(
id int identity(1,1)
, tablename varchar(255)
, colName varchar(255)
, MaxColLength bigint
)

I'm going to write it myself however didnt know if somewone has already done it so i dont have to go though the hassle.
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28309 Visits: 39955
something like this? also, max_length only makes sense for char types, so you mean like varchar/nvarchars right?

SELECT
OBJECT_NAME(OBJECT_ID) AS tablename,
name AS colName,
TYPE_NAME(system_type_id),
CASE
WHEN TYPE_NAME(system_type_id) IN ('NCHAR','NVARCHAR')
THEN max_length / 2
ELSE max_length
END AS MaxColLength
FROM sys.columns
WHERE TYPE_NAME(system_type_id) IN ('NCHAR','NVARCHAR','CHAR','VARCHAR')



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Erin Ramsay
Erin Ramsay
SSC Eights!
SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)SSC Eights! (821 reputation)

Group: General Forum Members
Points: 821 Visits: 1099
That's seems like an interesting task. What do you hope to gain from it?
Snargables
Snargables
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 805
I’m converting a client’s db to our internal structure. In doing so I need to bring their files down. Import them into sql then convert them to my internal structure. There are about 200 files ranging in all sizes. the first time I bring in all the files I usually set the cols to a data type that will give the lease amount of error. In some cases I make em all varchar(max) just to get the file loaded. I then go through and create the schema based on the data types the client sends. However in this instance it is not only outdated however but on a PDF and not from a sql server database so the types are all different.

Nonetheless, I get the data loaded into the dummy table w/ varchar(max) cols. then I go back and manually create the table based on the clients schema spec. Then I try and insert the dummie table into the newly create table. When I do so I find I get a lot of truncation errors. Some of these tables have 50+ columns so it's impossible to see where the truncation is occurring. Hence the below sql I generated shortly after I posted this. Rather than doing it for every table I simply made it to run for a single table. it has paid off and now I can get through all my truncation issues when they occur relatively quickly. When i have mor etime i''ll play w doing what i originally planned

DECLARE @outtersql VARCHAR(max)

SET @outtersql=''

DECLARE @innersql VARCHAR(max)

SET @innersql=''

DECLARE @tablename VARCHAR(1000)

SET @tablename = 'put_Table_name_here'

DECLARE @colname VARCHAR(1000)

SET @colname = ''

DECLARE @sql VARCHAR(max)

SET @sql=''

SELECT st.name tablename
,sc.name colname
,0 processed
INTO #temp
FROM sys.tables st
JOIN sys.columns sc
ON sc.object_id = st.object_id
WHERE st.name LIKE @tablename
ORDER BY st.name
,sc.column_id

WHILE EXISTS (SELECT TOP 1 *
FROM #temp
WHERE processed = 0)
BEGIN
SET @sql= ''
SET @innersql = ''
SET @outtersql = ''

SELECT TOP 1 @tablename = tablename
FROM #temp
WHERE processed = 0

SET @outtersql = ' select ''' + @tablename + ''' tableName, '
SET @innersql = ' from ( SELECT '

WHILE EXISTS (SELECT TOP 1 *
FROM #temp
WHERE processed = 0
AND tablename = @tablename)
BEGIN
SELECT TOP 1 @colname = colname
FROM #temp
WHERE processed = 0
AND tablename = @tablename

SET @outtersql = @outtersql + ' max([' + @colname + ']) '
+ '[MaxLength_' + @colname + '], '
SET @innersql = @innersql + ' len([' + @colname + ']) ' + '['
+ @colname + '], '
UPDATE #temp
SET processed = 1
WHERE processed = 0
AND tablename = @tablename
AND @colname = colname
END
SET @innersql = @innersql + 'from ' + @tablename
+ '(nolock) ) tt'
SET @innersql = Replace(@innersql, ', from', ' from ')
SET @outtersql = Reverse(Substring(Reverse(@outtersql), 2, 100000))
SET @sql= @outtersql + @innersql
SET @sql = Replace(@sql, ', from ', ' from ')

execute( @sql)

UPDATE #temp
SET processed = 1
WHERE processed = 0
AND @tablename = tablename
END

UPDATE #temp
SET processed = 0

DROP TABLE #temp
sqldriver
sqldriver
SSC Eights!
SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)

Group: General Forum Members
Points: 976 Visits: 2518
i run into truncation issues a lot. i've been using this to check the max length of raw table column values when they won't load to my processing table:


declare @thing nvarchar(max), @table sysname
select @table='j3688723', @thing=''
select @thing=@thing+'select '''+@table+''' as table_name,'''+column_name+''' as column_name,
max(len('+column_name+')) as column_length from '+@table+' union all '
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'
set @thing=left(@thing,len(@thing)-9)
exec(@thing)


Snargables
Snargables
Right there with Babe
Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)Right there with Babe (743 reputation)

Group: General Forum Members
Points: 743 Visits: 805
bery cool. i will take that and use it for my own. a little more intense on the system but much less code
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7921 Visits: 7155
It's a huge waste to do a separate SELECT from the table for each column.

You can do a MAX(LEN(column_name)) for all columns in one SELECT statement, and hopefully thus do only one scan (or at least fewer scans) of the table.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
sqldriver
sqldriver
SSC Eights!
SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)SSC Eights! (976 reputation)

Group: General Forum Members
Points: 976 Visits: 2518
ScottPletcher (4/2/2013)
It's a huge waste to do a separate SELECT from the table for each column.

You can do a MAX(LEN(column_name)) for all columns in one SELECT statement, and hopefully thus do only one scan (or at least fewer scans) of the table.


Would you mind showing me what you mean?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28309 Visits: 39955
I believe he means a single query, getting all the max lens in one query:

SELECt
Max(len(col1)) As m1,
Max(len(col2)) As m2,
Max(len(col3)) As m3
FROM yourTable



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26195 Visits: 17539
Using your dynamic code as an example this will produce the same results but as a single query instead of 1 query for each column.


declare @thing nvarchar(max), @table sysname = 'SomeTable'

select top 1 @thing = 'SELECT ''' + @table + ''' as TableName, ' + STUFF(
(
select ', MAX(LEN(' + COLUMN_NAME + ')) as ' + COLUMN_NAME
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'
for XML path('')), 1, 1, '') + ' FROM [' + @table + ']'
from information_schema.columns where table_name=''+@table+'' and data_type <> 'ntext'

exec sp_executesql @thing



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search