February 1, 2012 at 10:20 pm
declare @objid as int
select @objid=object_id from sys.objects where name='Procedurename'
if exists(select id from sysdepends where depid=@objid)
begin
select name as ParentProcedure from sys.objects where object_id in
(select id from sysdepends where depid=@objid)
end
else
begin
print 'No Parent Procedure Found'
end
will this script be useful for finding the parent proc?
February 2, 2012 at 4:39 am
any comments on this?
February 2, 2012 at 10:29 pm
declare @objid as int
select @objid=object_id from sys.objects where name='substitute your Procedurename'
if exists(select id from sysdepends where depid=@objid)
begin
select name as ParentProcedure from sys.objects where object_id in
(select id from sysdepends where depid=@objid)
end
else
begin
print 'No Parent Procedure Found'
end
anything wrong with this script?
February 3, 2012 at 1:18 pm
The reliability of sysdepends and other types of similar functions (sp_depends) is somewhat suspect due to several factors. I wouldn't necessarily rely on it for determining a parent/calling function. The short answer is, this is not always up-to date or accurate information: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/keeping-sysdepends-up-to-date-in-sql-server-2008.aspx : somewhat easy to follow writeup on how/why this happens.
February 3, 2012 at 2:41 pm
there's a quick fix for that, though; you can simply ALTER all your procedures. which will fix the sysdepends; if everything was rebuilt correctly, your dependencies are up to date.
note that will not enter dependencies on procedures which use dynamic SQL, and of course it will not create references for objects outside of the database, via cross database calls or anything.
here's something i put together to rebuild/find invalid objects, like when a view or proc uses a table,a nd the table changes, gets dropped, or gets a column altered or whatever.
My friend Jason pointed out that it assumes "CREATE PROCEDURE" , with a single space between CREATE and PROCEDURE exists...so if your prdoecure uses a different string format, like proecedure on the next line or something , you might have to fiddle with it based on your office practices.
iiSET NOCOUNT ON
DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(max)
--#################################################################################################
--Views
--#################################################################################################
DECLARE acursor CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(v.name)
FROM sys.views v
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
OPEN acursor
FETCH NEXT FROM acursor INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRANSACTION
BEGIN TRY
exec sp_refreshview @objname
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM acursor INTO @objname
END
CLOSE acursor
DEALLOCATE acursor
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH
FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
SELECT * FROM @BadObjects
Lowell
February 4, 2012 at 7:23 am
Thank u mates.!
February 9, 2012 at 5:25 am
Lowell (2/3/2012)
My friend Jason pointed out that it assumes "CREATE PROCEDURE" , with a single space between CREATE and PROCEDURE exists...so if your prdoecure uses a different string format, like proecedure on the next line or something , you might have to fiddle with it based on your office practices.
sp_refreshsqlmodule can come handy here. We can fetch the name from sys.all_objects & pass it like:
Execute sp_refreshsqlmodule @name=YourObjectName
ObjectName can be anything stored procedure,views or functions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply