preventing select * from the query in SQL
For Team Leads, Developer don’t listen what you say 
still using * in their select queries. You can restrict them using * by using deny select on object.
This all you can do by create a dummy column in table and deny it for selection. Now user can select the rest of the columns but cannot use SELECT *.
Following is the example
CREATE TABLE tblDeny(
fieldprimary INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,fieldtest INT NULL
,fielddummy CHAR(1) NULL
);
GO
INSERT INTO tblDeny (fieldtest)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5;
GO
DENY SELECT ON OBJECT:: tblDeny(fielddummy) TO USERLOGINID;
GO
now try this query
select * from tblDeny
or try
select count(*) from tblDeny
–Result
Msg 230, Level 14, State 1, Line 1
The SELECT permission was denied on the column ‘fielddummy’ of the object ‘tblDeny”, database ‘YOUR DATABASE’, schema ‘dbo’.
nice & knowledgeable article…keep writing……
So we see the Pro only:
The Con is :
The only problem will be in case you need to do some support or analysis, you need to type heavy (big) SQL each time.
And with tools like SQL Developer its very convinient to do a select * and check the required field or take the data dump in an xls and analyse.
Hi deepak
I don’t understand what you are saying for taking a dump data in xls file .. Here on the post I am just saying that we can prevent the using of * from the select query as it optimize our queries ?