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 |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2006-10-16 : 05:37:14
|
| I need query that returns all the columns,with datatypes and datatype length from all the tables in my SQL Server 2005 database. selcting from the sys.columns gives me this, however the datatype lengths are giving me problems as the sys.columns only returns a max_length datatype length. Calling "sp_help tablename" also only returns the max_length value.For example for one column that is a nchar datatype has a datatype length of 10. In the sys.columns view the maxlength value for this column is 20. 1. How can I get the correct value in a sql query? Sql 2000 returns the correct value as it returns "prec" which contains the correct length but 2005 does not return prec?2.Also is there a way that I can view the sql behind system views as it doesn't have a "modify" option when you right click on it?Thanks for the help |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 05:44:14
|
| You are getting the right values. Learn more about data types!NVARCHAR(10) takes 20 bytes of storage.Peter LarssonHelsingborg, Sweden |
 |
|
|
adlo
Posting Yak Master
108 Posts |
Posted - 2006-10-16 : 05:50:35
|
| I don't want to get the byte value but I need the length value i.e. in this case 10! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-10-16 : 06:01:57
|
| case when data_type in ('nvarchar', 'nchar') then max_length / 2 else max_length end as NewMaxLengthPeter LarssonHelsingborg, Sweden |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-16 : 07:16:06
|
| SELECT COLUMN_NAME, CHARACTER_OCTET_LENGTH FROM INFORMATION_SCHEMA.COLUMS WHERE TABLE_NAME='myTable'You're better off using the INFORMATION_SCHEMA views wherever possible.You also cannot modify system views. |
|
|
|
|
|
|
sys.columns in 2005 does not give column length