Grant execute on all stored procedures

Ever wanted to grant execute permissions on all the user stored procedures for a user?
I had to do this and I ended up writing this cursor. Instead of executing directly, I opted to print the execute commands so that I can review before executing.

DECLARE @spname nvarchar(100)
DECLARE spgrant CURSOR FOR 
select name from sys.objects where type = 'P' AND is_ms_shipped = 0
OPEN spgrant
FETCH NEXT FROM spgrant INTO @spname
WHILE @@FETCH_STATUS = 0
BEGIN 
 Print 'GRANT EXECUTE ON OBJECT::'+@spname+ ' TO Cherry'
FETCH NEXT FROM spgrant INTO @spname
END
CLOSE spgrant

DEALLOCATE spgrant

Create a database user if it doesn't already exist

Similar to OBJECT_ID function which can be used to check whether it already exists before creating/dropping, we can use SUSER_ID function check whether the database user exists before creating. This can be handy when you have a user rebuild script and it often errors out when the script tries to add a user that is already in the database

IF SUSER_ID('Cherry') IS NULL
 CREATE USER [Cherry] FOR LOGIN [Cherry]
ELSE Print 'Already exists' 

Delete duplicate rows using CTE, Surrogate Key, and SELECT DISTINCT

Once again, this is from one of the chapters from the book SQL Server 2008 Bible. I have used other sources like MSDN, Stackoverflow and Google in general in writing this post :)

  Removing Duplicates

 Lets create a table and populate it with some values

USE AdventureWorks;
GO 

SELECT TOP 100 * INTO dba_PersonCopy FROM Person.Person
GO

--Lets see the data

SELECT * FROM dba_PersonCopy
GO

The Original table has Primary Key on Business_Entity_Id, but the table we generated using SELECT INTO will not inherit primary keys, indexes etc. Since we would like to deal with duplicates on a table that doesn't have a primary key this suits our purpose.So lets go ahead and add insert top 50 records a couple of times so that we have 200 rows, 100 of them duplicate and waiting to be removed. I added 100 records 50 at a time by using GO [Count] syntax. Check it out, its cool.

INSERT INTO dba_PersonCopy 
 SELECT TOP 50 * FROM Person.Person
GO 2

Lets check if the table indeed has duplicates

SELECT BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 AdditionalContactInfo,
 Demographics,
 rowguid,
 ModifiedDate,
 COUNT (*) AS DuplicateCount
FROM dba_PersonCopy
 GROUP BY BusinessEntityID,
  Persontype,
  NameStyle,
  Title,
  FirstName,
  MiddleName,
  LastName,
  Suffix,
  EmailPromotion,
  AdditionalContactInfo,
  Demographics,
  rowguid,
  ModifiedDate
 HAVING COUNT(*) >1;

At this point I faced an error.

Error: Msg 305, Level 16, State 1, Line 25
The XML data type cannot be compared or sorted, except when using the IS NULL operator.

It seems the demographics column and AdditionalContactInfo are the offending ones. While I need to study more on this problem, since its out of topic, I will just get rid of the columns

ALTER TABLE dba_PersonCopy DROP COLUMN Demographics;
ALTER TABLE dba_PersonCopy DROP COLUMN AdditionalContactInfo;
GO

Modified query to check duplicates

SELECT BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate,
 COUNT (*) AS DuplicateCount
FROM dba_PersonCopy
 GROUP BY BusinessEntityID,
  Persontype,
  NameStyle,
  Title,
  FirstName,
  MiddleName,
  LastName,
  Suffix,
  EmailPromotion,
  rowguid,
  ModifiedDate
 HAVING COUNT(*) >1;

Take a look at the DuplicateCount column and you'll see duplicates. So how do we go about eliminating them?

 1. Removing duplicates by Over() clause with a Row_number() function and a partition. Details about ROW_NUMBER() Function here

SELECT BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate,
 ROW_NUMBER() 
OVER 
(
 PARTITION BY
 BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate
 ORDER BY BusinessEntityID
) AS RowNumber
FROM dba_PersonCopy;

When you execute this, you'll see duplicates will have the RowNumber column greater than 1. We now need a CTE (Common table expression) to delete the duplicate rows by using a WHERE clause

 
BEGIN TRAN

;WITH DupesCTE 
AS
(SELECT BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate,
 ROW_NUMBER() 
OVER 
(
 PARTITION BY
 BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate
 ORDER BY BusinessEntityID
) AS RowNumber
FROM dba_PersonCopy
)
DELETE DupesCTE WHERE RowNumber >1
--ROLLBACK
-- 100 rows affected, duplicates removed!

Btw, I struggled for about half an hour with syntax issues here. It said there is a syntax error at the keyword 'AS'. The problem is, I missed a ";" at the beginnig! Thanks to the answer on this thread or I'd be stuck forever.

 2. Next method is to delete duplicates by using a Surrogate Key. We'll create a Key column to uniquely identify each column since such a column doesn't exist on the table already

ALTER TABLE dba_PersonCopy 
 ADD TheKey INT IDENTITY NOT NULL
 CONSTRAINT PK_PersonCopyDuplicates PRIMARY KEY;
SELECT * FROM dba_PersonCopy 

Now we issue a DELETE command to get rid of the duplicates

BEGIN TRAN
DELETE dba_PersonCopy
 WHERE EXISTS (
  SELECT * FROM dba_PersonCopy AS T1
   WHERE T1.BusinessEntityID = [dba_PersonCopy].BusinessEntityID
   AND T1.PersonType =[dba_PersonCopy].Persontype
   AND T1.NameStyle = [dba_PersonCopy].NameStyle
   AND T1.Title = [dba_PersonCopy].NameStyle
   AND T1.FirstName = [dba_PersonCopy].FirstName
   AND T1.LastName = [dba_PersonCopy].LastName
   AND T1.MiddleName = [dba_PersonCopy].MiddleName
   AND T1.Suffix = [dba_PersonCopy].Suffix
   AND T1.EmailPromotion = [dba_PersonCopy].EmailPromotion
   AND T1.rowguid = [dba_PersonCopy].rowguid
   AND T1.ModifiedDate = [dba_PersonCopy].ModifiedDate
   AND T1.TheKey > [dba_PersonCopy].TheKey)
--ROLLBACK

I'll just Drop the primary key and the column to test next example

ALTER TABLE dba_PersonCopy DROP CONSTRAINT PK_PersonCopyDuplicates;
ALTER TABLE dba_PersonCopy DROP COLUMN TheKey;

3. Deleting using SELECT DISTANT

SELECT DISTINCT 
 BusinessEntityID,
 Persontype,
 NameStyle,
 Title,
 FirstName,
 MiddleName,
 LastName,
 Suffix,
 EmailPromotion,
 rowguid,
 ModifiedDate INTO dba_PersonCopy_Noduplicates 
FROM dba_PersonCopy;

SELECT COUNT(*) FROM dba_PersonCopy_NoDuplicates
-- 100 rows remaining

I like the CTE option the best of all these methods

Return data from an UPDATE and DELETE -- OUTPUT clause

It is possible to view the changes you've made using the OUTPUT clause while you update a table. Lets test that. I created a copy of Person.Person table from AdventureWorks to test this with top 100 rows.

--Lets delete something 
USE AdventureWorks;
GO

BEGIN TRAN
DELETE TOP (10)  dba_PersonCopy 
OUTPUT deleted.* 

--ROLLBACK

You'll see The deleted Rows.

--Lets Do an update and see what was updated and what was it updated to

UPDATE dba_PersonCopy 
 SET PersonType = 'NT'
 OUTPUT deleted.Persontype AS OldPersonType, inserted.PersonType AS UpdatePersonType
WHERE rowguid = '92C4279F-1207-48A3-8448-4636514EB7E2'

More info at BOL

Insert results of a stored procedures into a temporary table

I found many people asking similar questions on stackexhange and other places. Of all the methods, I liked this answer from stackoverflow which uses OPENROWSET. Below is the method (I didn't write this, just saving it here for my future reference. Full credit goes to the original posted on StackOverflow) You first need to enable adhoc distributed queries using sp_configure in order to use openrowset.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO


-- Then use the openrowset query to insert into temp table

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\BINYOGA;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

I was reading the SQL Server Bible yesterday and learned that we can insert the results of a stored procedure into a table using the below syntax

INSERT INTO dbo.MyTable[(columns)]
  EXEC StoredProcedure Parameters;
 

So when I tried to test that, there was an error -

So, it looks like you are not allowed to use a temptable there. You need to create the table first (it can be a temp table) so that it has the right number of columns as the output.

-- Lets create a simple stored procedure
CREATE PROCEDURE dba_databaselist
AS
SELECT name FROM sysdatabases

--Create the temp table
CREATE TABLE #MyTempTable
(
 name varchar(30),
 
)

--insert from stored procedure

INSERT #myTempTable
 EXEC dba_databaselist

-- verify

SELECT * FROM #myTempTable

UPDATE: Here is another way to do it. Declare a table variable and insert the results of stored procedure to the table.

 

-- Lets create a stored procedure. I am gonna use AdventureWorks database
USE AdventureWorks
GO
CREATE PROC dba_Person
AS
SET NOCOUNT ON;
SELECT FirstName, Lastname 
FROM Person.Person;
--another result set
SELECT FirstName, Lastname
FROM Family.dbo.Person
RETURN;

--Lets execute the stored procedure

Exec AdventureWorks.dbo.dba_Person

-- You'll see two result sets, now lets declare a table variable and insert the data from result sets

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    FirstName varchar(30),
 LastName varchar(30)
    );

-- Now, INSERT/EXECUTE command
INSERT @MyTableVar
 EXEC dbo.dba_Person
-- Verify
SELECT * FROM @MyTableVar

NOTE: I have emulated examples from SQL Server Bible 2008 book in this post.

Create table from stage table on the fly

If you have a script that does nightly drop and recreate of tables, your drop query should look something like this:

IF OBJECT_ID('dbo.StageTable', 'U') IS NOT NULL
  DROP TABLE dbo.StageTable;

What if you have to create a bunch of destination tables from stage table on the fly? You can use:

IF OBJECT_ID('dbo.DestinationTable') IS NULL
  SELECT * INTO dbo.DestinationTable FROM dbo.StageTable WHERE 1=0

The above creates the table structure with no data in it and you can then do your inserts as necessary. This is particularly useful when you have to wade through thousands of tables in a database using the GUI, select desired table and generate a create script for it.

Remote database backups and restores using SQLCMD

One of the clients I work for has a lot of servers in countries like Somalia, Kenya, Afghanistan with what we call 'field databases' on them. The users often request for a . Though these servers are on the same vpn, it is often very painful to RDP to the remote server, take a backup, compress and upload to FTP (or copy to a network share). On a busy day, we usually have about 10 such database copy requests involving ten different servers. Automation is the way to go right? So we came up with the below script which uses linked server and OPENQUERY to make database copying process a bit easier.

begin
declare @dbname nvarchar(30)
set @dbname = 'DATABASENAME'
declare @servername nvarchar(50)
set @servername = 'FIELDSERVER'
declare @SrcBackupLocaction nvarchar(100)
set @SrcBackupLocaction = '\\' + @servername+ '\backups\'
declare @DestBackupLocaction nvarchar(100)
set @DestBackupLocaction = '\\HOMESERVER\BackupsFromField\'
print @DestBackupLocaction 
declare @backupFileName nvarchar(60)
declare @backupStmt nvarchar(500)
declare @compactStmt nvarchar(500)
declare @UncompactStmt nvarchar(500)
declare @copyStmt nvarchar(500)
--declare @destFile nvarchar(100)
declare @backupNameFullPath nvarchar(500)
declare @mdf nvarchar(500)
declare @ldf nvarchar(500)
declare @dblogName nvarchar(60)

--Declare @RestoreDBName nvarchar(30)
select @backupFileName = @dbname + '_'+ (Select replace(CONVERT(varchar(10),t,10),'-','') as t from openquery(FIELDSERVER, 'select getdate() as t'))+'_'+ @servername
set @backupStmt = 'BACKUP DATABASE ' + @dbname+ ' to disk=N''' + @SrcBackupLocaction + @backupFileName + '.bak'''
print @backupStmt 
print 'Backing up database to ' + @backupFileName

print @SrcBackupLocaction
print @DestBackupLocaction

set @backupNameFullPath = N'E:\Backups\BackupsFromField\'+ @backupFileName +'.bak'
set @mdf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '.mdf'
set @ldf = N'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\' + @backupFileName + '_1.LDF'
set @dblogName = @dbname + '_log'

/*
print @backupNameFullPath 
print @mdf
print @ldf
print @dblogName
end
*/

--Backup database
 exec FIELDSERVER.master.dbo.sp_executesql @backupStmt
print 'database BackUp completed '
print '--------------------------'

--Compact the backed up database
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @compactStmt = N'"E:\Program Files\7-Zip\7z.exe" a -tzip '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @SrcBackupLocaction + @backupfilename + '.bak -mx5'
print @compactStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @compactStmt

print 'database compressed successfully'
print '--------------------------'

--Copy compacted backup file
set @copyStmt = N'Copy '+ @SrcBackupLocaction + @backupfilename + '.zip ' + @DestBackupLocaction + ' /V /Y /Z'
print @copyStmt 
 Exec FIELDSERVER.master.dbo.xp_cmdshell @copyStmt

print 'Copied Compressed file copied successfully'
print '--------------------------'


--UnCompact the copied zip file
--set @compactStmt = N'Compact /C '+ @SrcBackupLocaction + @backupfilename + '.bak'
set @UncompactStmt = N'"E:\Program Files\7-Zip\7z.exe" e -y -o' + @DestBackupLocaction + ' '+ @DestBackupLocaction + @backupfilename + '.zip'
print @UncompactStmt 
 Exec dbo.xp_cmdshell @UncompactStmt

print 'Unzip the file successfully'
print '--------------------------'


RESTORE DATABASE @backupfilename FROM  DISK = @backupNameFullPath
WITH  FILE = 1,  
MOVE 'DataFile' TO @mdf,  
MOVE 'LogFile' TO @ldf,  NOUNLOAD,  STATS = 10


print 'Database ' + @dbname + ' created successfully'
print '-------End of Procedure-------------------'

End

Nice isn't it? But there is a catch, you need to add the field servers as linked servers on HomeServer, RDP to the field server, open management studio, connect to the HomeServer and then execute this script to achieve the desired result. If for some reason, the network connection times out while the compressed backup is being copied, we would have to go back to the manual mode to complete the rest of the steps(some of these servers are on 256 kbps internet connections and are often fickle). What if a user needs a database urgently? What if you can't remote into the field server at all? Then I found SQLCMD. Here is what I do these days - Log on to the HomeServer (which is a decent server with better resources and which is located in US) Open command prompt and connect to the remote server using SQLCMD

You can get the help about SQLCMD commands by using sqlcmd /? in command prompt. Since we use Windows authentication and I am a domain admin, I just specify the server name to connect to and I am in. Then issue the backup command. All of our field servers are alike in drive and folder structure, so all I need is the name of the database to copy. If there are long and funky names you can choose from:

SELECT name FROM sysdatabases
GO

Then, issue the backups command:

BACKUP DATABASE [My_Field_Database] TO  DISK = N'E:\MyFieldServer\BackupPath\MyFiledDB_082712.bak' 
WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

We use standard edition on field servers so you won't see COMPRESSION in the backup query :)

 I know that 7zip is installed on all the field servers and I know the path. So I will now use sqlcmd and xp_cmdshell to compress the backup so that it is easier for me to copy to HomeServer: I know xp_cmdshell is a security threat etc - so lets enable it, use it and then disable it. No harm done.

exec sp_configure 'show advanced options', 1
reconfigure
GO
exec sp_configure 'xp_cmdshell', 1
reconfigure
GO

--Lets now compress that backup using xp_cmdshell
exec xp_cmdshell '"E:\Program Files\7-Zip\7z.exe" a -tzip E:\YourFieldBackups\FileName.7z E:\YourFieldBackups\BackupName.bak -mx5'

-- Okay, we are done. Lets disable the cmdshell dude

exec sp_configure 'show advanced options', 1
reconfigure
GO
exec sp_configure 'xp_cmdshell', 0
reconfigure
GO

The rest is easy. You know where the compressed backup file is. You can now use xcopy to move the file to HomeServer

xcopy \\remoteserver\bacups\compressedfile.7z e:\backups\

I like this method a lot! I just need to put these steps together in a script and add some error handling etc. Thats a task for the near future!

Drop tables

I was reading Itzik Ben-Gan's T-SQL Fundamentals book and I learned something that I didn't know so far. You can drop multiple tables in a single drop query. I know, its stupid. I am monument of stupidity right now!

drop table dbo.Orders, dbo.Employees

NULL

For relational databases, the NULL is the unknown or it can simply mean that the value is absent. Just like in real world you can't add something to an unknown quantity and make it a known one in the relational world. So NULL is like the evil queen from fairy tales that turns every known value to unknown if not properly handled. Lets see

SELECT 'binyoga' + NULL;
GO 

Results a NULL. It seems this is the reason that some developers don't like allowing NULLs in their databases. Anyways, we all know that an unknown is not equal to another unknown, right? Even if it is, it is still unknown. SQL Server knows that too.

IF NULL = NULL
 SELECT 'Whoa, I cracked the unknown'
ELSE
 SELECT 'Nope. Unknown is not equal to unknown :(' 

So how do you deal with this? Enter IS NULL.

IF NULL IS NULL
 SELECT 'Yes. It is NULL. You don''t need my help to figure'
ELSE
 SELECT 'No, the world is ending. NULL is NOT NULL anymore'

You can use this guy to find out all the people that doesn't have Mr/Mrs titles in AdventureWorks database.

SELECT LastName + ',' + FirstName AS Name
FROM Person.Person
WHERE Title IS NULL
-- I wonder why 18K people have no titles!

Okay, so you push data to reports people and they don't like NULLs. How do you handle that? Enter ISNULL() function. More about that later. Trivial and basic stuff - yes. But blogging about it is a way to think again about it. Where did I get this info from? Like I said, I have been reading SQL Server Bible by Paul Nielsen and my next few posts are likely to be influenced by the book. Its an aswesome book, btw!

Why can't I drop this table?

Cannot DROP TABLE 'dbo.PleaseDropMe' because it is being referenced by object '_dta_mv_25'.

Looking at the _dta_ prefix, it looked like a view created from database engine tuning advisor. The view was created with SCHEMABINDING . When a view is created with SCHEMABINDING specified, the underlying tables can not be modified in a way that would break the view. Explanation here. So in this case I first had to drop the view and then the table

 This is a nice feature if you would like to restrict some of your important tables from being 'accidentally' dropped. Lets test it:

USE BINYOGA
GO
IF OBJECT_ID('dbo.DropTest', 'U') IS NOT NULL
DROP TABLE dbo.DropTest;

CREATE TABLE dbo.DropTest (
 c1 int primary key,
 c2 char(2)
)
GO

-- Lets create a view

CREATE VIEW DropTestView WITH SCHEMABINDING
AS
SELECT c1, c2 FROM dbo.DropTest
GO

--Lets try and drop the table

DROP TABLE dbo.DropTest
GO

You'll see the error

Msg 3729, Level 16, State 1, Line 1 Cannot DROP TABLE 'dbo.DropTest' because it is being referenced by object 'DropTestView'.

SQL Server Unattended installations

I was aware of the configuration.ini file that gets created during the normal GUI installation of SQL Server. This configuration file can be changed and reused to perform command line/remote installations. But in order to generate that initial file, you have to go through the GUI steps initially. Thats what I used to think. I have been reading SQL Server Bible by Paul Nielsen (Its a great book, btw. I wish I read it a couple of years ago) and I came across an interesting piece of info about unattended installations - Microsoft ships a template.ini file that you can use to kick off remote installations in the installation dvd. 

There is a long (very long!) article on BOL about command line and unattended installs:

There are two ways to go about it

You run the setup.exe file from command prompt specifying the components, passwords you want to install, passwords etc:

Start /wait <CD or DVD Drive>\servers\setup.exe /qb INSTANCENAME=<InstanceName> ADDLOCAL=All PIDKEY=<pidkey value with no "-"> SAPWD=<StrongPassword> SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domain\user> AGTPASSWORD=<DomainUserPassword> SQLBROWSERACCOUNT=<domain\user> SQLBROWSERPASSWORD=<DomainUserPassword>

Use the template.ini file to create a configuration.ini file and kick off the install

setup.exe /settings <full path to your .ini file>

Example:

setup.exe /settings C:\binyoga\sqlinstall.ini

You may use \qn switch for a silent installation with no dialogs and \qb switch if you can live with progress dialogs.

Click here for a sample template.ini file

select top x % from a table

I didn't know you could do this. Thanks to @shark at dba.stackexchange, now I do:

Source

declare @top_val int = 30
select top (@top_val) percent
    col1,
    col2,
    col3from yourTable

SQL Server Internals Viewer - CodePlex

Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works

I found it very useful while troubleshooting. You can download it from here

Btw, Codeplex is looking good after Metro UI makeover.  

Exporting table to excel - TSQL

There are many ways to export a table to excel - using SSIS/DTS package, bcp or simply right click on the grid results, copy and paste results to a new excel file - but most of them are cumbersome.

Here is an easier method which uses sp_makewebtask stored procedure. It needs to be enabled first using sp_configure.

sp_configure 'show advanced options' ,1;
GO
RECONFIGURE;
GO
sp_configure 'Web Assistant Procedures',1;
GO
RECONFIGURE
GO

Then, execute the below:

EXEC sp_makewebtask @outputfile = 'E:\testing.xls'
 ,@query = 'Select * from [RSBY-FINAL]..Tbl_policyRenewalDetails'
 ,@colheaders = 1
 ,@FixedFont = 0
 ,@lastupdated = 0
 ,@resultstitle = 'Testing details'
GO

The new Who Is Active

... is here! go get it

SQL Server - Stress Testing

Came across this tool today for SQL Server stress testing. With the PFIN upgrade looming, I am gonna put this tool to use one of these days: http://www.sqlstress.com/Overview.aspx

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