Refresh All Views - SQL Server

Here is a stored procedure that refreshes all views -

CREATE PROCEDURE dba.RefreshAllViews
AS
DECLARE @ViewName NVARCHAR(max)
DECLARE @SQL NVARCHAR(max)

DECLARE RefreshViews CURSOR
FOR
SELECT [name] AS ViewName
FROM sys.VIEWS

OPEN RefreshViews

FETCH NEXT
FROM RefreshViews
INTO @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND name = ''' + @ViewName + ''')
   BEGIN
  exec sp_refreshview N''dbo.' + @ViewName + '''END'

 EXEC (@SQL)

 FETCH NEXT
 FROM RefreshViews
 INTO @ViewName
END

CLOSE RefreshViews

DEALLOCATE RefreshViews
GO

Reduce backup times and MSDB performance tuning

Brent Ozar's post about the backup bottlenecks here: http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/

The backup/restore history is logged in msdb database and over the time, the tables become large that they become backup bottlenecks if you don't cleanup the history.

Here is how you do it: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

World's best SQL Tutorial!

Learning to write SQL queries using this tool doesn't feel like you are actually learning something - I mean, the boredom part of it. It is indeed the worlds best SQL Tutorial. I wish there are more tools like this for othe technologies.

Download it from here: http://sol.gfxile.net/galaxql.html

SQL Server Login Properties

I was searching for something and I stumbled upon this script which returns a login's properties like created time, password details, whether it is active etc.,

What if your server has hundreds of logins and you need to quickly get an overview of all the logins? There may be better ways, but I shamelessly stole the above script and put it inside a cursor, just for for fun :) Enjoy!

DECLARE @name NCHAR(100)
DECLARE @LoginTable TABLE (
 LoginName NCHAR(100)
 ,PasswordLastSetTime SQL_VARIANT
 ,IsExpired SQL_VARIANT
 ,IsLocked SQL_VARIANT
 ,IsMustChange SQL_VARIANT
 ,LockoutTime SQL_VARIANT
 ,BadPasswordcount SQL_VARIANT
 ,BadPasswordTime SQL_VARIANT
 ,HistoryLength SQL_VARIANT
 ,PasswordHash SQL_VARIANT
 ,PasswordLastSet SQL_VARIANT
 )

DECLARE Login_Cursor CURSOR
FOR
SELECT NAME
FROM syslogins

OPEN Login_Cursor

FETCH NEXT
FROM Login_Cursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO @LoginTable
 SELECT @name AS 'LoginName'
  ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSetTime
  ,LOGINPROPERTY(@name, 'IsExpired') AS IsExpiried
  ,LOGINPROPERTY(@name, 'IsLocked') AS IsLocked
  ,LOGINPROPERTY(@name, 'IsMustChange') AS IsMustChange
  ,LOGINPROPERTY(@name, 'LockoutTime') AS LockoutTime
  ,LOGINPROPERTY(@name, 'BadPasswordCount') AS BadPasswordCount
  ,LOGINPROPERTY(@name, 'BadPasswordTime') AS BadPasswordTime
  ,LOGINPROPERTY(@name, 'HistoryLength') AS HistoryLength
  ,LOGINPROPERTY(@name, 'PasswordHash') AS PasswordHash
  ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSet

 FETCH NEXT
 FROM Login_Cursor
 INTO @name
END

CLOSE Login_Cursor

DEALLOCATE Login_Cursor

SELECT *
FROM @LoginTable

Attach a database without log file (ldf fife)

I downloaded SQL Server 2012 RC0 today and the sample database to poke around from here. The download contains the data file (mdf file) of adventure works but not ldf. For a moment, I thought why would codeplex not include ldf file - it seems that it is indeed possible to attach a database without ldf file.

Head over to CREATE Database on BOL and CTRL+F for  " FOR_ATTACH_REBUILD_LOG"

Script below:

How to use a Cursor? - TSQL

A wonderful post on how to use cursors here : http://justgeeks.blogspot.com/2008/08/using-t-sql-cursor-in-sql-server.html

SQL Server to word

Note to self:  http://www.simple-talk.com/sql/t-sql-programming/writing-to-word-from-sql-server/

Introduction to Databases - Stanford online course

Introduction databases online course has been officially launched today by Stanford. This will be the closest I will ever get to be in a Stanford class, though virtual. I won't miss this opportunity :)

SQL online code formatting

Here is a free tool: http://poorsql.com/

Delete backup files older than X days - SQL Server Backups

We use Litespeed for backup compression on one of our production servers and our backup file can only store 2 days worth of full backups and log backups. I have tried scheduling a job to delete the backup files using SQL Server maintenance plan - while it works well with the native backup files, for some reason the job doesn't delete litespeed backups. I did some search and it seems that I am not alone.

I found this thread which has an alternative way of doing this. It is simple. Create a bat file with the below -

forfiles -p "E:\your\backup_drive" -s -m *.* -d -number of days -c "cmd /c del @path"

 If you would like to delete files that are older than 2 days, then it would look like this

forfiles -p "E:\Backups" -s -m *.* -d -2 -c "cmd /c del @path"

For this to work, you need to have forfiles.exe in WINDOWS\systems32 folder.

There are a couple of other ways to delete files older than x days - powershell and VB script.

EDIT: Ah, How did I miss this before? here is a detailed article on mssqltips: