Category: SQL

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

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.

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:

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:

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

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:

HTH

 

Refreshing views with sp_refreshview

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:


HTH

sp_recompile is your friend

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:


HTH


*Note: sp_recompile can also recompile triggers

Some useful date processing SQL Snippets

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


And here’s the result for the query above


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

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 ) :



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



The using the undocumented stored procedure sp_MsForEachDatabase approach



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]



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]



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]



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!


 

List SQL Servers using SqlDataSourceEnumerator

I wrote an article a few years ago using SQL SMO and just realized today that I can also list SQL Severs without importing an additional assembly to my project(Microsoft.SqlServer.Management.Smo) by using the SqlDataSourceEnumerator class. SqlDataSourceEnumerator is a class that provides a mechanism for enumerating all intances of SQL Server in a given network. SqlDataSourceEnumerator exposes a method called GetDataSources() that returns a DataTable containing the list of SQL Servers and some basic information about the server. Below is a sample function I built that shows you how to use the SqlDataSourceEnumerator class.



[C# Version]


/// <summary>
/// A method that returns a list of all available SQL Servers in the network
/// </summary>
/// <param name=”shouldSortList”>Specifies whether the list should be sorted or not</param>
/// <returns>a generic list of string containing the servers found</returns>
private List<string> GetSqlServers(bool shouldSortList)
{
   //create the list that would hold our servers
   List<string> listOfServers = new List<string>();

   //create a new instance of our SqlDataSourceEnumerator
   SqlDataSourceEnumerator sqlEnumerator = SqlDataSourceEnumerator.Instance;

   //get the datatable containing our sql servers
   DataTable sqlServersTable = sqlEnumerator.GetDataSources();

   //iterate thru all the rows
   foreach (DataRow rowOfData in sqlServersTable.Rows)
   {
      //get the server name
      string serverName = rowOfData["ServerName"].ToString();
      //get the instance name
      string instanceName = rowOfData["InstanceName"].ToString();

      //check if the instance name is empty
      if (!instanceName.Equals(String.Empty))
      {
         //append the instance name to the server name
         serverName += String.Format(“\{0}”, instanceName);
      }

      //add the server to our list
      listOfServers.Add(serverName);
   }

   //sort the list if the sort option is specified
   if (shouldSortList)
   {
      //sort it!
      listOfServers.Sort();
   }

   //return our list
   return listOfServers;
}

/// <summary>
/// A method that returns a list of all available SQL Servers in the network
/// </summary>
/// <returns>a generic list of string containing the servers found</returns>
private List<string> GetSqlServers()
{
   //get the servers with the list sorted
   return GetSqlServers(true);
}


[VB.NET Version]


”’ <summary>
”’ A method that returns a list of all available SQL Servers in the network
”’ </summary>
”’ <param name=”shouldSortList”>Specifies whether the list should be sorted or not</param>
”’ <returns>a generic list of string containing the servers found</returns>
Private Function GetSqlServers(ByVal shouldSortList As Boolean) As List(Of String)
   ’create the list that would hold our servers 
   Dim listOfServers As New List(Of String)()

   ’create a new instance of our SqlDataSourceEnumerator 
   Dim sqlEnumerator As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance

   ’get the datatable containing our sql servers 
   Dim sqlServersTable As DataTable = sqlEnumerator.GetDataSources()

   ’iterate thru all the rows 
   For Each rowOfData As DataRow In sqlServersTable.Rows
      ’get the server name 
      Dim serverName As String = rowOfData(“ServerName”).ToString()
      ’get the instance name 
      Dim instanceName As String = rowOfData(“InstanceName”).ToString()

      ’check if the instance name is empty 
      If Not instanceName.Equals(String.Empty) Then
         ’append the instance name to the server name 
         serverName += String.Format(“{0}”, instanceName)
      End If

      ’add the server to our list 
      listOfServers.Add(serverName)
   Next

   ’sort the list if the sort option is specified 
   If shouldSortList Then
      ’sort it! 
      listOfServers.Sort()
   End If

   ’return our list 
   Return listOfServers
End Function

”’ <summary>
”’ A method that returns a list of all available SQL Servers in the network
”’ </summary>
”’ <returns>a generic list of string containing the servers found</returns>
Private Function GetSqlServers() As List(Of String)
   ’get the servers with the list sorted 
   Return GetSqlServers(True)
End Function


One thing to note about the SqlDataSourceEnumerator.GetDataSources which is mentioned in the documentation is that it will not always return a complete list of the available servers, and the list might not be the same on every call. Which in layman terms mean that its not the most accurate way of listing SQL Servers. I suggest you use the NetServerEnum function with the server type specified to SV_TYPE_SQLSERVER if you want something that is more accurate. The only drawback with NetServerEnum is that you need to use PInvoke but don’t worry the guys at PInvoke.NET already has a sample class which you can use as a reference when trying to deal with the murky waters of PInvoke.


I hope this helps someone in the future. If you are interested, you can download the source code for this article in both VB.NET and C# here.

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

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: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable

I’ve been working on alot of SQL Server lately due to the current project i’m assigned to and I found myself this morning needing a query that would truncate all the tables in one of my staging database. My initial thought is that I can do this using a cursor that would hold all truncate statements and execute each one of them one at a time so within 5 minutes i was able to build a query that looks like this:


–declare a variable that would hold the query to be executed
DECLARE @TruncateQuery varchar(4000)

– create a cursor that would hold our truncate statements
DECLARE TruncateQuerries CURSOR LOCAL FAST_FORWARD
FOR SELECT
    N‘TRUNCATE TABLE ‘ +
    QUOTENAME(TABLE_SCHEMA) +
    N‘.’ + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
        TABLE_TYPE = ‘BASE TABLE’
    AND    OBJECTPROPERTY    (
                            OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
                            N‘.’ + QUOTENAME(TABLE_NAME)
                        ), ‘IsMSShipped’) = 0

– read our truncate statements
OPEN TruncateQuerries
– loop thru each statement in our truncate statement cursor
FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery
WHILE @@FETCH_STATUS = 0
BEGIN
    –execute the statement
    EXEC(@TruncateQuery)
    –assign the current truncate statement to our @TruncateStatement variable
    FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery 


END
– close our cursor
CLOSE TruncateQuerries
– and free up the resources
DEALLOCATE TruncateQuerries


Looks great right? Then I realized what my good friend Jon Galloway told me once that there are hidden stored procedures in SQL Server and 1 of those stored proc is sp_MSforeachtable. It’s an undocumented sp so you won’t find anything about it in the SQL Books Online. What this stored procedure basically does is that it lets you execute a command or a set of commands against all tables inside a database. Before we go into further detail lets look at the parameters that sp_MSforeachtable expects.


exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand


Where:(description taken from [LINK])



  • @RETURN_VALUE – is the return value which will be set by “sp_MSforeachtable”
  • @command1 – is the first command to be executed by “sp_MSforeachtable” and is defined as a nvarchar(2000)
  • @replacechar – is a character in the command string that will be replaced with the table name being processed (default replacechar is a “?”)
  • @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2
  • @whereand – this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000)
  • @precommand – is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table
  • @postcommand – is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables

All other parameters are optional except for @command1 which is first statement that would be executed.


Now, lets rewrite our truncate table query from above to use the sp_MSForeachtable procedure:


EXEC [sp_MSforeachtable] @command1=”TRUNCATE TABLE ?”


What??? Tha’t it? 1 line? Crazy huh? I didn’t realized that it was that easy until i tried it. Man, If i knew this when I started writing my query above I could have not wasted 5 minutes of my life in something that can be done in 10 seconds. Thanks for hiding feature this Micrsoft!


But wait! There’s more, now i’ve truncated my tables i need a way to reseed all the identity columns on my database:


EXEC [sp_MSforeachtable] @command1=”DBCC CHECKIDENT (?, RESEED, 100)”


What??? Another 1 liner? Whats even better is that I could also REINDEX all the tables in my DB with one line of beautiful SQL code:


EXEC [sp_MSforeachtable] @command1=”DBCC DBREINDEX(‘?’)”


Wanna show progress message on each execution? Try this version:


EXEC [sp_MSforeachtable] @command1=”RAISERROR(‘DBCC DBREINDEX(”?”) …’,10,1) WITH NOWAIT DBCC DBREINDEX(‘?’)”


Oh, Ma! I can’t believe being a programmer could be this easy. ;) I hope i could save someone’s precious time by proving this example because i know it would save mine in the future.

How To: Create Date Dimension Tables in SQL Server 2000/2005

I was writing an application today that needed a date dimension table and this was the script that i built. I don’t know if this going to be useful to anybody.. anyway, my main purpose of putting it in my blog is so that i can just go to my site and do a copy-and-paste when the time comes that I need to use this script again.


First we need to build our table schema. My table schema required an ID column, a Date column, a Year column, a Month column and a Day column. All fields where of int data type except for the Date column. Below is how the script for creating the table looks like.


[STEP 1 - Create Table Schema.sql]


–#############################################################
– Author: Keith Rull
– Script Date: May 11. 2007
– Description: This script creates the table schmema for
– our date dimension table.
–#############################################################
IF EXISTS (
                SELECT
                    *
                FROM
                    sys.objects
                WHERE
                        object_id = OBJECT_ID(N‘[dbo].[DateDimension]‘)
                    AND type in (N‘U’)
            )
DROP TABLE [dbo].[DateDimension]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DateDimension](
    [DateID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [Day] [int] NOT NULL,
CONSTRAINT [PK_DateDimension] PRIMARY KEY CLUSTERED
(
    [DateID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


*NOTE: Please be careful when using this script. A drop table script is include on the first few lines of the file. Please delete it if you dont need to delete the existing date dimension table


I built two scripts for creating a date dimension table. The first script is designed to be use with SQL Server 2000(or earlier) but will still work with SQL Server 2005. I created it by using a while loop instead of using a cursor which an officemate of mine suggested because i didn’t find any urgency to use cursors for this script. Below is the is the script I built:


[STEP 2 - POPULATE THE TABLE WITH DATES.sql]


–#############################################################
– Author: Keith Rull
– Script Date: May 11, 2007
– Description: Use this script if you are using SQL Server 2000
– or earlier. This works with SQL Server 2005 too.
–#############################################################

–declare our date variables
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME
DECLARE @IteratingDate DATETIME

–set the initial dates
SET @LowerBoundDate = ’1/1/1980′
SET @UpperBoundDate = ’1/1/2040′

–create our temporary table
DECLARE @TempDateDimension TABLE
(
        Date DATETIME
    ,    [Year] INT
    ,    [Month] INT
    ,    [Day]    INT
)

–set the iterating date to the lowerbound date
SET @IteratingDate = @LowerBoundDate

–continue to loop until our iterating date is the same as the target date
WHILE @IteratingDate <= @UpperBoundDate
BEGIN
    –insert the date to our temporary table
    INSERT @TempDateDimension
    (
            Date
        ,    [Year]
        ,    [Month]
        ,    [Day]
    )
    VALUES
    (    
            @IteratingDate
        ,    Year(@IteratingDate)
        ,    Month(@IteratingDate)
        ,    Day(@IteratingDate)
    )
    –increase the value of our iterating date
    SET @IteratingDate = @IteratingDate + 1
END

–Insert the dates to our permanent Dimension table
INSERT INTO dbo.DateDimension
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    @TempDateDimension

–View the contents of the table
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    dbo.DateDimension WITH(NOLOCK)


The script above expects you to change the start and end date. What the script does is that it iterates from a lowerbound date to an upperbound date and inserts those dates into our DateDimension table. Pretty slick huh?


What’s cooler is that you can do the same script without using a while loop(or cursors) with one the new features of SQL Server 2005 called Common Table Expressions. The resulting script would look like this:


[STEP 2 - POPULATE THE TABLE WITH DATES(SQL 2005).sql]


–#############################################################
– Author: Keith Rull
– Script Date: May 11, 2007
– Description: Use this script if you want to use the new
– SQL Server 2005 featured called Commong Table Expressions.
– This script only works with SQL Server 2005
–#############################################################

–declare our date variables
DECLARE @LowerBoundDate DATETIME
DECLARE @UpperBoundDate DATETIME

–set the initial dates
SET @LowerBoundDate = ’1/1/1980′;
SET @UpperBoundDate = ’1/1/2040′;

–create our temporary table
DECLARE @TempDateDimension TABLE
(
        Date DATETIME
    ,    [Year] INT
    ,    [Month] INT
    ,    [Day]    INT
);

–use CTE to create our date iterator
WITH CalculatedDate AS
(
SELECT    
        @LowerBoundDate Date

UNION ALL

SELECT
        (Date + 1)
FROM
            CalculatedDate
WHERE
        ((Date + 1) <= @UpperBoundDate)
)

    –insert each date to our temporay dimension table
    INSERT INTO @TempDateDimension
    SELECT
            Date
        ,    Year(Date) AS [Year]
        ,    Month(Date) AS [Month]
        ,    Day(Date) AS Day
    FROM
        CalculatedDate

OPTION (MAXRECURSION 0)

–Insert the dates to our permanent Dimension table
INSERT INTO dbo.DateDimension
SELECT     
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    @TempDateDimension

–View the contents of the table
SELECT
        Date
    ,    [Year]
    ,    [Month]
    ,    [Day]
FROM
    DateDimension WITH(NOLOCK)


Sweet! It looks confusing at first but once you get use to it(and know whole nine yards about CTE) you’ll find how great CTE of a help can be to you in your SQL projects.


I hope this helps someone with a similar dilema that I had to day.


Get the scripts here: Date Dimension Table.zip | Date Dimension Table(using CTE).zip