May 11, 2009 at 1:57 am
Hi,
I have the following requirement:
I need to find the occurrence of the columns in where clause or inner joins of all user defined tables in a database which are used in all stored procedures.
Something like:
Tab1 col1 sp1
Tab1 col1 sp2
Tab1 col3 sp1
Tab4 col2 sp3
col1 of Tab1 is used in 2 sps(sp1 and sp2)
col3 of Tab1 is used in 1 sp
col2 of Tab4 is used in 1 sp.
How do I proceed with this?
This is basically to propose indexes on columns in the table for the database based upon the occurrences\usage of the column.
May 11, 2009 at 10:22 am
Some time ago I downloaded the following stored procedure from the scripts section. You can rewrite it to find all the stored procedures that contain specific column names taken from sys.columns catalog view.
May 11, 2009 at 10:26 am
Sorry I forgot the stored proc in may last post...;-)
use master
go
create proc dbo.Usp_SearchInProc (@s varchar(400),
@flag char(1))
as
/***********************************************************
Written By : yousef ekhtiari
email :y_ekhtiari@yahoo.com
Date : 10 January 2006
Description : Returns the name of stored procedures
which contain whole or any part of tokens in a string
USAGE:
@flag='a' means any part of tokens
@flag='w' whole part of tokens
exec Usp_SearchInProc @s-2='#tmp_result "yousef ekhtiari" ',@flag='w'
exec Usp_SearchInProc @s-2='cirmon',@flag='a'
***********************************************************/
set nocount on
create table #Sarg (s varchar(100))
declare
@pos int,
@sSQL varchar(8000),
@dbname as sysname,
@where as varchar(8000)
if @flag not in ('w','a')
begin
raiserror('Invalid use of @flag',16,1)
return
end
set @s-2=ltrim(ltrim(@s))+' '
while len(@s)>0
begin
if left(@s,1)='"'
begin
set @pos=CHARINDEX('"',@s,2)
insert #Sarg values( ltrim(replace( left(@s,CHARINDEX('"',@s,2) ) ,'"','')))
end
else
begin
set @pos=CHARINDEX(' ',@s,2)
insert #Sarg values( ltrim(left(@s,CHARINDEX(' ',@s,2))))
end
set @s-2=ltrim(stuff(@s,1,@pos ,''))
end
declare db cursor
for SELECT [name]
FROM [master].[dbo].[sysdatabases]
where sid0x01
open db
fetch next from db into @dbname
while @@fetch_status=0
begin
print '----------------'+ @dbname+'-------------'
set @sSQL='SELECT distinct [name]
FROM '+@dbname+'.[dbo].[sysobjects] o
inner join '+@dbname+'.[dbo].[syscomments] c on o.id=c.id
where xtype=''p''
and name not like ''dt_%'''
if @flag='a'
set @sSQL=@sSQL+' and exists(
select * from #Sarg
where
ltrim(rtrim(text)) like ''%''+ltrim(rtrim(s))+''%'')'
else if @flag='w'
begin
set @where=''
select @where=@where+' and patindex( ''%'+replace(ltrim(rtrim(s)),'''','''''' )+'%'', text)>0'
from #Sarg
set @sSQL=@sSQL+@where
end
exec(@sSQL)
fetch next from db into @dbname
end
close db
deallocate db
drop table #Sarg
May 12, 2009 at 12:46 am
Thanks for the prompt response :-).
But I require occurrences of the columns in all tables in all sps for proposing indexes.
So require to consider the alias names used in the procedure for table names and the columns used in the where clause or joins.Some procedures may have alias name and some may not have.For some columns they would have used the column names alone directly without using the alias.
May 12, 2009 at 6:54 am
Even when you use aliases for table names, the table name must appear explicitly in the query, so you firstly have to find the table names and afterwards find column names that belong to those tables following the "join" or "where" clause... it is a very complicated problem due to text analysis you have to accomplish, but this way you can achieve at least an initial approach
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply