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,
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
2 Make use of Information_schema.columns view and generate column list
COLUMN_NAME+',' AS COLUMN_NAME
The above creates the following result
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)
@SQL='', @TABLE_NAME ='sales_details'
SET @SQL='SELECT '+LEFT(@SQL,LEN(@SQL)-1)+' FROM '+@TABLE_NAME
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.