Full-Text Search Engine

Following is the content table:

 Create table content 

(

 Pagename varchar(20) not null primary key,

 URL varchar(30) not null,

 Description  text null,

 Keywords  varchar(4000) null

)

Insert the following values.

                              INSERT content values ("home.asp","home.asp","This is the home page","home,SQL")                              GO                              INSERT content values ("pagetwo.asp","/page2/pagetwo.asp","NT Magazine is great","second")                              GO                              INSERT content values ("pagethree.asp","/page3/pagethree.asp","SQL Magazine is the greatest","third")                              GO 

The table will look like this:

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
home.asp	/home.asp	This is the home page	home, SQL
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

Next, review the full-text enabling procedures, enable all columns for full-text searching, and populate the index.
Let's practice querying this table using some keyword searches. Open SQL Server 7.0's Query Analyzer, and try some of the following queries.
 

Query 1 (FREETEXT)

 

                              SELECT * FROM content WHERE freetext(*,"home") 

Result
Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
home.asp	/home.asp	This is the home page	home, SQL

This queries all full-text-enabled columns in the content table for the string "home."
 

Query 2 (FREETEXT)

 

                              SELECT * FROM content WHERE freetext(description,"Magazine")

Result
Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

This only searches the Description column and returns all matches for the string "Magazine."
 

Query 3 (FREETEXT)

 

                              SELECT * FROM content WHERE freetext(description,"SQL Mag")

Result
Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

Although this appears to search on the string "SQL Mag," it actually searches on "SQL" or "Mag."
 

Query 4 (FREETEXT)

 

                              SELECT * FROM content WHERE freetext(description,"the")

Result

                              Server: Msg 7619, Level 16, State 1, Line 1

The query contains only ignored words; we've queried a noise word here. You'll find "the" in the noise words file at \MSSQL7\FTDATA\SQLSERVER\CONFIG.
 

Query 5 (CONTAINS)

 

                              SELECT * FROM content WHERE contains(*,"home")

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
home.asp	/home.asp	This is the home page	home, SQL

Like the Freetext query, this searches all full-text-enabled columns for the keyword "home."
 

Query 6 (CONTAINS)

 

                              SELECT * FROM content WHERE contains(Description,'  "Magaz*"  ')

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

This statement queries the Description column for a word beginning with "Magaz." Note that the asterisk acts as a wildcard or placeholder, just as the percent sign (%) does with the LIKE keyword. (To make this work, you need to use single quotes on either side of the double quotes.)
 

Query 7 (CONTAINS)

 

                              SELECT * FROM content WHERE contains(Description,'  "*azine"  ')

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------

This search yields no results. You can't use an asterisk as a placeholder for a prefix.
 

Query 8 (CONTAINS)

 

                              SELECT * FROM content WHERE contains(Description,'  "Magazine" Or "Great"  ')

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

This full-text scan uses OR so that you can search for "Magazine" or "Great"; it also works with AND and AND NOT. (Again, note the single quotes around the search criteria.)
 

Query 9 (CONTAINS)

 

                              SELECT * FROM content WHERE CONTAINS(description, 'NT NEAR great')

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second

This search on the Description column finds all rows where "NT" is near "great".
 

Query 10 (CONTAINS)

 

                              SELECT * FROM content WHERE contains(description, ' formsof (inflectional, great) ')

Pagename	URL	Description	Keywords
--------	---------	---------------------	---------
Pagetwo.asp	/page2/page2.asp	NT Magazine is great	second
Pagethree.asp	/page3/page3.asp	SQL Magazine is the greatest	third

This statement returns all results for "great," "greatest," "greater," and so on.

SQL Server 2008: How to query all databases sizes?

with fs

as

(

    select database_id, type, convert(numeric(18,2),round((size * 8.0 / 1024),2)) size

    from sys.master_files

)

select

    name,

    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,

    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB

from sys.databases db

Rebuild Every Index of All Tables of Database

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor

Enabling Database Mail on SQL Server Express

--1- I copied the three mentioned file.

--2- executed : dbo.sysmail_start_sp and dbo.sysmail_stop_sp

--3- Configured "Database Mail XPs' using (refering the comment by "John Hunter"):

--To fix the run the following script:

USE Master

GO

sp_configure 'show advanced options', 1

GO

reconfigure with override

GO

sp_configure 'Database Mail XPs', 1

GO

reconfigure

GO

sp_configure 'show advanced options', 0

GO

--4- created the a profile and set it as default:

-- Create new profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DoNotReply',

@description = 'Profile to send the notification emails'

GO

-- Set the New Profile as the Default

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'DoNotReply',

@principal_name = 'public',

@is_default = 1 ; -- Make this the default profile

GO

--5- created the account as:

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DoNotReply',

@description = 'my email description',

@email_address = 'DoNotReply@mydomain.com',

@display_name = 'My email display name',

@replyto_address = null,

@mailserver_name = 'mail.mydomain.com',

@username = 'DoNotReply@mydomain.com',

@password = 'my_password',

@port = 25, -- Check with your admin for correct port

@enable_ssl = False -- Enable ssl communication

--6- Added account to the profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DoNotReply',

@account_name = 'DoNotReply',

@sequence_number = 1

GO

--7-Made sure the define port (25 in my case) is allowed in the server.

--8- Test :

EXECUTE msdb.dbo.sp_send_dbmail

@recipients='receipient@recipientdomain.com',

@subject = 'Test e-mail sent from database mail',

@body = 'This is a test message sent from the newly created database mail account',

@reply_to = 'info@mydomain.com'

GO