This procedure shows in a tree structure the dependencies of an object. It's very a basic, but effective script. Script is developed for 2K5
Make sure recursive triggers is enabled!
Also, in order to be sure the metadata in sys.sql_dependencies is actual, run sp_refreshview for views, or sp_refreshsqlmodule for each view/procedure and function
if object_id('usp_dependencytree') is not null
drop procedure usp_dependencytree
go
create procedure usp_dependencytree(@ObjectName sysname, @deplevel int)
/*
name: usp_dependencytree
author: wilfred van dijk (www.wilfredvandijk.nl)
purpose: shows dependencytree for specified object
date: 20080625 initial release
*/
as
begin
declare @dependson sysname
declare @soort varchar(32)
select @soort = type_desc from sys.objects where name = @ObjectName
if @deplevel = 0
print @ObjectName + ' (' + @soort + ')'
else
if @deplevel = 1
print '+-' + @ObjectName+ ' (' + @soort + ')'
else
print replicate('| ', @deplevel-1) + '+-' + @ObjectName+ ' (' + @soort + ')'
declare c_lus cursor local for
select distinct(object_name(REFERENCED_MAJOR_ID))
from sys.sql_dependencies
where object_id = object_id(@ObjectName)
open c_lus
fetch next from c_lus into @dependson
while @@fetch_status = 0
begin
set @deplevel = @deplevel + 1
exec usp_dependencytree @dependson, @deplevel
set @deplevel = @deplevel - 1
fetch next from c_lus into @dependson
end
close c_lus
deallocate c_lus
end
go
My interest in writing this article was started by an MSDN article titled SQL Server 2005: The CLR Enters the Relational Stage. The article shows how to write a function that returns the top three countries per category. That's always been something that was difficult to do in SQL so I was curious about the approach. The article started out well but I was very unhappy by the end. It's just soooo much easier to do this in SQL Server 2005 using the new CROSS APPLY clause in Transact-SQL. So I'm going to write a query to return the top 3 orders for each customer and I'm going to do it in about 10 lines of SQL. (UPDATE: An alert reader found an even better approach!)
2005-06-02
3,066 reads
SQL Server doesn't have a Product aggregate function. A recent blog post by Karen Watterson pointed out that Microsoft Knowledge Base article Q89656: Simulating a PRODUCT() Aggregate Function discusses how to achieve the Product aggregate functionality using the POWER function. We e-mailed about it and she challenged me to create a Product aggregate in SQL Server 2005. I decided to accept the challenge.
2005-03-22
1,489 reads
The next evoution of T-SQL, which will be released in SQL Server 2005, contains a number of enhancements designed to allow you to write more powerful queries while keeping the code structured in a way that makes development and understanding it easier. Coming ever so closer to the SQL-99 specification with Common Table Expressions, new author Srinivas Sampath brings us an introduction to this new way of writing complex queries.
2007-09-22 (first published: 2005-03-02)
50,559 reads
TRY-CATCH blocks in T-SQL allow error detection and reporting in a construct similar to that used in VB .NET, C#, and C++. The specific focus here is how TRY-CATCH blocks are used in stored procedures and where there might be some surprises.
2005-02-04
2,889 reads
SQL Server 2005 has added a new format for queries called a Common Table Expression or CTE. CTE’s are part of the SQL:1999 Specification and represent further compliance with this specification by SQL Server. This article will cover using one of the many capabilities of CTE’s, implementing recursive functions. A recursive function is a one that iteratively executes itself. Understanding how CTE’s implement recursive functions is the first step to understanding the full capabilities of CTE’s.
2005-01-19
1,858 reads