Generate SELECT Statement

ByHariharan Rajendran

Generate SELECT Statement

This article explains that how to generate SELECT statement with all the table columns.

In the Production environment, most of the tables will have more than 100 columns. If those tables are used in a report with all the columns then it will be difficult to type all the columns.

To ease the process, we can generate the SELECT statement and can use that for any purpose.

Use the below code and pass the table which you want to use in the SELECT statement.

In this example, I used FactInternetSales table.

—–Code——-

Declare @Column varchar(500), @tableName varchar(25)

SET @tableName =’FactInternetSales’

SET @Column = ”

select @Column = @Column + Column_Name + ‘, ‘ from [AdventureWorksDW2014].INFORMATION_SCHEMA.COLUMNS

where table_name=@tableName

select ‘SELECT ‘ + SUBSTRING(@Column, 0, LEN(@Column)) + ‘ From ‘+@tableName as SELECTScript

—–Code———-

SQL1

Copy and paste the result and execute the script.

SQL2

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

2 Comments so far

health servicesPosted on  11:52 am - Mar 20, 2017

I’m not that much of a internet reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your website to come back down the road. Many thanks

online educationPosted on  3:13 am - Mar 21, 2017

Excellent, what a blog it is! This weblog gives helpful data to us, keep it up.

Leave a Reply