# SQL Server Rule: SML005
| | |
|----|----|
| Assembly | TSQLSmellSCA |
| Namespace | TSQLSmellSCA |
| Class | TSQLSmellSCA5 |
## Rule Information
| | |
|----|----|
| Id | SML005 |
| Friendly Name | Avoid use of 'Select *' |
| Category | CodeSmells |
| Ignorable | false |
| Applicable Types | Model |
## Description
Avoid use of 'Select *'
### Examples
```sql
CREATE PROCEDURE dbo.InsertSelectStar
AS
set nocount on;
INSERT INTO dbo.TestTableSSDT([IdCol],Col1,Col2,Col3,DateCol)
SELECT * from dbo.TestTableSSDT;
-- SML005
```
```sql
CREATE PROCEDURE dbo.MultiCteTest
@pInsertCount INT = 0 OUTPUT
AS
BEGIN
set nocount on ;
WITH
successfulOrder AS (
SELECT * from dbo.TestTableSSDT
)
INSERT INTO dbo.TestTableSSDT
(
Col1
)
SELECT col1
from successfulOrder;
END;
-- SML005, SRD0016
```
```sql
Create PRocedure dbo.SelectAs
as
Set nocount on ;
Declare @X table
(
Id integer
)
Select * from @x
-- SML005, SML033
```
```sql
CREATE PROCEDURE dbo.SelectStarFromViewInProc
AS
Set nocount on;
SELECT * FROM dbo.ViewWithOrder;
go
-- SML005
```
```sql
CREATE PROCEDURE dbo.SelectStarOutOfCteTest1
AS
Set NoCount on;
WITH ctex
AS (
SELECT * FROM dbo.TestTable
)
SELECT * FROM ctex
go
-- SML005
```
```sql
CREATE PROCEDURE dbo.TestSelectStarBeginEndBlock
as
set nocount on;
begin
SELECT * FROM dbo.TestTableSSDT;
end;
-- SML005,SRD0067
```
```sql
CREATE PROCEDURE dbo.SelectStarInCteTest
AS
SET nocount on ;
WITH ctex
AS (
SELECT * FROM dbo.TestTableSSDT
)
SELECT idcol,Col1 FROM ctex
go
-- SML005
```
```sql
CREATE Function dbo.udfTestSelectStarMultiStatementTVF()
RETURNS @RetTable TABLE(
id INTEGER
)
as
BEGIN
SET NOCOUNT ON;
DECLARE @s INTEGER;
WITH cteTest
AS
(
SELECT * FROM dbo.TestTableSSDT
)
SELECT @s = cteTest.Col1
FROM cteTest
return
end
-- SML005, SML033
```
```sql
CREATE Function dbo.udfTestSelectStar()
RETURNS integer
as
BEGIN
DECLARE @s INTEGER;
WITH cteTest
AS
(
SELECT * FROM dbo.TestTableSSDT
)
SELECT @s = IdCol
FROM cteTest
RETURN @s
end
-- SML005, SML033
```
```sql
CREATE PROCEDURE dbo.TestSelectStarInWhileLoop
AS
SET NOCOUNT on
WHILE(0=0) begin
SELECT * FROM dbo.TestTableSSDT
end
-- SML005
```
```sql
CREATE PROCEDURE dbo.TestWithExists
AS
Set nocount on
IF EXISTS(SELECT * FROM dbo.TestTableSSDT) BEGIN
SELECT Idcol from dbo.TestTableSSDT
end
-- SML005, SRD0063
```
```sql
CREATE PROCEDURE dbo.TestWithExistsAndNestedSelectStar
AS
Set nocount on ;
IF EXISTS(SELECT * FROM dbo.TestTableSSDT) BEGIN
SELECT * FROM dbo.TestTableSSDT
end
GO
-- SML005
```
```sql
CREATE PROCEDURE dbo.TestWithExistsAndNestedSelectStarInlineIF
AS
Set nocount on
IF EXISTS(SELECT * FROM dbo.TestTableSSDT) SELECT * FROM dbo.TestTableSSDT
GO
-- SML005
```
```sql
CREATE PROCEDURE dbo.UnionTest
AS
SET NoCount on
SELECT * FROM dbo.TestTableSSDT
Union ALL
SELECT * FROM dbo.TestTableSSDT
UNION ALL
SELECT * FROM dbo.TestTableSSDT
-- SML005
```
<sub><sup>Generated by a tool</sup></sub>
