June 1, 2009 at 3:17 am
Hi All
I have got nearly 1000 SP's on one of our database, i need to figure out a way to find what tables are been used on all SP's. if i get all the tables name that are corresponding SP that will be ideal.
Something like
SP1 -- Table1, table2,table5
SP2 -- Table 1
SP3 -- Table 3
Can you please let me know if this is possible, if yes how can i achieve this result rather than going into each SP manually
Thanks in advance for all your help 🙂
June 1, 2009 at 3:51 am
Check out:
http://blog.sqlauthority.com/2007/08/10/sql-server-2005-find-stored-procedure-create-date-and-modified-date/[/url]
http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/[/url]
http://www.sql-server-performance.com/articles/dba/sp_dependencies_p1.aspx
Tanx 😀
June 1, 2009 at 4:48 am
Brilliant Eswin, Thanks a lot mate, i wrote a script on a while loop to find out the tables. 🙂
June 1, 2009 at 4:53 am
Can u please share the script
Tanx 😀
June 1, 2009 at 4:59 am
Hi Eswin
I had inserted all the table names into a table with an Identity insert ID, i got 371 table names so i used the condition ID<=371, Then i created a temp table to insert the SP Name and then table name, once the script has been completed then you will get all the information you need on the temp table,
Create Table TablesUsednSP (ProcedureName varchar(max), TableName Varchar(max))
Declare @ID int
Declare @TableName varchar(max)
Set @ID=1
while @ID<=371
Begin
Select @TableName =TableNamesNotUsed from Tables where id=@ID
Select @TableName
Declare @sql varchar(max)
SEt @sql ='SELECT DISTINCT so.name into TempNumbers
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ''%'+ @TableName +'%'''
EXEC(@SQL)
Insert into TablesUsednSP
Select *,@TableName from TempNumbers
Set @ID=@ID+1
Select @ID
Drop Table TempNumbers
End
June 1, 2009 at 6:26 am
Hi
Just Run the script....
DECLARE @PROCEDUREVARCHAR(40)
DECLARE @TABLESVARCHAR(MAX)
DECLARE @IterateINT
SELECT @Iterate = 1
SELECT
DENSE_RANK() OVER (ORDER BY S.ID) 'GROUPID',
OBJECT_NAME(S.ID) 'SP',
OBJECT_NAME(S.DEPID)'TableS' INTO #GROUPS
FROMSYS.SYSDEPENDS S(NOLOCK) JOIN SYS.SYSOBJECTS SS
ON(S.DEPID = SS.ID)
WHERESS.[TYPE]='U'
GROUP BY S.ID,OBJECT_NAME(S.DEPID),OBJECT_NAME(S.ID)
WHILE @Iterate <= (SELECT MAX(GROUPID) FROM #GROUPS)
BEGIN
SELECT@PROCEDURE= SP,
@TABLES= COALESCE(@TABLES,'','') + TableS + ',' FROM #GROUPS WHERE GROUPID = @Iterate
PRINT @PROCEDURE + ' -- ' + LEFT(@TABLES,LEN(@TABLES)-1)
SELECT @PROCEDURE ='',@TABLES=''
SELECT @Iterate = @Iterate + 1
END
DROP TABLE #GROUPS
Results
Procedure1 -- Table1,Table2
Procedure2 -- Table1
Procedure3 -- Table2,Table4
...
...
Regards
June 1, 2009 at 7:10 am
Thanks a Lot mate 🙂 , this is cool
June 1, 2009 at 8:43 am
sysdepends may not be 100% accurate. You might create a stored procedure referencing a table that doesn't exist yet. In that case sysdepends does not get updated.
June 1, 2009 at 11:16 pm
Hi Santhappan
When i exec the script you gave im also getting the constraints and triggers in the database.........
Tanx 😀
June 2, 2009 at 3:14 am
Hi, this will return only SPs.
DECLARE @PROCEDURE VARCHAR(40)
DECLARE @TABLES VARCHAR(MAX)
DECLARE @Iterate INT
SELECT @Iterate = 1
DROP TABLE #GROUPS
SELECT
DENSE_RANK() OVER (ORDER BY S.ID) 'GROUPID',
OBJECT_NAME(S.ID) 'SP',
OBJECT_NAME(S.DEPID) 'TableS'INTO #GROUPS
FROM SYS.SYSDEPENDS S(NOLOCK) JOIN SYS.SYSOBJECTS SS ON (S.DEPID = SS.ID)
JOIN SYS.OBJECTS O ON (S.ID=O.OBJECT_ID)
WHERE SS.[TYPE] ='U' and O.TYPE = 'P'
GROUP BY S.ID,OBJECT_NAME(S.DEPID),OBJECT_NAME(S.ID)
select * from #GROUPS
WHILE @Iterate <= (SELECT MAX(GROUPID) FROM #GROUPS)
BEGIN
SELECT @PROCEDURE = SP,
@TABLES = COALESCE(@TABLES,'','') + TableS + ',' FROM #GROUPS WHERE GROUPID = @Iterate
PRINT @PROCEDURE + ' -- ' + LEFT(@TABLES,LEN(@TABLES)-1)
SELECT @PROCEDURE ='',@TABLES=''
SELECT @Iterate = @Iterate + 1
END
DROP TABLE #GROUPS
"Don't limit your challenges, challenge your limits"
June 2, 2009 at 11:16 pm
Thanks a lot........
Hai will it also give the stored procedures that doesn't depend on any table......
Tanx 😀
June 2, 2009 at 11:26 pm
Hi, here is the stored procedure...
/* **********************************************************
To check dependency as per object, use following values as
input of the procedure.
For,
Stored Procedure : 'P'
Views :'V'
Primary Key :'PK'
Function :'FN'
********************************************************** */
CREATE PROCEDURE uspShowDependency(@objType as char(5)) AS
BEGIN
DECLARE @PROCEDURE VARCHAR(40)
DECLARE @TABLES VARCHAR(MAX)
DECLARE @Iterate INT
SELECT @Iterate = 1
SELECT
DENSE_RANK() OVER (ORDER BY S.ID) 'GROUPID',
OBJECT_NAME(S.ID) 'SP',
OBJECT_NAME(S.DEPID) 'TableS'INTO #GROUPS
FROM SYS.SYSDEPENDS S(NOLOCK) JOIN SYS.SYSOBJECTS SS ON (S.DEPID = SS.ID)
JOIN SYS.OBJECTS O ON (S.ID=O.OBJECT_ID)
WHERE SS.[TYPE] ='U' and O.TYPE = @objType
GROUP BY S.ID,OBJECT_NAME(S.DEPID),OBJECT_NAME(S.ID)
--select * from #GROUPS
CREATE TABLE #TEMP(ObjName VARCHAR(100), Dependency NVARCHAR(MAX))
WHILE @Iterate <= (SELECT MAX(GROUPID) FROM #GROUPS)
BEGIN
SELECT @PROCEDURE = SP,
@TABLES = COALESCE(@TABLES,'','') + TableS + ',' FROM #GROUPS WHERE GROUPID = @Iterate
INSERT INTO #TEMP SELECT @PROCEDURE, @TABLES
SELECT @PROCEDURE='', @TABLES=''
SELECT @Iterate = @Iterate + 1
END
SELECT * FROM #TEMP
DROP TABLE #GROUPS
DROP TABLE #TEMP
END
--EXEC uspShowDependency 'P'
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 12:15 am
No i have stored procedures in my database that are not depending on any tables......
How to get that also along with the once that depend on tables......
I heard that "SYS.SYSDEPENDS" doesn't give that.
Tanx 😀
June 3, 2009 at 1:10 am
Sorry, i misunderstood your pt earlier...
I tried to make changes according to your pt. please check this.
/* **********************************************************
To check dependency as per object, use following values as
input of the procedure.
For,
Stored Procedure : 'P'
Views :'V'
Primary Key :'PK'
Function :'FN'
********************************************************** */
CREATE PROCEDURE uspShowDependency(@objType as char(5)) AS
BEGIN
DECLARE @PROCEDURE VARCHAR(40)
DECLARE @TABLES VARCHAR(MAX)
DECLARE @Iterate INT
SELECT @Iterate = 1
SELECT DENSE_RANK() OVER (ORDER BY O.OBJECT_ID) 'GROUPID',
OBJECT_NAME(O.OBJECT_ID) 'SP',
OBJECT_NAME(SD.DEPID) 'TABLES' INTO #GROUPS
FROM SYS.OBJECTS O LEFT JOIN SYS.SYSDEPENDS SD ON (O.OBJECT_ID=SD.ID)
WHERE O.TYPE='P' OR SD.DEPID IN (SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE='U')
GROUP BY O.OBJECT_ID,OBJECT_NAME(O.OBJECT_ID),OBJECT_NAME(SD.DEPID)
--select * from #GROUPS
CREATE TABLE #TEMP(ObjName VARCHAR(100), Dependency NVARCHAR(MAX))
WHILE @Iterate <= (SELECT MAX(GROUPID) FROM #GROUPS)
BEGIN
SELECT @PROCEDURE = SP,
@TABLES = COALESCE(@TABLES,'','') + TableS + ',' FROM #GROUPS WHERE GROUPID = @Iterate
INSERT INTO #TEMP SELECT @PROCEDURE, @TABLES
SELECT @PROCEDURE='', @TABLES=''
SELECT @Iterate = @Iterate + 1
END
SELECT * FROM #TEMP
DROP TABLE #GROUPS
DROP TABLE #TEMP
END
--EXEC uspShowDependency 'P'
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 1:58 am
Thanks a lot.......
But this script is again showing me all the procedures and also trigger and chk constraints when i run
EXEC uspShowDependency 'P'
Tanx 😀
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply