Keith Rull : SQL

How To: Count the occurrence of a character in a string in SQL

keithrull — Wed, 09 Jun 2010 19:03:00 GMT

Last night I was trying to cleanup the spammers from the database of devpinoy.org and while I was evaluating the result sets i was able to conclude that aside from using common spam text like 'cheap', 'buy', 'free', 'deal', 'viagra', 'prozac' that 30% of the false emails that spam accounts are using multiple dots on their email address. A good example is a subset below from the list of offenders that I found in the devpinoy db.

Having found that fact I immediately created a sql script that will delete users from the database if they have more than 2 dots in their email address.

Enough with the side note and here is some code.

DECLARE @string2check varchar(50)
DECLARE @character2find char

SET @string2check = 'this is a very long string'
SET @character2find = 'i'

PRINT LEN(@string2check) - LEN(REPLACE(@string2check, @character2find, ''))

What the code above is doing is that it is removing the characters that matched our search key and then subracts the length of that string to the original string to find the total occurrence of the character we are looking for.

Now, if you want to use this as a function you can use this:

CREATE FUNCTION udf_CountCharOccurence
(    @string2check varchar(500)
 ,    @character2find char
)RETURNS INT
BEGIN

RETURN (LEN(@string2check) - LEN(REPLACE(
                                @string2check, 
                                @character2find)
                                        )
                                )

END
GO

The code above works great but there's a catch. If you are concerned with case sensitivity then the code above wont work. The way around it is to use COLLATION which is supported by the SQL function below:

CREATE FUNCTION udf_CountCharOccurenceCaseSensitive
(    @string2check varchar(500)
 ,    @character2find char
)RETURNS INT
BEGIN

RETURN (LEN(@string2check) - LEN(REPLACE(
                                @string2check COLLATE SQL_Latin1_General_Cp1_CS_AS, 
                                @character2find COLLATE SQL_Latin1_General_Cp1_CS_AS, '')
                                        )
                                )

END
GO

In order to use this in your query all you need to do is

PRINT dbo.udf_CountCharOccurenceCaseSensitive('This is a long text','i')

Or if you want to put it to use to meet the criteria that I mentioned about dots on emails you can do it this way:

SELECT * FROM Users
WHERE dbo.udf_CountCharOccurenceCaseSensitive(EmailAddress,'.') > 2

HTH

Refreshing views with sp_refreshview

keithrull — Tue, 19 Aug 2008 16:00:00 GMT

One of the common problems that you would encounter when you are building applications that utilize views is that sometimes there are cases wherein a view gets out of date. This happens when you add a new column to a table a view is refrencing.

To fix this some people would delete the view and recreate it but there is better solution called sp_refreshview. sp_refreshview updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends^{[description from msdn]}.

Here's a remark from MSDN regarding sp_refreshview:

"If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried."

The syntax is pretty straight-forward

EXECUTE sp_refreshview '<view name>'

Once executed the views definition would be updated.

What if I want to execute sp_refreshview on all the views in my database? The answer is to create a cursor that would execute sp_refreshview on each of your view. Below is a script that does exacrly what you need:

--Refresh the underlying metadata of all views
DECLARE @viewName AS VARCHAR(255)

DECLARE listOfViews CURSOR FOR
SELECT [name] 
FROM sysobjects 
WHERE xtype = 'V'


OPEN listOfViews

    FETCH NEXT FROM listOfViews into @viewName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

        FETCH NEXT FROM listOfViews INTO @viewName
        EXEC sp_refreshview @viewName

    END

CLOSE listOfViews

DEALLOCATE listOfViews

HTH

sp_recompile is your friend

keithrull — Tue, 19 Aug 2008 15:47:00 GMT

I've been doing a lot of code deployments lately and I've come across several occasions wherein my stored procedures wouldn't run as fast as expected compared to it's previously known execution time. The problem lies on the statistics not getting updated after change has been made against an index or other object that may affect efficiency. Since stored procedures are compiled, recompiling them would update these statistics.

Here's a remark about sp_recompile taken from the MSDN website

"The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries."

The syntax is pretty straight-forward:

EXEC sp_recompile '<name of your sp>'

Once executed the stored procedure would then be marked for recompilation and would then be recompiled on the next execution. Niffty huh?!

But what if I want to recompile all my stored procedures? Well, fear not! You can use a cursor that would iterate on all the stored procedure in your current database and execute an sp_recompile against all of them. Below is the script to accomplish this task:

--Recompile all stored procedures on the current database
DECLARE @StoredProcedureName AS VARCHAR(255)

DECLARE listOfStoredProcedure CURSOR FOR
SELECT [Name] FROM sysobjects 
WHERE XTtype = 'P'


OPEN listOfStoredProcedure

    FETCH NEXT FROM listOfStoredProcedure into @StoredProcedureName

    WHILE (@@FETCH_STATUS <> -1)
    BEGIN

        FETCH NEXT FROM listOfStoredProcedure INTO @StoredProcedureName
        EXEC sp_recompile @StoredProcedureName

    END

CLOSE listOfStoredProcedure

DEALLOCATE listOfStoredProcedure

GO

HTH

*Note: sp_recompile can also recompile triggers

Some useful date processing SQL Snippets

keithrull — Thu, 24 Jul 2008 15:44:00 GMT

Here's an update to my blog entry 3 years ago regarding the same topic:

---Calculates the first day of the previous month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1, 0) 
AS [First day of the previous month]

---Calculates the first day of current month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0) 
AS [First day of the current month]

---Calculates the first day of next month
SELECT DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0) 
AS [First day of the next month]

---Calculates the last day of the previous month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) 
AS [Last day of the previous month]

---Calculates the last day of the current month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) 
AS [Last day of the current month]

--Calculates the last day of the next month
SELECT DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) 
AS [Last day of the next month]

--Calculates the first day of the year
SELECT DATEADD(yy, DATEDIFF(yy,0,GetDate()), 0) 
AS [First day of the year]

--Calculates the first day of the quater
SELECT DATEADD(qq, DATEDIFF(qq,0,GetDate()), 0) 
AS [First day of the quarter]

--Calculates the first monday of the month
SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(dd, 6 - DATEPART(Day,GetDate()),GetDate())), 0) 
AS [First monday of the month]

--Calculates the last day of the prior month
SELECT DATEADD(mm, DATEDIFF(mm,0,GetDate()), 0) 
AS [Last day of the previous month]

--Calculates the last day of the prior year
SELECT DATEADD(yy, DATEDIFF(yy,0,GetDate()), 0)
AS [Last day of the previous year]

--Calculates the last day of the current year
SELECT DATEADD(mm, DATEDIFF(m,0,GetDate() ) + 1, 0)
AS [Last day of the current year]

--Calculates the monday of the current week
SELECT DATEADD(wk, DATEDIFF(wk,0,GetDate()), 0)
AS [Monday of the current week]

--Calculates the yesterdays date
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), -1)
AS [Yesterdays date]

--Calculates the todays date
SELECT GetDate()
AS [Todays date]

--Calculates the tommorows date
SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 1)
AS [Tommorows date]

---Calculates the 15th day of previous month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) - 1 , 0))
AS [15th day of previous month]

---Calculates the 15th day of current month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
AS [15th day of current month]

---Calculates the 15th day of next month
SELECT DATEADD(d, 14, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))
AS [15th day of next month]

--Gets the name of the current month
SELECT DATENAME(month, GetDate())
AS [Name of the current month]

--Gets the weekday name of the current date
SELECT DATENAME(dw, GetDate())
AS [Weekday name of the current date]

--Gets the weekday name of the current date next year
SELECT DATENAME(dw, DATEADD(yy, 1, GetDate()))
AS [Weekday name of the current date next year]

--Gets the weekday name of the current date last year
SELECT DATENAME(dw, DATEADD(yy, -1, GetDate()))
AS [Weekday name of the current date last year]

And here's the result for the query above

First day of the previous month
-------------------------------
2008-06-01 00:00:00.000

(1 row(s) affected)

First day of the current month
------------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

First day of the next month
---------------------------
2008-08-01 00:00:00.000

(1 row(s) affected)

Last day of the previous month
------------------------------
2008-06-30 00:00:00.000

(1 row(s) affected)

Last day of the current month
-----------------------------
2008-07-31 00:00:00.000

(1 row(s) affected)

Last day of the next month
--------------------------
2008-08-31 00:00:00.000

(1 row(s) affected)

First day of the year
-----------------------
2008-01-01 00:00:00.000

(1 row(s) affected)

First day of the quarter
------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

First monday of the month
-------------------------
2008-07-07 00:00:00.000

(1 row(s) affected)

Last day of the previous month
------------------------------
2008-07-01 00:00:00.000

(1 row(s) affected)

Last day of the previous year
-----------------------------
2008-01-01 00:00:00.000

(1 row(s) affected)

Last day of the current year
----------------------------
2008-08-01 00:00:00.000

(1 row(s) affected)

Monday of the current week
--------------------------
2008-07-21 00:00:00.000

(1 row(s) affected)

Yesterdays date
-----------------------
2008-07-23 00:00:00.000

(1 row(s) affected)

Todays date
-----------------------
2008-07-24 11:40:57.557

(1 row(s) affected)

Tommorows date
-----------------------
2008-07-25 00:00:00.000

(1 row(s) affected)

15th day of previous month
--------------------------
2008-06-15 00:00:00.000

(1 row(s) affected)

15th day of current month
-------------------------
2008-07-15 00:00:00.000

(1 row(s) affected)

15th day of next month
-----------------------
2008-08-15 00:00:00.000

(1 row(s) affected)

Name of the current month
------------------------------
July

(1 row(s) affected)

Weekday name of the current date
--------------------------------
Thursday

(1 row(s) affected)

Weekday name of the current date next year
------------------------------------------
Friday

(1 row(s) affected)

Weekday name of the current date last year
------------------------------------------
Tuesday

(1 row(s) affected)

I'm hoping that I could update this regularly. Did I miss anything? Post it on the comments and lets start an archive of useful sql date scripts.

How To: List databases in a SQL Server using T-SQL and truncate their logs

keithrull — Tue, 22 Jan 2008 16:59:00 GMT

Finally i'm back after a month of crazyness due to my webhost not performing up to its promise of 99.9% uptime.

Enough with the rant and on with the article, today i'm going to talk about how to list databases in a SQL Server by just using T-SQL and then showing you how to truncate the logs on those databases in different ways.

First, let's look at the 5 different approaches that you can use to list down databases in SQL Server by just using plain old T-SQL.

The stored procedure approach ( sp_databases, sp_helpdb ) :

 USE master;

-- Lists databases that either reside in an instance of the SQL Server 
-- 2005 Database Engine or are accessible through a database gateway.
EXEC sp_databases

-- Reports information about a specified database or all databases.
EXEC sp_helpdb

The sys tables approach ( sys.databases, sys.sysdatabases ):

USE master;

-- [SQL Server 2000/2005] Contains one row for each database in an instance of 
-- Microsoft SQL Server 2005. When SQL Server is first installed, sysdatabases 
-- contains entries for the master, model, msdb, and tempdb databases. 
SELECT NAME FROM sysdatabases

-- [SQL Server 2005] Contains one row per database in the instance of Microsoft SQL Server. 
SELECT NAME FROM sys.sysdatabases

The using the undocumented stored procedure sp_MsForEachDatabase approach

USE master;

-- Undocumented SQL Server stored procedure
EXEC sp_msForEachDB 'PRINT ''?''';

Simple huh? Now that we know how to list databases let's go back to the problem of truncating all of them in one query (in fact it's just one line!). Some people would suggest writing cursors that would loop thru all the rows returned by our sysdatabases query to do this task. Their solution might be similar to the one listed below:

[SOLUTION 1]

USE master;

DECLARE 
    DBNames CURSOR
FOR
    SELECT 
        NAME 
    FROM sysdatabases

OPEN DBNames

DECLARE @Name varchar(50)

FETCH NEXT FROM DBNames
INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    DBCC SHRINKDATABASE( @Name , 0)

    FETCH NEXT FROM DBNames
    INTO @Name

END

CLOSE DBNames
DEALLOCATE DBNames

Others would suggest a more primitive approach which is building a simple query first that would list the names of the database appended with the DBCC SHRINKDATABASE command:

[SOLUTION 2]

USE master;

SELECT 
    'DBCC SHRINKDATABASE(' + NAME  + ', 0)'
FROM sysdatabases

Then they would change the output option of the query into "Results to Text", copy the result to a new query window and execute the query from there. Pretty primitive. Alot of steps. Same results.

My suggested solution is using the sp_MSForEachDatabase procedure in conjunction with the DBCC SHRINKDATABASE function. This would result into a 1 line query. Less code with less steps to do that creates the same results.

[MY RECOMMENDED SOLUTION]

USE master;

-- truncates all the logs of all database in the server
EXEC sp_msForEachDB 'DBCC SHRINKDATABASE( ''?'', 0)'

Simple and straight to the point.

I hope you learned something from our article today. You can also do this programmatically by using C# and VB.NET. I wrote two articles about that topic here and here. Interested in truncating tables using sp_MsForEachTable? You can checkout this post.

Thanks!

How to: Get a list of fixed drives and their free space inside SQL Server

keithrull — Fri, 07 Sep 2007 15:30:00 GMT

I didn't know that I could do this inside SQL Server

EXEC master..xp_fixeddrives

Executing the procedure onmy server gave me this result set:

drive  MB free
C       5897
E       33334

Man, I think it's about time I upgrade and brush up my SQL skills. I don't know why you would do it inside SQL Server. But then again it's pretty cool to know that something like this exist. You never know, you might need it someday.

How To: List All Available .NET Data Providers

keithrull — Sun, 27 Aug 2006 18:51:00 GMT

.NET 2.0 introduces to us the class known as DBProviderFactories which enables us to access available providers on a machine. the DBProviderFactories class has method called GetFactoryClasses() that returns a DataTable that contains any class that is registered on the GAC that implements the DBProviderFactory class.

[ C# Version ]

using System;
using System.Data;
using System.Data.Common;

namespace KeithRull.ListAllAvailableDataProviders
{
   class Program
   {
      static void Main(string[] args)
      {
         DataTable providersTable = DbProviderFactories.GetFactoryClasses();

         //loop thru each provider in the providers data table
         foreach (DataRow providerRow in providersTable.Rows)
         {
            //loop thru each column in the providers table
            foreach (DataColumn providerColumn in providersTable.Columns)
            {
               //print the information
               Console.WriteLine(providerColumn.ColumnName + ": " + providerRow[providerColumn].ToString());
            }
            //create a extra line feed for data separation
            Console.WriteLine("");
         }

      //show the information to the screen
      Console.ReadLine();
      }
   }
}

[ VB.NET Version ]

Imports System
Imports System.Data
Imports System.Data.Common

Namespace KeithRull.ListAllAvailableDataProviders
   Class Program
      Shared Sub Main(ByVal args() As String)
         Dim providersTable As DataTable = DbProviderFactories.GetFactoryClasses()

         Dim providerRow As DataRow
         For Each providerRow In providersTable.Rows
            Dim providerColumn As DataColumn
            For Each providerColumn In providersTable.Columns
               Console.WriteLine(providerColumn.ColumnName + ": " + providerRow(providerColumn).ToString())
            Next
            Console.WriteLine("")
         Next
         Console.ReadLine()
   End Sub
End Class
End Namespace

HowTo: List Database Objects in SQL Server using sysobjects, syscolumns and systypes

keithrull — Sat, 17 Jun 2006 17:52:00 GMT

I have been working more and more with data recently and its a painstaking task. Alot of my time includes analysis of trades and financial information to create and calculate error free result sets.

This also includes data cleanup..

My task today was to create positional and transactional data for investments. Since the company that i am working for right now is thinking of consolidating the data into one system instead of two(theres two system that basically handles the same thing).This involves digging into one of the biggest database that i have seen and finding out to properly extract data without any idea on how they structured their database (the 3rd party vendor is selling us services to do this but the company declined since it would cost alot of money.)

So here i am digging to every column, every table, every function and every view. I realized that it would be pretty hard to manually go to every database to discover what columns it contain(since there was no db diagram provided by the vendor).

The programmer inside me thought of using SQL SMO/DMO since this would just require me tho modify one of my previous demo regarding this subject.

but... i decided not too since this would be just a one time thing and can be accomplished using sql scripts and just pasting the resultset to Excel so that i could have a suitable documentation for the database structure.

One thing to remember is that sysobjects, syscolumns and systypes are your friends. This are the database objects that we would be using for this demo.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

select * from sysobjects;

The most useful column in this table is the xtype column. This column signifies what type of object is in the returned rowset. specifying

select * from sysobjects where xtype = 'u' ;

will return the list of tables on the current database. Here's a list of possible values for xtype:

C : CHECK constraint
D : Default or DEFAULT constraint
F : FOREIGN KEY constraint
L : Log
P : Stored procedure
PK : PRIMARY KEY constraint (type is K)
RF : Replication filter stored procedure
S : System tables
TR : Triggers
U : User table
UQ : UNIQUE constraint (type is K)
V : Views
X : Extended stored procedure
TF : Functions

You can use syscolumns to retrieve columns on the database. doing

select * from syscolumns;

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

127 : bigint
173 :  binary
104 :  bit
175 :  char
61 :  datetime
106 :  decimal
62 :  float
34 :  image
56 :  int
60 :  money
239 :  nchar
99 :  ntext
108 :  numeric
231 :  nvarchar
59 :  real
58 :  smalldatetime
52 :  smallint
122 :  smallmoney
98 :  sql_variant
231 :  sysname
35 :  text
189 :  timestamp
48 :  tinyint
36 :  uniqueidentifier
165 :  varbinary
167 :  varchar

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.

Putting it all together: So my idea behind this article was to create a list of available tables, and views with their underlying columns. The idea is that i can just join the sysobjects to the syscolumns to get the the tables columns and the link it to systypes to get the column datatype. The complete scripts are listed below as reference to how i was able to accomplish this task.

[code language="T-SQL"]

-----------------------------------------
-- List only the tables in this database
-----------------------------------------
SELECT
o.name AS [Table Name],
o.type,
c.name AS [Col Name],
s.name AS [Col Type],
c.prec,
c.scale,
c.isnullable
FROM
dbo.sysobjects AS o
INNER JOIN
dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE (
   o.type = 'U'
)

-----------------------------------------
-- List only the views in this database
-----------------------------------------
SELECT
o.name AS [View Name],
o.type,
c.name AS [Col Name],
s.name AS [Col Type],
c.prec,
c.scale,
c.isnullable
FROM
dbo.sysobjects AS o
INNER JOIN
dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE (
   o.type = 'V'
)

-----------------------------------------
-- List only the functions in this database
-----------------------------------------
SELECT
o.name AS [Funtion Name],
o.type,
c.name AS [Col Name],
s.name AS [Col Type],
c.prec,
c.scale,
c.isnullable
FROM
dbo.sysobjects AS o
INNER JOIN
dbo.syscolumns AS c
  ON
   c.id = o.id
INNER JOIN
dbo.systypes AS s
  ON
   c.xtype = s.xtype
WHERE (
   o.type = 'TF'
)

[/code]

hmm.. now i need to go back to work :) have fun!