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 firstname.lastname@example.org 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.