Which Tables are Fueling Your DB Growth?

Here's a link to a wonderful script from Mitchel Sellers that creates a stored procedure that, when executed, returns a list of all the tables in your database and the size of each table.

Here's what the output looks like.

tableName	numberofRows	reservedSize	dataSize	indexSize	unusedSize
authors	227	32 KB	24 KB	8 KB	0 KB
books	822	112 KB	40 KB	72 KB	0 KB

The only drawback to the output is that you can't sort by size as it's a text field.

UPDATE:
Ok, so the drawback has been bugging me for a while, so I decided to eliminate it. Replace all code in the stored procedure above AFTER 'DEALLOCATE tableCursor' with the following:

--Another procedure level temp table to store the results minus the 'kb'
CREATE TABLE #TempTable2
(
tableName varchar(100),
numberofRows int,
reservedSize int,
dataSize int,
indexSize int,
unusedSize int
)

INSERT INTO #TempTable2
SELECT tableName,
CAST(numberofRows AS int),
CAST(LEFT(reservedSize, LEN(reservedSize)-3) AS int),
CAST(LEFT(dataSize, LEN(dataSize)-3) AS int),
CAST(LEFT(indexSize, LEN(indexSize)-3) AS int),
CAST(LEFT(unusedSize, LEN(unusedSize)-3) AS int)
FROM #TempTable

--Select all records so we can use the reults
SELECT * 
FROM #TempTable2
ORDER BY reservedSize DESC

--Final cleanup!
DROP TABLE #TempTable
DROP TABLE #TempTable2

Undocumented Stored Procedures

Here's a link to an excellent article at SQLServerCentral.com on undocumented stored procedures in SQL Server 2005. Free registration is required (and well worth it).

Minimum and Maximum Rate by Timekeeper Title

This fairly simple script will find the current minimum and maximum rate per title for non-terminated timekeepers whose time entry flag is checked. This is based on rates in the timerate table versus the accounting rate table.

SELECT tktitle AS 'Title',
MIN(tkrt01) AS 'Minimum Rate',
MAX(tkrt01) AS 'Maximum Rate'
FROM timekeep
INNER JOIN (
SELECT tkinit, MAX(tkeffdate) AS tkeffdate
FROM timerate
GROUP BY tkinit) AS recentrate ON recentrate.tkinit=timekeep.tkinit
INNER JOIN timerate ON recentrate.tkinit=timerate.tkinit AND recentrate.tkeffdate=timerate.tkeffdate
WHERE tkeflag = 'Y' AND tktmdate IS NULL
GROUP BY tktitle

Working Timekeeper Collections for a Specific Billing Timekeeper

This query will return a summary of collections by working timekeeper for a specific billing timekeepers historical matters. This will give you insights as to who exactly is working on the matters of a specific billing attorney. Included in this query is also an example of how to use the OVER() function available to those on SQL Server 2005 and 2008. The OVER() function allows you to sum the contents of a column to facilitate, among other things, creating a percent-to-total value for each row without an additional call to the database. This query also uses the timekeeper name user defined function.

The core of this query was submitted by Penny Turner, IS Director at Smith Haughey Rice & Roegge and was edited to include the percent to total functionality and grand total. Many thanks to Penny!

SELECT mttk AS 'TK #',
dbo.udfTKFullName(mttk) AS 'Working TK',
Collections,
CAST(1.00*Collections/SUM(Collections) OVER() AS DECIMAL(16,4)) AS 'Pct to Total'
FROM
(
SELECT mttk,
tklast,
SUM(mtdocodc) AS 'Collections'
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0
GROUP BY mttk,tklast
) AS a
UNION
SELECT 'GT','Grand Total',SUM(mtdocodc),NULL
FROM mattimhs
INNER JOIN matter ON mtmatter=mmatter
INNER JOIN hmatter ON hmatter=mmatter AND mbillaty=hmbaty
INNER JOIN timekeep ON tkinit=mttk
INNER JOIN periodt ON mtper=pe
WHERE YEAR(peendt) = '2009'
AND hmbaty ='2100'
AND pebedt BETWEEN hmdate1 AND hmdate2
AND mtdocodc<>0