Hi team,

Is there any shortcut way to code review stored procedures in sql.

Note: Condn. need to check are below

• Checking whether all temp tables dropped, if its not dropped then to identify that table

• checking update and delete statement on physical table which should include PRIMARY KEY column in where condition

• checking Every JOIN stmt shouldn't combine more than five physical tables

• Checking any temp table created like select * into #table from some physical table

• Checking any physical table creation during run time of Sp

Thanks in a

No, there is no shortcut for code review.

BTB, "Checking all physical table contains with NOLOCK" - This is a very bad thing, so please remove this item from your code review points. NOLOCK will lead you to dirty read and possibly wrong data.

Hi Muthukumar,

There's no shortcut for almost all the points in your code review list. For the second point, you can find the tables that the stored procedure(SP) depends and check the constraints of the tables. See below.
View the Dependencies of a Stored Procedure
Determine a table's primary key using TSQL

For other points, there may no shortcut, but the built-in SP sp_helptext help to output the SP text into grid result, which at least for me is easiler to read.

If you have any question, feel free to let me know.

Hi all,
Following function gives the create tmp table and drop temp table stmt in row wise.Hence we can count the create tmp tables and drop tables.   Count will identify the missing drop tables ,Like this am asking shortcuts for code reviewing.

===========================================

Create FUNCTION [dbo].[uftReadfileAsTable]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
@File TABLE
(
[LineNo] int identity(1,1), 
line varchar(8000)) 

AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
   @Command varchar(1000),
   @hr int,
@String VARCHAR(8000),
@YesOrNo INT

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
, @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
BEGIN
if @HR=0 Select @objErrorObject=@objTextStream, 
@strErrorMessage='finding out if there is more to read in "'+@filename+'"'
if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

IF @YesOrNo<>0  break
if @HR=0 Select @objErrorObject=@objTextStream, 
@strErrorMessage='reading from the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Readline', @String OUTPUT
INSERT INTO @file(line) SELECT @String
END

if @HR=0 Select @objErrorObject=@objTextStream, 
@strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
begin
Declare 
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int

EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
insert into @File(line) select @strErrorMessage
end
EXECUTE  sp_OADestroy @objTextStream
-- Fill the table variable with the rows for your result set

RETURN 
END

==================================================

steps to check:
1. Execute the function n ur DB
2. Store your sql file in some location.
3. Execute following query in ur DB and path should be replaced with where u have stored ur SQl file

--QUERY

Select line from
 Dbo.uftReadfileAsTable('D:\SQL_CODE_REVIEW','filename.sql')
where line  like '%create%table%#%'



Select line from
 Dbo.uftReadfileAsTable('D:\SQL_CODE_REVIEW','filename.sql')
where line  like '%drop%table%#%'