Generating an automatic script to select all columns in a table

Here's how to use the derived table technique to generate an automatic script for selecting all columns in a table:

                
declare @tablename varchar(30) 
set @tablename  = 'products' 

SELECT stmt 
FROM ( 
        SELECT -1 AS pos, 'SELECT' AS stmt 
        UNION ALL 
        SELECT ORDINAL_POSITION, 
                CHAR(9) + char(9) + COL_NAME(OBJECT_ID(@tablename), ORDINAL_POSITION) + 
                CASE WHEN ORDINAL_POSITION < 
                                 (select count(*) from 
                                   INFORMATION_SCHEMA.columns 
                                  where table_name =  @tablename) THEN ',' ELSE '' END 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @tablename AND ORDINAL_POSITION < 
                              (select count(*) + 1 from 
                               INFORMATION_SCHEMA.columns 
                                where table_name =  @tablename) 
        UNION ALL 
        SELECT (select count(*) + 2 from 
                INFORMATION_SCHEMA.columns 
                where table_name =  @tablename), 'FROM ' + @tablename 
) AS st 
ORDER BY pos 

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.