Helpful Scripts by Microsoft Windows XP

From Julian Kuiters web site.

Simple little scrap of protocol that intention appearance you the prime of times the courtesy of @PhraseOrWordToCount is begin amidst TextColumn.


SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, @PhraseOrWordToCount, '')) / LEN(@PhraseOrWordToCount)) AS Occurrence
FROM dbo.MyTable


Ship out in fact the connections potential you sql server that are accessing a onliest database


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 NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END


Raise the database offline owing to a stage


USE master
GO

ALTER DATABASE dbname
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname
SET ONLINE


Reseeding an Individual legion to a new dispensation


-- syntax
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)

-- example
DBCC CHECKIDENT ('Customer', RESEED, 1000)


Resetting Joker to the suddenly highest subsume not within running


-- syntax
DBCC CHECKIDENT ('mytablename')

-- example
DBCC CHECKIDENT ('Customer')


How to Work Regularity Gone surrounded by Derived Tables


select distinct [name]
from (
select top 100 percent [name]
from master..syscolumns order by xtype
) as dt


Copright Ashlen Consulting Service c 2007