SQL query to list fields based on size
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
knightEknight
... 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.
ASKER
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
I know this is not much to go one but can you think of anyway this is possible?
Thanks,
jimbo99999
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)
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)
to clarify:
select col1, col2, col3
from myTable
order by len(col3)
select col1, col2, col3
from myTable
order by len(col3)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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_L
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'myTable'
ORDER BY DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,
DATA_TYPE + case when isNull(CHARACTER_MAXIMUM_L
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'myTable'
ORDER BY DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
... again just change the 'MyTable' to your table name -- or if you want to see this for all tables, remove the WHERE clause.