NimbleText

Manipulate text and data with light-weight patterns

How do you list the columns of a table, quickly!

My favored way of quickly getting a list of the columns for a table is to run sp_help '<tableName>' -- for example:

sp_help 'dbo.contacts'

This outputs a lot of metadata about the table, and the second table of metadata is a list of the columns, their types, defaults etc. I just copy those columns names into the data textbox of NimbleText, and then I'm ready to manipulate the list in any way I need (for example, to generate insert statements, or update statements, or to define a new view).

But there are plenty of other techniques.

You could just as well type:

Select 
  Column_Name 
from 
  INFORMATION_SCHEMA.COLUMNS 
where 
  Table_Schema + '.' + Column_Name = 'dbo.Contacts'

or, if you prefer systables you could try:

Select 
  c.Name 
from 
  sysobjects o 
inner join syscolumns c on o.id = c.id 
where 
  o.name = 'Contacts'

Another technique is to copy them out of the table designer. In SQL Server Management Studio (SSMS), use the object explorer to navigate to the table in question, right click, select 'Design.' Once you have the table designer open, you can highlight all of the columns (by clicking the row header) and copy the full list of columns (and their types) into your clipboard. This might seem a little cumbersome compared to the other methods, but it *is* particularly useful if you need to copy out the name of some columns while you are half way through editing them, or have finished editing them but haven't updated the database yet.

Here's another technique that I used to use because of its speed:

In SSMS, turn on "Results to Text" (by pressing Ctrl-T) and execute a query that returns 0 rows, like this:

Select * from dbo.Contacts where 2=1

Saying 'where 2=1' just guarantees that no rows are returned, and it's extra useful because it's so quick to type.

The result will be a list of all the columns, separated with spaces.

You can use replace (Ctrl-H) to remove the spaces. But unfortunately if any of the column names have spaces, you'll remove those in the process. So this is not a particularly reliable technique. But it is about the quickest way to just look at the column names, if you also know that you are about to write a query against that table (as you already know you had to write most of this query anyway)

Why are we doing this in the first place? No doubt we have some purpose in getting the list of columns. We might want to generate an insert statement, an update statement, a stored procedure, a view, or manipulate the text in some other way. No doubt, getting the list of columns is not an end in itself.

Once you have the list of columns, the simplest way to manipulate it is with NimbleText.

We have a whole series of articles on generating and manipulating queries with NimbleText.

We've also written guides on generating many other ad-hoc queries:

Further help

You can also get general help, help on all the symbols and keywords, or on the built-in functions, filtering with a where clause, help with the powerful command-line automation, or applying custom formats to your dates and times.

You need to purchase a license to unlock all the features in NimbleText.

If you haven't downloaded NimbleText yet, then for added power, privacy and versatility I sincerely think you should download it now.

Download NimbleText