Get SQL table column names in a string

SELECT * is all well and good for quick debugging and manually run statements, but when you are inserting the results into a table within a stored proc, the dangers of a SELECT * and the problems caused by changes to the underlying table should be all too obvious.

However, picture the scene - a Friday afternoon and all you want to do is head to the pub, but you know you should finish your code properly.  You have a SELECT * statement in a proc or a view, but it pulls all the fields from a table with over 100 columns ... you're not really going to start typing every field name are you?  Well I'm not - so I would hit ALT-F1 on the table name, cut and paste into excel and add commas - or do a select name + ',' from syscolumns and paste the result into my code.  However, even that is too much effort for a lazy DBA like me, so I bring you <fanfare> sp_tablecolumns.

CREATE PROC sp_tablecolumns @object varchar(100)    AS    SET NOCOUNT ON    DECLARE @STRING VARCHAR(1000)    SET @STRING = ''    SELECT @STRING = @STRING + '[' + name + '], '    FROM syscolumns WHERE id = OBJECT_ID(@object)    ORDER BY ColOrder    SELECT LEFT(@STRING, LEN(@STRING) - 1)

Stick this in the master database for it to run for all db's (with relevant permissions if necessary) or just drop into a single database (if you're not a DBA and your own won't let you near the master db!).  Then execute it or set this up on a hot key, and with one key press you get a nice comma separated string of fields you can replace that * with ... and your beer is still cold!