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

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

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.

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!

 

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

Thanks Dave for making me laugh today!

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.

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.

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.

Hey guys! I'm here at code camp right now in CalState Fullerton and its been amazing. Kudos to the CodeCamp organizers!

Well.. i wast late abit.. so i wasnt able to listen to Woody and Michelle do their early morning speech(heheh, this is how one of the guys i met here in the camp described it). The registration was fast and really organized. I like how they have the parking directions posted everywhere because last year was a mess specially if you dont know the area(well, one can argue that if you can read parking lot signs then you wont have a problem since the SoCal codecamp website said that the parking for the event is on parking lot F).

One thing i noticed is that there is no schedule given to attendies. Last year they gave away papers containing schedules on where and when to find a specific talk. The good thing this year is that there is internet connectivity so you can check the posted schedule on the CodeCamp website which is non-existent last year.

I'll be updating this on every session i go to(hope i did this last year so that i could have warned people about not going to $%&*^* session(name encrypted to protect that persons ego) because he just reads slides and cant even give a good answer(sample response: "Uh, try searching that on the internet.. again i am just relaying to you what i have read online").

The notes below are from my scratchpad courtesy of Notepad++. This is a work in progress so dont bug me if theres typos on it. :D

9:00 - Mark Rosenberg's talk about Stored Procedure and below is the juice of his talk

[13 tips on  optimizing your stored procedures]
use stored procedures instead of heavy-duty querries
include set no count to on
call using fully qualified name
if you are returning a single integer user a return statement rather than returning one value as a record set
 Output value is for errors only, return value for values you want to be returned by the procedures.
SQL dot notations in 2005 helps in grouping objects in the database.
use the sp_executesql storedprocedure instead of execute
use the sp_executesql instead if temporary stored procedures
breakdown large stored procedures into smaller stored procedures
use @Table variables instead of #Table because #Table lives in tempdb and @table is created in memory.
avoid using ddl statements in your stored procedures
add with recompile option to create procedure statement if you know the query will change each time it id run
use the sql server profiler to determine which stored procedures have been recompiled to often
avoid using having clause
 << i think there are scenarios wherein a having clause is faster than a where clause.. so if you ask me i'll tell you that this suggestion is on a case to case basis.
use union all instead of union

#temp tables
dont use them
ok, sometimes bbut not often
 gettingresults from stored procedure(since you cant use recompile on them)
always create the taable then fill it(helps  with tempdb locking)can index (which can help performance)
dont use select into

@table variables
fster anf more efficient
not good for large datasets that would be saved to disk anyway
less table locking, logging
unfortunately no indexes, but you can have a primary key by using unique or primary key constraint.
much better during transactions
can only be used itn functions stored procedures and batches
when used in stored procedures there is less recompiling
cant apply alter table
cant use select into

dont use cursors if you can help it(use while clause instead)
filter as early in your stored procedure as possible
never select all the columns
sometimes a transaction can actually improve performance(but not often)

quote: "use print statements on querries too count performance" - i greatly disagree on this one.. i think its easier to look at it on the profiler plus adding print statements add clutter in your procedures.

10:15 - Robert Kozak's talk about Attribute Based Programming

He built a nice framework and demoed how to use the same set of classes to do different things. I love how he built his form by just mere UserControls and no code on the form itself. I agree with him when he said that seperation of concern is the way to go in builfing applications. I wasnt able to take down alot of notes because i was enamoured by the amount of cool stuff he was showcasing in his demo. i was able to take note of the third slide though.

 A properly designed framework will
    promote proper seperation between interface, implementation and user interface
    promote lose coupling and strong cohesion
    promote reusability of code(write once use everywhere)
    promotes RAD of production quality code
    wait theres more: automatic API for your application

1:00 - Bill Sheldon's ASP.NET Membership Customization << currently in progress  

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..

Here is some samples on how to use cursors in SQL to do updates and deletes on records. Nothing fancy, just pure t-sql code. :)

Creating A Date Dimension Table in SQL Server

A stored procedure that sends notifications connected users in an SQL Server.

A tool that automatically formats your sql statement in any language (C#, Java, VB and Delphi)

A custom T-SQL function for removing embedded HTML tags

XPSMTP provides a SMTP based SQL Mail solution for sending MIME based email over SMTP, implemented as an Extended Stored Procedure. It does not require any software to be installed, just a SMTP email server that can handle the outgoing mail request. XPSMTP is using TCP/IP sockets to communicate to port 25. XPSMTP does not spawn additional threads, the xp_smtp_sendmail request is handled on the same thread as it is being called on by SQL Server. Each call to xp_smtp_sendmail establishes a connection to the SMTP server and disconnects when done sending the email. The connection is created using asynchronous communication and aborts based on a timeout value (@timeout which by default is 10000 milliseconds, so 10 seconds).

Sql Server script to automate the removal of logins

An SQL syntax cue card to demonstrate basic commands in SQL

Code snippets that discusses how to manipulate the datetime data type in T-SQL