SELECT * and subtract a couple of fields?!

• Rob-350472

  SSCertifiable

  Points: 7197

  More actions

  April 3, 2008 at 1:59 am

  #797660

  SQLZ (4/2/2008)

  ---

  There's an even neater trick in Management Studio.

  Expand the table, select the Columns folder and drag it into the query window - all columns are added in a comma-seperated fashion.

  Now that is a pretty slick trick 🙂 Thanks SQLZ

• Ross McMicken

  SSCarpal Tunnel

  Points: 4436

  More actions

  April 4, 2008 at 7:35 am

  #798435

  Here's something I've used lately for listing columns in a query:

  select 'a.' + column_name + ','

  from information.schema.columns

  where table_name = 'table name you want'

  order by ordinal_position

  This gives me the prefix and the comma. I edit out the columns I don't want, as well as the comma after the last column.

  In general, I've found it very easy to write scripts to generate scripts when I'm updating many items, or doing data scrubbing during a prboject.

• Cliff Jones

  SSChampion

  Points: 10547

  More actions

  April 4, 2008 at 8:03 am

  #798455

  SQLZ (4/2/2008)

  ---

  There's an even neater trick in Management Studio.

  Expand the table, select the Columns folder and drag it into the query window - all columns are added in a comma-seperated fashion.

  That is a pretty slick trick. As much as I have used Management Studio I had no idea you could do that. My productivity just increased 20%.

  Thanks.

• tfifield

  SSCrazy Eights

  Points: 9655

  More actions

  April 4, 2008 at 11:23 am

  #798591

  There's a fairly easy way to do this using Dynamic SQL. If you mostly want all columns and a few that you want to omit then you can get a column list dynamically and select it (I mostly use this for inserts on tables that have many, many columns and I don't want to have to type in each column name).

  I have a UDF that returns all columns from a table except the ones I want to omit. This particular version was created for inserts so I leave out all computed columns.

  DECLARE

  @SQL VARCHAR(MAX)

  , @Columns VARCHAR(MAX)

  , @ColumnsToOmit VARCHAR(MAX)

  -- Get all columns from the Customer table except these.

  SET @ColumnsToOmit = 'ADRS_3,COUNTRY,BALANCE'

  SELECT @Columns = dbo.fnGetColumnList('Customers', @ColumnsToOmit)

  -- Set the SQL query

  SET @SQL = 'SELECT ' + @Columns + ' FROM Customers'

  EXEC (@SQL)

  The function to get the columns is below

  Todd Fifield

  -- =============================================

  -- Author: Todd Fifield

  -- Create date: 10/3/07

  -- Description:Return a string of columns that can

  -- be used for INSERT/SELECT type statements.

  -- Pass in the table name and which columns to omit.

  -- This version is used for Inserts so all computed columns

  -- are automatically omitted.

  -- =============================================

  CREATE FUNCTION [dbo].[fnGetColumnList]

  (

  @TableName VARCHAR(100)

  , @ColumnsToOmit VARCHAR(1000)

  )

  RETURNS VARCHAR(MAX)

  AS

  BEGIN

  DECLARE

  @CL VARCHAR(MAX) -- Returned Column List

  , @Comma VARCHAR(1) -- Set to Comma after first iteration

  SET @CL = ''-- Column List

  SET @Comma = ''

  SET @ColumnsToOmit = ',' + REPLACE(@ColumnsToOmit, ' ', '') + ','

  SELECT @CL = @CL + @Comma + ColName, @Comma = ','

  FROM

  ( SELECT C.[Name] AS ColName

  FROM syscolumns C WITH (NOLOCK)

  INNER JOIN sysobjects S WITH (NOLOCK) ON

  C.[id] = S.[id]

  WHERE S.[name] = @TableName

  AND C.iscomputed = 0

  AND C.xtype <> 189 -- Don't go TimeStamp

  AND CHARINDEX(',' + C.[name] + ',', @ColumnsToOmit) = 0

  ) AS X

  RETURN @CL

  END