Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Generate Column lists for a table

Sep 18 2012 4:05AM by Madhivanan   

Often, you are advised not to use * in your SELECT statement. It is because the code that depends on * may be  broken if a column is added or removed. If you have a view that uses *, it will not reflect newly added/removed column until the view is refreshed. Also sometimes people may be lazy to type out all the columns in case there are tens of columns. Do you wonder if there is an easy way other than manually typing all the columns? Yes there are at least three different methods that I know.

Create this table

create table sales_details
(
	sales_id int identity(1,1),
	cust_id char(10) not null,
	product_id char(12) not null,
	sales_date datetime not null,
	sales_amount decimal(12,2),
	qty int,
	remarks varchar(100)
)

 

1 Make use of Script Table As option from SSMS

In the object explorer navigate to a table ;right click on table name; Script Table As—>SELECT To—>New Query Editor Window

The new query window will get opened with SELECT statement that lists out all columns

These are the screenshots for your reference

 

script

 

result

 

2 Make use of Information_schema.columns view and generate column list

SELECT 
	COLUMN_NAME+',' AS COLUMN_NAME 
FROM 
	INFORMATION_SCHEMA.COLUMNS 
WHERE 
	TABLE_NAME='sales_details'

The above creates the following result

sales_id,
cust_id,
product_id,
sales_date,
sales_amount,
qty,
remarks,

All you have to do is to delete last comma and add SELECT before first column and add FROM sales_details after the last column. This is very tricky with small amount of manual work.

3 Make use of Information_schema.columns view and generate column list dynamically based on table name

If you still want to avoid small amount of manual work of method 2, you can use this method by passing table name as parameter

DECLARE @SQL VARCHAR(8000), @TABLE_NAME VARCHAR(128)
SELECT 
	@SQL='', @TABLE_NAME ='sales_details'
SELECT 
	@SQL=@SQL+COLUMN_NAME+',' 
FROM 
	INFORMATION_SCHEMA.COLUMNS 
WHERE 
	TABLE_NAME=@TABLE_NAME 
SET @SQL='SELECT '+LEFT(@SQL,LEN(@SQL)-1)+' FROM '+@TABLE_NAME 
SELECT @SQL 

The result of the above code is as follows

SELECT sales_id,cust_id,product_id,sales_date,sales_amount,qty,remarks FROM SALES_DETAILS

Note : If you want to omit some columns from the SELECT statement add a WHERE clause to INFORMATION_SCHEMA.COLUMNS view to omit those columns.

Tags: 


Madhivanan
3 · 40% · 12947
12
 
0
Lifesaver
 
 
0
Learned
 
0
Incorrect



Submit

8  Comments  

  • Two more ways to do this

    1) In object explorer, expand table and then drag a column node to query window, all the columns will be selected

    2) Write down query with select *, open query designer and click OK. This will also add list of columns to select statement. But This method loss the collate keywords in query ( only required in few cases)

    commented on Sep 18 2012 4:18AM
    Chintak Chhapia
    40 · 5% · 1470
  • Thats cool too Chintak Chhapia. Thanks for the quick reply :)

    commented on Sep 18 2012 4:26AM
    Madhivanan
    3 · 40% · 12947
  • Thanks abhIShek BandI for your feedback

    commented on Sep 18 2012 6:34AM
    Madhivanan
    3 · 40% · 12947
  • Thanks Datta for the link :)

    commented on Sep 20 2012 2:42AM
    Madhivanan
    3 · 40% · 12947
  • Thanks For Sharing...

    commented on Sep 24 2012 1:51PM
    Jagdish Ilasariya
    2047 · 0% · 8
  • This is how I do this:

    I use either the += statement:

    DECLARE    @AltColumns NVARCHAR(MAX)--= ''
    
    SELECT    @AltColumns+= ', ' + C.Name
    FROM   sys.Columns C
    INNER JOIN sys.Tables T ON C.Object_ID = T.Object_ID
    WHERE    T.Name = 'Table_Name'
    
    PRINT @AltColumns
    

    Or I use the for XML statement (This i use for over remote servers)

    SELECT  t.name
          , Col.ColNames
    FROM    [LINKED_SERVER].[DATABASE].sys.tables T
        	OUTER APPLY (SELECT	', [' + C.name + ']'
        				 FROM	[LINKED_SERVER].[DATABASE].sys.columns C
        				 WHERE	t.object_id = C.object_id
        				 ORDER BY C.column_id
        				FOR
        				 XML PATH('')
        				) Col (ColNames)
    WHERE   T.Name = 'TABLE_NAME'
    

    The last is explained here more: remote query considerations with projection operators

    commented on Oct 1 2012 11:55AM
    jdgraaf
    3077 · 0% · 2

Your Comment


Sign Up or Login to post a comment.

"Generate Column lists for a table" rated 5 out of 5 by 12 readers
Generate Column lists for a table , 5.0 out of 5 based on 12 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]