Free Tools For Developers

Unlike Scott Hanseleman's long list of useful tools, following are just a few free utilities I use most during my work:

.NET related:

• Reflector (http://www.aisto.com/roeder/dotnet/): an excellent tool to view the source code from .NET assemblies.

Web related:

• IE Developer Toolbar Beta (http://www.microsoft.com/downloads/details.aspx?FamilyID=e59c3964-672d-4511-bb3e-2d5e1db91038): an IE add-on to view/edit page's DOM objects, JavaScript, CSS, etc.
  
• FireBug(http://www.joehewitt.com/software/firebug/): a FireFox add-on with similar functions of IE developer toolbar. You can debug JavaScript easily with latest version of 0.4.
  
• Fiddler (http://www.fiddlertool.com/): a traffic monitor tool to view all data transferred between your IE browser and a server.
  

File related:

• WinMerge (http://sourceforge.net/projects/winmerge/): a tool to compare and merge the files.
  
• NotePad++ (http://sourceforge.net/projects/notepad-plus/): a notepad that's much powerful than Microsoft's.

SQL Server 2005 Tips

List All Recent Changes

SELECT * FROM sys.objects WHERE create_date >= '2006-04-01' OR modify_date >= '2006-04-01'

List all Stored Procedures

SELECT * FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Or
SELECT * FROM sys.objects WHERE [type]='p' AND is_ms_shipped=0 AND [name] NOT LIKE 'sp[_]%diagram%'
--Note: 'NOT LIKE' is to skip stored procedures created during database installation.

Delete All User Created Stored Procedures

SELECT 'Drop Procedure ' + name FROM sys.procedures WHERE [type] = 'P' AND is_ms_shipped = 0 AND [name] NOT LIKE 'sp[_]%'

List Schemas Owned By A Login

SELECT * FROM sys.schemas WHERE principal_id = user_id('DBUser')
--Note: To delete a login we need to change owner of the schemas owned by that login

Cross Apply

--Select top 5 quantity of production:
CREATE FUNCTION dbo.GetOrderDetail(@OrderID AS int, @MaxRow)

RETURNS TABLE AS
RETURN
SELECT TOP(MaxRow) * FROM OrderDetails WHERE OrderID = @OrderID ORDER BY Quantity DESC
GO

SELECT O.OrderID, O.Date, D.ProductName, D.Quantity
FROM Orders AS O CROSS APPLY GetOrderDetail(O.OrderID, 5) AS D

CTE(Common Table Expressions), ROW_NUMBER And RANK

--Efficient Paging:
DECLARE @PageNumber int
SET @PageNumber = 2;
DECLARE @PageSize int
SET @PageSize = 10;
WITH CTE_ORDER (OrderID, TotalAmount, Ranking, PageNumber) AS
(
SELECT O.OrderID, O.TotalAmount,
RANK() OVER (ORDER BY O.TotalAmount DESC) AS Ranking,
CEILING((ROW_NUMBER() OVER (ORDER BY O.TotalAmount DESC)) * 1.0 / @PageSize) AS PageNumber
FROM
(SELECT OrderID, SUM(Amount) AS TotalAmount FROM OrderDetails GROUP BY OrderID) AS O
)
SELECT * FROM CTE_ORDER WHERE PageNumber = @PageNumber
--Row_NUMBER() is incremental and unique but Rank() can be duplicate

--Feb. 2007 Updated: Concatenate column values using CTE
--http://www.projectdmx.com/tsql/rowconcatenate.aspx
;WITH CTE (CategoryID, JoinName, Name, length )
AS
(
SELECT CategoryID, CAST('' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM Products GROUP BY CategoryId
UNION ALL
SELECT p.CategoryId, CAST( JoinName +
CASE WHEN length = 0 THEN '' ELSE ',' END + p.Name AS VARCHAR(8000)),
CAST(p.Name AS VARCHAR(8000)), length + 1
FROM CTE c INNER JOIN Products p ON c.CategoryID = p.CategoryID
WHERE p.Name > c.Name
)
SELECT CategoryId, JoinName
FROM ( SELECT CategoryId, JoinName,
RANK() OVER ( PARTITION BY CategoryID ORDER BY length DESC) AS Ranking
FROM CTE) AS r
WHERE r.Ranking = 1

Configure Firewall Setting with Netsh

Check machine firewall setting:
Netsh firewall show state
Netsh firewall show config
Netsh firewall show allowedprogram
Netsh firewall show portopening

If firewall blocks access to the SQL Server:

Netsh firewall set portopening tcp 445 SQLNP ENABLE ALL
Netsh firewall set portopening tcp 1433 SQL_PORT_1433 ENABLE ALL
Netsh firewall set portopening udp 1434 SQLBrowser enable ALL

Check Connection Status

Select * --P.spid, P.status, P.program_name, P.cmd
FROM
master.dbo.sysprocesses P with (nolock) JOIN
master.dbo.sysdatabases D with (nolock) ON P.dbid = D.dbid
WHERE D.Name = 'Northwind'


Efficiently Get Total Row Number

SELECT rowcnt FROM sysindexes WHERE OBJECT_NAME(id) = 'NorthWind'
AND indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1