Just an idea ...

Question

Just an idea ...

Phil Parkin

SSC Guru

Points: 246956
More actions
January 23, 2015 at 1:52 am

#296926

I've been working with some wide and unfamiliar tables recently. Scanning and finding column names can be a challenge in these cases.
So I started thinking that a nice SSMS function would be to create a 'right-click/sort' function on the 'Columns' node – for display only.
What do others think? Or is this functionality already available somewhere?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Neil Burton SSC-Insane Points: 22998 More actions · Answer 1

That definitely sounds like a good idea. We've got some tables that are 300-ish columns wide and I'd said at least at third of those are all nulls (don't ask, we used a third party to create this mess). Having some way of easily finding the column I want would be very useful.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

This is something that I noticed some period ago, and it's definitely a good idea - a filter for the columns to be implemented in ssms.

Sometimes I use the red-gate SQL Search (free), and sometimes I use the following query.

select c.name, tp.name

from sys.tables t

join sys.columns c on t.object_id = c.object_id

join sys.types tp on c.user_type_id = tp.user_type_id

where SCHEMA_NAME(t.schema_id)='dbo' and t.name = 'Matches' and c.name like '%ID%'

order by c.name

Igor Micev,My blog: www.igormicev.com

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

Igor Micev (1/23/2015)
This is something that I noticed some period ago, and it's definitely a good idea - a filter for the columns to be implemented in ssms.
Sometimes I use the red-gate SQL Search (free), and sometimes I use the following query.
select c.name, tp.name
from sys.tables t
join sys.columns c on t.object_id = c.object_id
join sys.types tp on c.user_type_id = tp.user_type_id
where SCHEMA_NAME(t.schema_id)='dbo' and t.name = 'Matches' and c.name like '%ID%'
order by c.name

something very similar, except I include the schema name and ignore sys tables, so that new systems I work on I can plug it in to Excel using a DB connection.

That way I keep SSMS on one screen the Column list spreadsheet on the other.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 4

Doesn't Intellisense do what you want here? Or are you looking for the datatype and constraints, etc, as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246956 More actions · Answer 5

Jeff Moden (1/23/2015)
Doesn't Intellisense do what you want here? Or are you looking for the datatype and constraints, etc, as well?

This is not about cutting code – SQL Prompt gives me all of that with no problem. It's more for answering quick questions: what's the length of the ProductName column in table X? There are other ways of getting to that, of course.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 6

Ah. Understood. In that case, I just use sp_help 'schemaname.tablename' and then search the results.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gazareth One Orange Chip Points: 27737 More actions · Answer 7

Yeah, I'd be using sys.objects etc for that, or the INFORMATION_SCHEMA views.

Could be nice to have sorting/filtering on the Object Explorer though.

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 8

Gazareth (1/23/2015)
Yeah, I'd be using sys.objects etc for that, or the INFORMATION_SCHEMA views.
Could be nice to have sorting/filtering on the Object Explorer though.

For quickly finding objects, there already is. Right click on (for example) TABLES in any database in Object Explorer and click on "Filter".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

...or you can use the Object Explorer Details view to sort the columns.

Just click on the "Columns" node in Object Explorer, then press F7 to open the details view and click the header of whichever detail element you want to sort by. - e.g. Name

You can dock the details view on the side of the window if that makes life easier as well.

Of course there are also various add-ins that will display tooltips with column type/size information when you hover over them in the editor.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Phil Parkin SSC Guru Points: 246956 More actions · Answer 10

mister.magoo (1/24/2015)
...or you can use the Object Explorer Details view to sort the columns.
Just click on the "Columns" node in Object Explorer, then press F7 to open the details view and click the header of whichever detail element you want to sort by. - e.g. Name
You can dock the details view on the side of the window if that makes life easier as well.
Of course there are also various add-ins that will display tooltips with column type/size information when you hover over them in the editor.

Nifty! Thanks MM.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Gazareth One Orange Chip Points: 27737 More actions · Answer 11

mister.magoo (1/24/2015)
...or you can use the Object Explorer Details view to sort the columns.
Just click on the "Columns" node in Object Explorer, then press F7 to open the details view and click the header of whichever detail element you want to sort by. - e.g. Name
You can dock the details view on the side of the window if that makes life easier as well.
Of course there are also various add-ins that will display tooltips with column type/size information when you hover over them in the editor.

Nice 🙂

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 12

I know this is fairly radical, but I enforce an alphabetic order to the column list in the schema. My only concession is pk first.

I got an incredible amount of whining, but after a while it died down as people got accustomed to the standard.

Don Simpson

I'm not sure about Heisenberg.

Phil Parkin SSC Guru Points: 246956 More actions · Answer 13

DonlSimpson (1/27/2015)
I know this is fairly radical, but I enforce an alphabetic order to the column list in the schema. My only concession is pk first.
I got an incredible amount of whining, but after a while it died down as people got accustomed to the standard.

Do all of your new columns begin with 'z'? 😀

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

below86 SSChampion Points: 11532 More actions · Answer 14

mister.magoo (1/24/2015)
...or you can use the Object Explorer Details view to sort the columns.
Just click on the "Columns" node in Object Explorer, then press F7 to open the details view and click the header of whichever detail element you want to sort by. - e.g. Name
You can dock the details view on the side of the window if that makes life easier as well.
Of course there are also various add-ins that will display tooltips with column type/size information when you hover over them in the editor.

Oh Magoo you've done it again. Thanks for the tip.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.