Sql Server (RSS)

Topics related to SQL Server.

Blog moved to WordPress and new domain...

I've moved my blog to my own domain and onto a WordPress engine, CS + SPAM was driving me nuts!

Catchup posts: Oh yes, I've been in Japan for a couple of months, first 2 weeks of photos are uploaded on Flickr, I still havent had a chance to upload the thousands after.

Whats more, I've actually written some information about me so you can finally find out my sad sad way of life. See About Thushan Fernando.
posted Sunday, June 29, 2008 2:57 PM by Thushan Fernando

SOFTWARE: Visual Studio .NET 2008 and .NET 3.5 Released!

Quick post before our Games Programming exam in a few hours, Scott Guthrie's blog has just announced the launch of Visual Studio  .NET 2008 and .NET Framework 3.5...

Yayyy! All the details are on his blog. Download away!
posted Monday, November 19, 2007 10:25 PM by Thushan Fernando with 0 Comments

SOFTWARE: Visual Studio 2008 RTM Next week!

Well what an exciting few weeks/month this has been. We've had:
Just when you thought things couldnt get better and as predicted in an earlier post (well I cheated a bit!) now Microsoft will finally RTM Visual Studio 2008 early next week. MSDN Subscribers can download it from the "Top Subscriber Downloads" list according to the MSDN Subscriptions Blog.
posted Saturday, November 17, 2007 10:51 AM by Thushan Fernando with 1 Comments

PEE-PANTS-WORTHY: Microsoft will be releasing the source to the .NET BCL, ADO.NET, ASP.NET *AND* WPF!!!

I had to read this a few times before I was able to digest it, then I pee'd my pants - it was also raining tonight on the walk back to the car from uni... Scott Guthrie's blog just mentioned about .NET being openned up with the release of Orcas. Here's the important bits:
One of the things my team has been working to enable has been the ability for .NET developers to download and browse the source code of the .NET Framework libraries, and to easily enable debugging support in them.

Today I'm excited to announce that we'll be providing this with the .NET 3.5 and VS 2008 release later this year.

We'll begin by offering the source code (with source file comments included) for the .NET Base Class Libraries (System, System.IO, System.Collections, System.Configuration, System.Threading, System.Net, System.Security, System.Runtime, System.Text, etc), ASP.NET (System.Web), Windows Forms (System.Windows.Forms), ADO.NET (System.Data), XML (System.Xml), and WPF (System.Windows). We'll then be adding more libraries in the months ahead (including WCF, Workflow, and LINQ). The source code will be released under the Microsoft Reference License (MS-RL).

You'll be able to download the .NET Framework source libraries via a standalone install (allowing you to use any text editor to browse it locally). We will also provide integrated debugging support of it within VS 2008.
Unfortunately, the licensing terms make this a 'readonly' view of the source as stated in the Licensing Terms. So what does this mean to .NET Developers? We can finally debug at the Framework level!!! You can even opt to download the sources from MSDN. Checkout the Channel9 video of Shawn Burk, the man behind the curtain that made it happen and discusses the implications! His official announcement is available on Shawn's blog! . If you havent already, grab the latest Beta 2 release of Visual Studio 2008 or wait for the final due out early November.

Personally I cant wait to see the sources for WPF. So many good things coming in the next couple of months, not to forget Ubuntu Gutsy Gibbon (7.10), MythUbuntu and Visual Studio 2008.
posted Thursday, October 04, 2007 11:29 AM by Thushan Fernando with 0 Comments

SQL: Clean a SqlServer Database of tables and Stored Procedures Script

Here's a quick script I wrote to assist in cleaning up a SQL Server database of all tables and Stored Procedures.

WARNING:
THIS *WILL* REMOVE everything in the currently selected database, so USE WITH *EXTREME* CAUTION for heaven's sake! Dont blame me if you blow your company's database without a backup!

Replace [DATABASE_NAME] with whichever DB you would like to clean. This takes on from my previously posted tip about using the undocumented sp_MSforeachtable stored procedure to drop tables post. This is only tested in SQL Server 2005 & 2008, should work in 2000. 
------------------------------------------------------
-- Database Cleaner-up-er for SQL Server 2005
------------------------------------------------------
--  Version:	1.0.0
--  Date:	15/09/2007
--  Author:	Thushan Fernando (thushan [ at ] wsoftware [dot ] biz)
--  URL:	http://blogs.developerfusion.co.uk/blogs/thushan/archive/2007/09/15/3320.aspx	
--	
--  Cleans a database removing all tables and stored
--  procedures.
--  
--  THIS WILL DELETE / DESTROY ALL TABLES AND STORED
--  PROCEDURES. YOU HAVE BEEN WARNED HOMEY!!!
-------------------------------------------------------

USE [DATABASE_NAME]

GO
-- INIT
SET NOCOUNT ON

-- VARIABLES
DECLARE @COMMAND varchar(255)	-- Command to execute
DECLARE @COUNTER int			-- Counter used to iterate
DECLARE @spName varchar(255)	-- Stored procedure name

-- DELETE ALL THE TABLES
-- First disable any constraints on that table
EXEC sp_MSforeachtable @command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

-- Drop the table
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

-- CREATE A TEMPORARY TABLE
CREATE TABLE #StoredProcedures
(ID int IDENTITY (1,1),
 ProcName varchar(128) NOT NULL)

-- INSERT OUR LIST OF STORED PROCEDURES
INSERT INTO #StoredProcedures (ProcName)
	SELECT ROUTINE_NAME 
		FROM INFORMATION_SCHEMA.ROUTINES
	WHERE ROUTINE_NAME NOT LIKE 'dt_%' AND ROUTINE_TYPE = 'PROCEDURE'

-- Get the number of StoredProc's in the database
SELECT @COUNTER = MAX(ID) FROM #StoredProcedures

-- Iterate through the items
WHILE @COUNTER > 0
	BEGIN
		-- Get the name of the Object
		SELECT @spName = ProcName	FROM #StoredProcedures WHERE ID = @COUNTER

		-- Make the command  to execute
		SELECT @COMMAND = 'DROP PROCEDURE ' + @spName
		
		-- Execute the command
		EXEC(@COMMAND)

		-- Reduce the counter
		SET @COUNTER = @COUNTER - 1
	END

-- Cleanup the temporary table
DROP TABLE #StoredProcedures

SET NOCOUNT OFF
GO
There's probably a better way of doing this - asside from DROP DATABASE [DATABASE_NAME] (har-har!), but IWFM!!!
posted Saturday, September 15, 2007 2:19 PM by Thushan Fernando with 0 Comments

HOWTO: Drop all tables in a SqlServer database automajically with hidden stored proc sp_MSforeachtable!!!

Sometimes you just want to start fresh, clean slate, forget the mistakes of the past and just start out brand new. By this I mean for your SqlServer database - not real life or anything... although... How to do that when you dont have Enterprise Manager or SQL Server Manager  handy?

Easy! By using the special hidden MSforeachtable stored proc to retrieve the tables in the database and execute a command for each entry.

*BUT* BE WARNED!!! THIS IS NOT REVERSABLE. SO BE CAREFUL! 
 -- EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
That will remove all the Tables in the database. You may need to run it a couple of times if there are any foreign Key's blocking a table from being removed. You can also use the MSforeachtable to iterate through tables and re-index or check how much space they are taking: 
-- EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'"
-- EXEC sp_MSforeachtable @command1 = "sp_MStablespace '?'"
Funky dory aye?
posted Saturday, September 15, 2007 1:50 PM by Thushan Fernando with 68 Comments