Skip to main content

Count the Occurrence of a Phrase or Word in a Column

Simple little bit of code that will show you the number of times the value of @PhraseOrWordToCount is found in TextColumn. SELECT (LEN(TextColumn) – LEN(REPLACE(TextColumn, @PhraseOrWordToCount, ”)) / LEN(@PhraseOrWordToCount)) AS Occurrence FROM dbo.MyTable I noticed this on Simon Sabin’s blog, I think it has more applications than just FullText searches so I thought I’d do […]

Snippet: Drop all connections to a database

If you want to drop all the connections on you sql server that are accessing a particular database, here’s some code: USE master go DECLARE @dbname sysname SET @dbname = ‘name of database you want to drop connections from‘ DECLARE @spid int SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) WHILE @spid IS […]

Resetting IDENTITY (SQL Server 2000)

I’ve been asked plenty of times, how do you reset an IDENTITY column? After inserting data into a table, and then deleting everything, how do I make the IDENTITY to start from 1 again? Personally I think is a bad idea to reset identity columns to any value lower than they currently are. To find […]

How to Use ORDER BY in Derived Tables

I was asked today how to select distinct columns while ordering by another column that does not appear in the column output list. For example: select distinct [name] from master..syscolumns order by xtype If you try to run this code, you will get the error message: Server: Msg 145, Level 15, State 1, Line 1 […]

Rebuilding and Restoring Master Database

Over the holiday break one of our servers suffered a disk failure in the main raid set. The disk was replaced and the raid set repaired. All seemed to be going well, until SQL Server wouldn’t start. Checking the latest ERRORLOG for SQL Server showed: 2005-12-28 10:44:26.17 spid3 Starting up database ‘master’. 2005-12-28 10:44:26.18 spid3 […]

Rebuilding the Master Database

Rebuilding the master database is done using the Rebuild Master utility “rebuildm.exe” which is located in the 80\Tools\Binn directory of the SQL Server installation. Select the server and instance that you want to rebuild. Now you are going to need access to the files from the install CD for SQL Server. Browse to the Data […]

Restoring the Master Database

Stop the SQL Server instance. And start it in single user mode. Open a command prompt window. Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run: sqlservr.exe -c -m The -c option tells SQL Server to run independantly, and not as a […]