?
Solved

SQL query to list fields based on size

Posted on 2011-05-10
10
Medium Priority
?
282 Views
Last Modified: 2012-05-11
Good Day Experts

I am interested in a SQL statement that will give a list of fields in a table grouped by type/size.  

Is this possible?  We are using SQL Server 2005.

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
10 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 1600 total points
ID: 35732016
Something like this?

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,  DATA_TYPE + case when isNull(CHARACTER_MAXIMUM_LENGTH,0)=0 then '' else '(' +  cast(CHARACTER_MAXIMUM_LENGTH as varchar) + ')' end as DATA_TYPE, COLUMN_DEFAULT
FROM myDatabase.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'myTable'
ORDER BY DATA_TYPE,  CHARACTER_MAXIMUM_LENGTH
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35732041
... just replace "myDatabase" with the name of your catalog and "myTable" with the name of your table.  You can then order by or group by which ever columns you choose.
0
 

Author Comment

by:Jimbo99999
ID: 35732634
Thank you for responding.  I was able to get the query to execute. However, it is not returing any data...just the column names.  Data is present in the table.  

I know this is not much to go one but can you think of anyway this is possible?  

Thanks,
jimbo99999
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:knightEknight
ID: 35733160
Perhaps I mis-understood ...
Do you need the query sorted by the size of the column, or by the size of the data IN the column?

If the later then order by len(columnName)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35733165
to clarify:

select col1, col2, col3
from myTable
order by len(col3)
0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 200 total points
ID: 35734317

SELECT[column_name]'Column Name',
[data_type]'Data Type',
[Character_maximum_length]'Maximum Length'
FROM[information_schema].[columns]
WHERE[table_name]='emp2'
order by [data_type], [Character_maximum_length]
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 200 total points
ID: 35735646
Select name, (Select name from sys.objects where type = 'u' and object_id = a.object_id), system_type_id, max_length from sys.columns a Where (Select name from sys.objects where type = 'u' and object_id = a.object_id) Is not null order by  object_id, system_type_id, max_length
0
 

Author Comment

by:Jimbo99999
ID: 35746076
Hey There

I did not state clearly...I apologize.  I would like to sort on the size of the column.  So, if I get all the char defined fields together have the largest ones first.

Thanks again for helping,
jimbo99999
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35746223
My first query above should do that, as would several other suggestions made earlier ...

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,  
          DATA_TYPE + case when isNull(CHARACTER_MAXIMUM_LENGTH,0)=0 then '' else '(' +  cast(CHARACTER_MAXIMUM_LENGTH as varchar) + ')' end as DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'myTable'
ORDER BY DATA_TYPE,  CHARACTER_MAXIMUM_LENGTH
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35746231
... again just change the 'MyTable' to your table name -- or if you want to see this for all tables, remove the WHERE clause.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question