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: