Category: SQL

How to delete all stored procedures from MSSQL database using cursor

January 24, 2012 Posted by Matt under SQL
No Comments

Here is a code for deleting all stored procedures in SQL Server database using cursor. DECLARE @name varchar(500) DECLARE @sql varchar(max) SET @sql = ” DECLARE cur CURSOR FOR SELECT [name] FROM sys.procedures OPEN cur FETCH NEXT FROM cur INTO @name WHILE @@fetch_status = 0 BEGIN SET @sql = ‘DROP PROC ‘ + @name PRINT…

SQL Data Types vs. C# Data Types

April 15, 2011 Posted by Matt under .NET, SQL
2 Comments

This article is just a reference of SQL Data Types to C# Data Types. SQL Server data type CLR data type (SQL Server) CLR data type (.NET Framework) varbinary SqlBytes, SqlBinary Byte[] binary SqlBytes, SqlBinary Byte[] varbinary(1), binary(1) SqlBytes, SqlBinary byte, Byte[] image None None varchar None None char None None nvarchar(1), nchar(1) SqlChars, SqlString…

Clustered vs. Non-clustered indexes in SQL Server

October 14, 2010 Posted by Matt under SQL
No Comments

In this article I will make attempt to outline differences between clustered and non-clustered indexes in SQL Server. Be fully aware that it is not going to be comprehensive explanation, but rather brief overview highlighting purpose of both types of indexes. Indexes Indexes are one of the most important part of database optimization, especially for large…

How to refer to previous row in SQL 2005

September 22, 2010 Posted by Matt under SQL
1 Comment

In this post I will describe simple mechanism allowing to refer back to previous row in SQL Server 2005/2008 database table.   For the purpose of this article let’s create a table dbo.AgentLog:   create table dbo.AgentLog ( AgentID int ,LogInTime datetime ,LogOutTime datetime ) go insert dbo.AgentLog select 1, ’2010-09-08 09:40:00.000′, ’2010-09-08 10:14:00.000′ union all select…

How to drop all schema objects in MS SQL 2005

August 10, 2010 Posted by Matt under SQL
2 Comments

I needed to test my SQL implementation script that was a part of big SQL Server Data Warehouse release. For anybody who have ever done large SQL implementation fundamental question is always “Have I scripted everything that is needed?”. So how to check if your implementation script contains all objects you require? Here is a few steps that helped me: Collate…