I am getting settled in my new project, and it only took me a week to get rights to install anything on the laptop! A lot of people would say "Wow, a billable week with no work!" but personally, the days just drag by. I'd much rather be buried in code. I did make some good use of the time though, digging into what documentation I could find and creating a new Functional Specification Document. I did finally get my rights, so tomorrow will be spent installing things and looking into code again.

I did get some reading done though, and I saw that in VS2010 C# will have optional and named parameters. I know the VB crowd (of which I come from) will chuckle a little at that, as that feature has been around forever. Until now, it's required C# developers to overload functions. Nothing Earth-shattering about this feature, other than as part of it, C# will support named parameters and default values as well as part of the implementation (the default values are part of the defiinition of the function).

As I was looking through Scott Hanselman's List of Tools for 2009, I saw a reference for Microsoft's Sketchflow (part of Microsoft Expression), which allows you to design (among other things) and work through ideas prior to coding. I am going to check it out this weekend, and maybe some of the alternate tools he recommended for his post.  I might even have to dust off my Table PC, as it seems like a good combination.

I can't take credit for this, as I took it from the article How To Get Table Row Counts Quickly And Painlessly on SqlServerCentral.com by Kendal Van Dyke. However, it such a good tip, I had to change it a bit, as I tend to use this a LOT.

First, I made the results a little more "printable" but reducing the table name column. Next, I made it a system proc because when I go onto client sites, I am looking at many databases. I probably don't need to create a procedure for it, but there have been many cases where I trend things, and it would be nice to have this handy. Finally, I added a parameter to allow me to change the sorting. By default, it sorts by table name. I added an optional parameter to allow me to pass in an integer (could have done it a million ways, I know, but I chose to do this solely for my preferences -- feel free to change to yours).

Usage:  sp_retrieveRowCounts    --- sorts by table name

            sp_retrieveRowCounts 1  ---sorts by row count, then table name

Here's the proc:

I posted about sp_findsp previously and decided to revisit it. I didn't write it (see previous post for credit/link) but I did add a bit to it. First, I cleaned it up a bit to make it easier to read, and second, I made it so that it is a system proc so I could access it from any db on my server. I may end up doing some cleanup on it's output, but that's all I had time for for now. So here goes, the new version!

USE master
GO

PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_findsp
END
GO


CREATE PROC sp_findsp @s varchar(255)
AS
BEGIN
    DECLARE @msg varchar(255) ,@ul varchar(255)
    SELECT @s='%' + @s + '%'
    
    SELECT 'SP Name'=upper(o.name), Seq=colid ,'SP Line'=substring(text,patindex(@s,text)-5, 30)
    FROM syscomments c , sysobjects o
    WHERE o.id=c.id
    and patindex(@s,text) > 0
    ORDER BY [name]

    SELECT @msg='* Stored procedures containing string "' + @s + '=' + convert(varchar(8),@@rowcount) + ' *'

    SELECT @ul=replicate('*',datalength(@msg))
    PRINT ' '
    PRINT @ul
    PRINT @msg
    PRINT @ul
END
GO

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_findsp
GO

PRINT 'Granting EXECUTE permission on sp_findsp to all users'
GRANT EXEC ON sp_findsp TO public

GO

When I go onto a client site, I usually don't have the luxury of designing everything from scratch. There are times (like on my current engagement) if I were given the choice, I'd say scrap it, but usually it doesn't happen that way. It is what it is... been saying that a lot lately!

So one of the tools I find useful is the ability to search for particular field names in the database. This is very similar to sp_findsp I use, which I have posted in the past off the net, but I have modified it so it gets registered as a system procedure.

So here's sp_findfields. It will return a fairly printable representation of the results of all fields which match the passed in string. It's registered as a system procedure in this script, so it is accessible to all databases.

Usage:

sp_findfields '%Contact%'

This will find all fields with the string 'Item' in it. Note you need to provide the wildcard characters.