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:
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:
- How to Generate Insert Statements
- How to Generate Update Statements
- How to Generate SQL 'IN' Statements
- How to List the Columns of a Table, Quickly!
- Extracting a list of columns from a block of text
- How to Generate Views the easy way
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.
- General help
- Symbols and Keywords
- Built-in Functions
- The 'Where' clause
- Date Time formatting
- Command-Line Automation
- SQL Master Class
- HTML Master Class
- Popular Text Manipulations Made Easy «« bookmark this one
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