February 2005 - Posts - Raymond Lewallen

The value of a programmer

This article, titled The Fallacy Of Cheap Programmers was quite interesting and pretty good. I think the writer notes a lot of points that I believe to be true. The article is missing, however, any studies that back him up. He states "A lot of research on programmer productivity...", but fails to state what that research is or where is came from, so you don't really know anything other than what the writer tells you unless you go out looking for it yourself. Also, I'm not sure I agree with the statement "...believe that one programmer can be as productive as 3, let alone 5 or even 20...". Can 1 programmer be as productive as 3? Perhaps, depending on the tasks. 5? That's a strech. 20? Um, I don't think so.

All in all, I do agree with the article. I think I'm a good programmer, and that all comes with experience and training. I like to think that some of the things I do today, such as programming in the beginning stages for performance and scalability, as any good programmer would do, helps relieve the focus of those aspects in future life-cycles of the application production. However, not all programmers do these things, and of course, I'm not a good enough programmer to do everything right the first time either. I believe I do good work and can be very productive when I want to. But how many programmers can I replace? Sometimes 3. Sometimes ½. Sometimes not at all. It depends on the tasks at hand and the abilities of the programmers I’m attempting to replace.

Posted Feb 28 2005, 03:30 PM by Raymond Lewallen with 5 comment(s)

Filed under: Generalities

Your bandwidth is killing me!

Yo, ge5-0-0-1000M.cr2.vel.net, you are killing me... absolutely killing me!

 12    19 ms    14 ms    17 ms  so-0-0-0.mpr1.iah1.us.above.net [64.125.31.61]
 13    49 ms    44 ms    47 ms  so-4-1-0.mpr2.lax9.us.above.net [64.125.29.101]
 14    50 ms    49 ms    47 ms  ge-vel-net.above.net [209.133.90.29]
 15   253 ms   281 ms   302 ms  ge5-0-0-1000M.cr2.vel.net [207.182.225.166]
 16    60 ms   195 ms    64 ms  haw-207-182-225-146.vel.net [207.182.225.146]
 17    85 ms    70 ms    73 ms  haw-66-102-133-21.vel.net [66.102.133.21]

Posted Feb 28 2005, 01:54 PM by Raymond Lewallen with no comments

Filed under: Generalities

Software Factories

Awhile back Somasegar talked about VSTS and Software Factories. Ah, the industrialization of software development. Let's talk about software factories for a second.

A software factory is a software product that can be plugged into or configure an extensible development tool, like Visual Studio Team System, as Somasegar mentions. So what is the software factory product? It can be any of a number of combination of different things, such as patterns, frameworks and domain specific languages. And what does this provide? Think about customer relationship management for a second. If you were to examine the majority of CRM applications, you would probably find some underlying similaries in the framework components and in the design patterns used in the software. Herein lies the problem. Why should everybody who designs a CRM application have to go through the process of building, from scratch, those same framework components under the same usage and design patters and guidance? If an accepted approach to the development of CRM applications has been proven and used successfully, why not make that non-customized portion of the application development life-cycle available to others, in an effort to increase productivity? This is what software factories will attempt to solve. The factory for a CRM application would contain something along the lines of the .Net framework, C# or VB.Net, the Microsoft Business Framework and Microsoft SQL Server, with maybe one or two other components that don't strike me at the moment. When given that software factory, you now have all the framework components of developing and endless number of CRM applications, each with its own specific customizations that tailor to the customers needs.

The main supporting player in software factories is the knowledgable and experienced developer. This knowledge and experience must be captured, recorded and made available for reuse by others in the software development industry.

Lets see how well the idea takes off.

Posted Feb 28 2005, 09:07 AM by Raymond Lewallen with 2 comment(s)

What to expect from me in the near future

Here is what I’m working on for posting in the next 10 days or so; stay tuned!

The final topic on OOP principles - Polymophism
Boxing and Unboxing explained with IL code included
Scope, Time, Resources and Quality and how they relate and effect eachother
Pivot and Unpivot in Sql Server 2005 and how it saves having to write a bunch of code
Reflection in a Nutshell

Currently listening to: Locomotive (Complicity) - Guns N' Roses

Posted Feb 25 2005, 12:41 PM by Raymond Lewallen with 2 comment(s)

Filed under: Generalities

Inheritance Defined

Here it is, ladies and gentlemen, part 3 of the 4 part series on the 4 major principles of object-oriented programming: data abstraction and encapsulation we have already talked about, polymorphism will be last and today we have inheritance.

Objects can relate to eachother with either a “has a”, “uses a” or an “is a” relationship. “Is a” is the inheritance way of object relationship. The example of this that has always stuck with me over the years is a library (I think I may have read it in something Grady Booch wrote). So, take a library, for example. A library lends more than just books, it also lends magazines, audiocassettes and microfilm. On some level, all of these items can be treated the same: All four types represent assets of the library that can be loaned out to people. However, even though the 4 types can be viewed as the same, they are not identical. A book has an ISBN and a magazine does not. And audiocassette has a play length and microfilm cannot be checked out overnight.

Each of these library’s assets should be represented by its own class definition. Without inheritance though, each class must independently implement the characteristics that are common to all loanable assets. All assets are either checked out or available for checkout. All assets have a title, a date of acquisition and a replacement cost. Rather than duplicate functionality, inheritance allows you to inherit functionality from another class, called a superclass or base class.

Let us look at loanable assets base class. This will be used as the base for assets classes such as book and audiocassette:

Public Class LibraryAsset

    Private _title As String
    Public Property Title() As String
        Get
            Return _title
        End Get
        Set(ByVal Value As String)
            _title = Value
        End Set
    End Property

    Private _checkedOut As Boolean
    Public Property CheckedOut() As Boolean
        Get
            Return _checkedOut
        End Get
        Set(ByVal Value As Boolean)
            _checkedOut = Value
        End Set
    End Property

    Private _dateOfAcquisition As DateTime
    Public Property DateOfAcquisition() As DateTime
        Get
            Return _dateOfAcquisition
        End Get
        Set(ByVal Value As DateTime)
            _dateOfAcquisition = Value
        End Set
    End Property

    Private _replacementCost As Double
    Public Property ReplacementCost() As Double
        Get
            Return _replacementCost
        End Get
        Set(ByVal Value As Double)
            _replacementCost = Value
        End Set
    End Property

End Class

This LibraryAsset is a superclass, or base class, that maintains only the data and methods that are common to all loanable assets. Book, magazine, audiocassette and microfilm will all be subclasses or derived classes or the LibraryAsset class, and so they inherit these characteristics. The inheritance relationship is called the “is a” relationship. A book “is a” LibraryAsset, as are the other 3 assets.

Let’s look at book and audiocassette classes that inherit from out LibraryAsset class:

 Public Class Book
    Inherits LibraryAsset

    Private _author As String
    Public Property Author() As String
        Get
            Return _author
        End Get
        Set(ByVal Value As String)
            _author = Value
        End Set
    End Property

    Private _isbn As String
    Public Property Isbn() As String
        Get
            Return _isbn
        End Get
        Set(ByVal Value As String)
            _isbn = Value
        End Set
    End Property

End Class

Public Class AudioCassette
    Inherits LibraryAsset

    Private _playLength As Int16
    Public Property PlayLength() As Int16
        Get
            Return _playLength
        End Get
        Set(ByVal Value As Int16)
            _playLength = Value
        End Set
    End Property

End Class

Now, lets create an instance of the book class so we can record a new book into the library inventory:

 Dim myBook As Book = New Book
 myBook.Author = "Sahil Malik"
 myBook.CheckedOut = False
 myBook.DateOfAcquisition = #2/15/2005#
 myBook.Isbn = "0-316-63945-8"
 myBook.ReplacementCost = 59.99
 myBook.Title = "The Best Ado.Net Book You'll Ever Buy"

You see, when we create a new book, we have all the properties of the LibraryAsset class available to us as well, because we inherited the class. Methods can be inherited as well. Let’s add a few methods to our LibraryAsset class:

 Public Class LibraryAsset

' Pretend the properties listed above are right here

    Public Sub CheckOut()
        If Not _checkedOut Then _checkedOut = True
    End Sub

    Public Sub CheckIn()
        If _checkedOut Then _checkedOut = False
    End Sub

End Class

Now, our “myBook” we created above automatically inherited these methods, and we didn’t even have to touch the Book class in order for it to happen. The book and audiocassette classes above automatically inherited the abilities to be checked out and checked in. In our “myBook” above, now we can check the book out by calling “myBook.CheckOut()”. Simple! One of the most powerful features of inheritance is the ability to extend components without any knowledge of the way in which a class was implemented.

Declaration options, such as Public and Private, dictate which members of a superclass can be inherited. For more information on this, see the Declaration Option section of Eric's post.

Currently listening to: Rollin' (The Ballad Of Big & Rich) - Big & Rich

Posted Feb 24 2005, 07:29 AM by Raymond Lewallen with 5 comment(s)

Filed under: Patterns and Practices

Viewstate chunking example in 2.0

Yesterday I talked briefly about Viewstate chunking. I am not aware of any publicly available resources or samples on view-state chunking. However, here’s an example of what chunking may look like (with a heavy emphasis on may):

Resultant page

In this example, the page state is split into a number of fields. Since the size of the combined state is 3000, the page state is split into 3 hidden fields, along with a hidden field indicating the count of fields. Notice that each view-state chunk (after the first one) is appended with a count index.

Currently listening to: Shimmy - System of a Down

Posted Feb 23 2005, 02:27 PM by Raymond Lewallen with 3 comment(s)

Filed under: .Net Development

Framework Glue: Metadata

I occasionally hear or read people asking about metadata and what is it in the .Net framework.

1. Metadata describes information found in all managed objects. Properties and methods found in a type are described by the metadata table associated with that type. Without metadata, the .Net framework wouldn’t be very useful. Because the framework reads the metadata for a type, this allows VB written classes to be used by C# code, because it was complied to a common assembly language and has a common metadata table structure that the framework can read and understand and therefore utilize in another language.

2. Probably Sahil’s favorite: serialization. Without metadata, serialization and deserialization of data wouldn’t be possible, I don’t think. Mind you, I’m not incredibly knowledgable about serialization, I just understand what it does and know how to serialize things and get them back. Metadata must be serialized into the stream so that you have some sort of map for what your object really is when you go to pull it back out of the stream. Metadata provides a map for the entire instance. If you have a System.DateTime object you want to serialize, you aren’t just serializing the DateTime object, you have to serialize System.Object as well, because its part of the instance. Metadata takes care of all that descriptive stuff so we don’t have to worry about any of that. You’ll have to get somebody else to explain to you the inner workings of exactly how metadata is used in serialization. Common sense tells you though, that just the nature of serialization and what takes place relies heavily on metadata.

3. Intellisense would not exist without metadata. The magic forces of intellisense read the metadata of an assemblies types and use that to provide us, in the IDE, with wonderful bits of information about the types, its methods and properties, available to us. Intellisense is really a wonderful thing, especially in 2005 IDE.

4. My favorite part of the framework: Garbage collection. The GC uses metadata to figure out what objects another object can refer to, and if that object is currently able to be used. The CLR uses metadata to figure out where objects exist in memory and just how much space they take up. Without metadata, the GC wouldn’t know the start and end of an objects address space.

5. How is metadata most commonly used directly by us developers? Reflection. Reflection is the dynamic examination of an object’s metadata. I use reflection to build very large datatables from the structure of an object and its properties without having to hardcode anything. This saves me thousands of lines of code, and is all made possible because of metadata. Reflection deserves its own topic, and you can probably google it and find quite a bit more information that I’ll provide anytime soon.

So, there you have it. That’s my opinion of what metadata is and what it does for me. If I left something out or am completely wrong about something, I’m sure somebody out there will let me know.

Posted Feb 23 2005, 07:14 AM by Raymond Lewallen with 2 comment(s)

Filed under: .Net Development

Developer versus Programmer

Once had a new hire come to the project I work on. Took him out to lunch his first day (he only lasted 5 days, admitted the project and code was out of his league) and I started talking about programming and programmers, because my wife won't let me and I have to have these conversations with somebody. At the first mention of the word programmer, he quickly corrected me with "I'm not a programmer. I'm a developer." Uh, what?

Let us examine to two from the dictionary:

pro gram mer or pro gram er
n.
    1. One who programs

de vel op er
n.
   1. One that develops

Ok, so that wasn't much help. You see job listings for web developer or C# programmer; do you ever see web programmer or C# developer? Are they interchangable? Of course they are. Even Microsoft refers to those who use C#, or any other language to write software, as developers. Is programmer a dying term? Are we all transistioning to some weird marketing scheme that I'm unaware of that will change my job title from Programmer to Developer? If you go out and look, most job titles are still focused around the keyword "programmer".

Is there a proper use for one over the other, and in what scenarios? I just find this completely meaningless and trivial concept of programmer versus developer interesting. I'm odd that way. Any thoughts? How do you like to be referred to, a programmer or a developer? What's on your business card? Your resume? FYI, my job title has Programmer in it.

Posted Feb 22 2005, 03:45 PM by Raymond Lewallen with 20 comment(s)

Filed under: Generalities

Viewstate chunking

I was away from my computer all weekend, so I'm playing a bit of catchup today on my blog posts. There is no direct upper limit on the size of a viewstate. Limitations come into play when the browser limits the size of hidden fields. A capability to look for in Whidbey is chunking view-state. This is intended for situations where the view-state field becomes very large and certain firewalls or proxies may prevent a page if a hidden field is considered to be too big. In this case, a developer will be able to configure the view-state so it can be broken into multiple hidden fields. This is accomplished by the developer providing a cut-off size for a single view-state field. The cut-off size will have no upper or lower limit, although setting the cut-off to be very low will hurt performance. This can be valuable when dealing with large datasets bound to datagrids and other instances where you get a very large viewstate. Be aware though, this chunking capability doesn't replace the fact that viewstates should be kept as small as possible, and even turned off where applicable.

Currently listening to: Heartcatchthump - Chainsaw Kittens

Posted Feb 22 2005, 06:39 AM by Raymond Lewallen with 6 comment(s)

Filed under: .Net Development

Any book suggestions?

I'm still waiting on copies of Code Complete 2 and the Extreme Programming Pocket Guide to arrive at my doorstep. As you can see on the right, I'm re-reading some parts of my MSIL book. I personally don't like to read books that are language specific. I love books on patterns and practices and .Net framework. Any suggestions on a great book out there I can pick up that you've read and would give a "must read" review? I wonder what the people who write books read in their spare time, if they have any spare time. If you're smart and dedicated enough to write a book, what are you reading? Anything?

Currently listening to: Astounded - Tantric

Posted Feb 22 2005, 06:04 AM by Raymond Lewallen with 6 comment(s)

Filed under: Generalities

Where are all the custom FxCop rules?

So far, I've only been able to find three places that have custom FxCop rules that are based on the new introspection engine: here, here (both of those by John Robbins), and one by Tim Weaver here. If you go to the FxCop message boards, there are plenty of people talking about their custom rules, but nobody is posting them. GotDotNet even has a User Samples area for these things to go, but you won't find any custom rules for FxCop. So why aren't people sharing these? I've been patiently waiting for many many months on new rules for the introspection engine to come up on the internet, but they aren't to be found. Come on people, whats the deal? I would certainly like to import some rules somebody else has already written and tested. No, I'm not lazy, I'm just dilatory :)

Ok, so that said, no I don't have any custom FxCop rules to share. Mine use the old FxCop reflection engine and you don't want those. Am I going to have to write some new rules so that I can barter for somebody elses?

Update: Thanks to Darrell, who lead me to David, the latest versions of John Robbins' rules can be found here.

David has and updated set of rules here.

Currently listening to: Memory - Sugarcult

Posted Feb 18 2005, 04:58 AM by Raymond Lewallen with 8 comment(s)

Filed under: .Net Development, Patterns and Practices

How to tell if you're a good programmer

I play the guitar. I've been playing for awhile and I don't suck and am a pretty good rhythm player, but I can't make money doing it either. I am also right handed, so I play the guitar right handed. Now if you play the guitar and don't think you are any good at it, just try turning the guitar over and playing it with your other hand, or left hand in my case. You're much better than you thought you were, aren't you?

What would an analogy be for this in the software development world, or is there one?

Posted Feb 17 2005, 01:31 PM by Raymond Lewallen with 10 comment(s)

Filed under: Generalities

Data Abstraction Defined

This is the second of a 4 part series, for those of you new to object-oriented programming, discussing the 4 major principles of an object-oriented language: Data Abstraction, Encapsulation we've already discussed, Polymorphism and Inheritence. Data abstraction is the simplest of principles to understand. Data abstraction and encapuslation are closely tied together, because a simple definition of data abstraction is the development of classes, objects, types in terms of their interfaces and functionality, instead of their implementation details. Abstraction denotes a model, a view, or some other focused representation for an actual item. Its the development of a software object to represent an object we can find in the real world. Encapsulation hides the details of that implementation.

Abstraction is used to manage complexity. Software developers use abstraction to decompose complex systems into smaller components. As development progresss, programmers know the functionality they can expect from as yet undeveloped subsystems. Thus, programmers are not burdened by considering the waysin which the implementation of later subsystesm will affect the design of earlier development.

The best definition of abstraction I've ever read is: "An abstraction denotes the essential characteristics of an object that distinguish it from all other kinds of object and thus provide crisply defined conceptual boundaries, relative to the perspective of the viewer." -- G. Booch, Object-Oriented Design With Applications, Benjamin/Cummings, Menlo Park, California, 1991.

Lets look at this code for a person object. What are some things that a person can do? Those things must be represented here in our software model of a person. Things such as how tall the person is, and the age of the person; we need to be able to see those. We need the ability for the person to do things, such as run. We need to be able to ask the person if they can read.

Public Class Person

    Private _height As Int16
    Public Property Height() As Int16
        Get
            Return _height
        End Get
        Set(ByVal Value As Int16)
            _height = Value
        End Set
    End Property

    Private _weight As Int16
    Public Property Weight() As Int16
        Get
            Return _weight
        End Get
        Set(ByVal Value As Int16)
            _weight = Value
        End Set
    End Property

    Private _age As Int16
    Public Property Age() As Int16
        Get
            Return _age
        End Get
        Set(ByVal Value As Int16)
            _age = Value
        End Set
    End Property

    Public Sub Sit()
        ' Code that makes the person sit
    End Sub

    Public Sub Run()
        ' Code that makes the person run
    End Sub

    Public Sub Cry()
        ' Code that make the person cry
    End Sub

    Public Function CanRead() As Boolean
        ' Code that determines if the person can read 

        ' and returns a true or false
    End Function

End Class

So, there we have started to create a software model of a person object; we have created an abstract type of what a person object is to us outside of the software world. The abstract person is defined by the operations that can be performed on it, and the information we can get from it and give to it. What does the abstracted person object look like to the software world that doesn't have access to its inner workings? It looks like this:

You can't really see what the code is that makes the person run. This is encapsulation that we discuseed last week.

So, in short, data abstraction is nothing more than the implementation of an object that contains the same essential properties and actions we can find in the original object we are representing.

Currently listening to: All Along the Watchtower - Dave Matthews Band with Phish

Posted Feb 17 2005, 05:44 AM by Raymond Lewallen with 6 comment(s)

Filed under: Patterns and Practices

State Class Management Presentation on March 7th

If anyone is in the Oklahoma City area on Monday, March 7th around noon or 6 pm CST, come to our .Net User's Group Meeting and listen to me present the topic State Class Management in Asp.Net. This is a topic that Brendan and I have been discussing a little bit in my blog post here and Brendan's post here. I'll be discussing the base page class inheritance (see Eric's post) along with the state class management.

There will also, to my knowledge, be 2 other presenters during this 1½ hour lunchtime/2 hour evening presentation time. Don't miss it if you don't have to! For more information on the Oklahoma City .Net User's Group, visit the website at http://www.okcpro.net

Currently listening to: Devil Without a Cause - Kid Rock

Posted Feb 16 2005, 11:45 AM by Raymond Lewallen with 1 comment(s)

Filed under: Patterns and Practices

Sql dependency report from query analyzer

If you take a bunch of code from sp_help and kinda move it around and rearrange it, you can produce a pretty good little object dependency report. Just take this code, plop it right into query analyzer, select the database and hit F5. Viola, dependency report. Looks better if you hit CTRL-T first so you get it in text format and not columns. Some portions, such as line the 'query' column manipulation in the first select statement, should be a UDF, but then I'd have to provide code for both and this is just easier. Sorry about not coloring it in HTML. Also, this is not optimized for speed so don't expect it to run to quickly. You would have to replace the temp tables with normal tables, run the query to build the tables (against a small database), then hit CTRL-L to get the execution plan and look at where you will want to create your stats and build your indexes. Guage it against the master database, which running this against the master database takes 31 seconds on the machine I have Sql installed on. I tested it against a single proc P4 1.8 with 256 RAM.

set nocount on

select 'name' = (o1.name),
'id' = (o1.id),
    'type' = substring(v2.name, 5, 16),
   'query' =
  replace(replace(
  substring(c7.[text],charindex('@',left(c7.[text],charindex('AS'+Char(13)+Char(10),replace(Upper(c7.[text]),'AS '+Char(13)+Char(10),'AS'+Char(13)+Char(10)),1)),1),
  charindex('AS'+Char(13)+Char(10),replace(Upper(c7.[text]),'AS '+Char(13)+Char(10),'AS'+Char(13)+Char(10)),1)-charindex('@',left(c7.[text],charindex('AS'+Char(13)+Char(10),replace(Upper(c7.[text]),'AS '+Char(13)+Char(10),'AS'+Char(13)+Char(10)),1)),1))
  ,'))',')')
  ,'nt])','nt]')
  into [dbo].#Initial
  from [dbo].sysobjects o1
   inner join master.dbo.spt_values v2 on o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
   inner join [dbo].syscomments c7 on o1.id = c7.id and o1.xtype = 'p'
  where o1.xtype <> 's'
  and o1.xtype <> 'c'
  and left(o1.name,3) <> 'dt_'
  and left(o1.name,2) <> 'dd'
  order by 'type', 'name'

update [dbo].#Initial set query = '' where charindex('CREATE',query,1) > 0
update [dbo].#Initial set query = replace(query,Char(13)+Char(10),'')

while exists(select * from [dbo].#Initial where charindex(' ',query,1) > 0)
update [dbo].#Initial set query = replace(query,' ',' ')

CREATE TABLE [dbo].[#Report] (
[IDC] [int] IDENTITY (1,1) NOT NULL ,
[objectid] [int] NULL ,
[objectname] [nvarchar] (776) NULL ,
[objecttype] [varchar] (100) NULL ,
[depname] [varchar] (776) NULL ,
[deptype] [varchar] (100) NULL ,
[depupdated] [varchar] (10) NULL ,
[depselected] [varchar] (10) NULL ,
[depcolumn] [varchar] (100) NULL
) ON [PRIMARY]

declare @objid int /* the id of the object we want */
declare @found_some bit /* flag for dependencies found */
declare @dbname sysname

declare lcReport cursor LOCAL FAST_FORWARD for select 'name' = (o1.name),
    'type' = substring(v2.name, 5, 16),
   'oid' = (o1.id)
  from [dbo].sysobjects  o1
   ,master.dbo.spt_values v2
   ,[dbo].sysusers  s6
  where o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
  and o1.uid = s6.uid
  and left(o1.[name],3) <> 'dt_'
  and (o1.xtype = 'p'
  or o1.xtype = 'u'
  or o1.xtype = 'v'
  or o1.xtype = 'fn')
  order by 'type', 'name'

open lcReport
declare @objname nvarchar(776)
declare @objtype varchar(50)
declare @oid int

fetch next from lcReport into @objname, @objtype, @oid

while @@fetch_status = 0
begin
set @dbname = parsename(@objname,3)

/*
** See if @objname exists.
*/
select @objid = object_id(@objname)
if @objid is not null
BEGIN
  /*
  ** Initialize @found_some to indicate that we haven't seen any dependencies.
  */
  set @found_some = 0

  insert [dbo].#Report select @oid, @objname, @objtype, '', '', '', '', ''

  /*
  ** Print out the particulars about the local dependencies.
  */
  if exists (select *
   from [dbo].sysdepends
    where id = @objid)
  begin
   insert [dbo].#Report select @oid, @objname, @objtype, 'name' = (o1.name),
     type = substring(v2.name, 5, 16),
     updated = substring(u4.name, 1, 7),
     selected = substring(w5.name, 1, 8),
                'column' = ISNULL(col_name(d3.depid, d3.depnumber),'')
   from [dbo].sysobjects  o1
    ,master.dbo.spt_values v2
    ,[dbo].sysdepends  d3
    ,master.dbo.spt_values u4
    ,master.dbo.spt_values w5 --11667
    ,[dbo].sysusers  s6
   where o1.id = d3.depid
   and o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
   and u4.type = 'B' and u4.number = d3.resultobj
   and w5.type = 'B' and w5.number = d3.readobj|d3.selall
   and d3.id = @objid
   and o1.uid = s6.uid
   and deptype < 2
   order by 'name','column'

set @found_some = 1
end

  /*
  ** Now check for things that depend on the object.
  */
  if exists (select *
   from [dbo].sysdepends
    where depid = @objid)
  begin
   insert [dbo].#Report select distinct @oid, @objname, @objtype, 'name' = (o.name)+ ' depends on',
    type = substring(v.name, 5, 16),
    '', '', ''
    from .[dbo].sysobjects o, master.dbo.spt_values v, [dbo].sysdepends d,
     [dbo].sysusers s
    where o.id = d.id
     and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
     and d.depid = @objid
     and o.uid = s.uid
     and deptype < 2

set @found_some = 1
end
END

fetch next from lcReport into @objname, @objtype, @oid
end

close lcReport
deallocate lcReport

CREATE CLUSTERED INDEX [idx_TDR] ON [dbo].[#Report]([IDC]) ON [PRIMARY]

update [dbo].#Report set objectname = '', objecttype = '' where depname <> ''

select IDC,
'Object'=case when objecttype <> '' then 'Database Object '+objecttype+' '+objectname
when depcolumn <> '' And CharIndex('depends on',depname,1) = 0 then '     Depends on '+deptype+' '+depname + ' column ' + depcolumn
when deptype <> '' And CharIndex('depends on',depname,1) = 0 then '     Depends on '+deptype+' '+depname
when depcolumn <> '' then '     Required by '+deptype+' '+replace(depname,' depends on','') + ' column ' + depcolumn
when deptype <> '' then '     Required by '+deptype+' '+replace(depname,' depends on','') end,
'Input'=ISNULL((select top 1 replace(replace(query,Char(9),' '),',@',', @') from [dbo].#Initial where [id] = [objectid] and objecttype <> ''),'')
into [dbo].#Final
from [dbo].#Report
ORDER BY IDC ASC

select object,input from [dbo].#Final

drop table [dbo].#Report
drop table [dbo].#Initial
drop table [dbo].#Final

set nocount off
GO

Posted Feb 16 2005, 08:22 AM by Raymond Lewallen with 12 comment(s)

Filed under: Sql Development

About Me

Raymond Lewallen