Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stonebreaker
Yak Posting Veteran
92 Posts |
 Posted - 2007-09-12 : 16:00:33
|
| I'm trying to create a stored proc that will give me a list of columns within a table, and the maximum data length in each column. I want to find the max data length so I can reduce the column size. So far I have been able to get the list of columns along with the maximum column size:declare@tablename varchar(50),@columnname varchar(50)set @tablename = select t.[name] as table_name,c.[object_id],c.[name] as column_name,c.max_length as column_lengthfrom sys.tables t inner join sys.columns con t.object_id = c.object_id where t.name like @tablenameAny help getting the max(len(<column>)) would be greatly appreciated. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-12 : 16:04:17
|
| check out..information_schema.columns... everything is there--------------------keeping it simple... |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-12 : 16:09:07
|
| There are many ways to get at that information. But, here is a link to some stuff that will do that: [url]http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm[/url]. Spefically, look for Chapter 2: Data profiling reports. It's a zip file with a couple stored procedures you can use / modify that will return that informaion. |
|
|
|
stonebreaker
Yak Posting Veteran
92 Posts |
Posted - 2007-09-13 : 08:57:28
|
quote:
Originally posted by Lamprey
There are many ways to get at that information. But, here is a link to some stuff that will do that: [url]http://www.msftdwtoolkit.com/ToolsandUtilities/ToolandUtilities.htm[/url]. Spefically, look for Chapter 2: Data profiling reports. It's a zip file with a couple stored procedures you can use / modify that will return that informaion.
Thank you, that did the trick. You learn something new every day - I never thought of putting the whole sql statement into a variable and executing it that way. |
|
|
|
|
|
|
need to create list of columns w/ max data length