One of the things that I have found disappointing about SQL 2005 Management Studio apart from the severe slowness of the GUI compared to 2k is the fact that there is no option (that I can find) that will allow you to generate the necessary scripts for fulltext indexes. If you select a fulltext catalog under the storage section and choose to script a create you will get the following:
CREATE FULLTEXT CATALOG [System_Help]
IN PATH N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData'
WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]
Which is just the script to rebuild the catalog and not the actual indexes within the catalog.
Obviously this is not very useful if you are wanting to quickly re-create the indexes along with the catalog and hopefully they will remedy this in SQL 2008. However until that day comes you can either rebuild the indexes by hand using the GUI or use a script like the one I have created which will:
- Script a CREATE FULLTEXT CATALOG statement for the catalog.
- Loop through each fulltext index within the catalog and script the necessary CREATE FULLTEXT INDEX statements.
An example of the SQL generated by my script is below. Its a catalog I created purely for testing that contains two indexes.
CREATE FULLTEXT CATALOG SystemHelp
WITH ACCENT_SENSITIVITY = OFF
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_CLIENTS] (
CompanyName Language 1033
,description Language 1033
,email Language 1033
,address1 Language 1033
)
KEY INDEX PK_tbl_CLIENTS
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO
CREATE FULLTEXT INDEX ON [dbo].[tbl_SYSTEM_HELP] (
Title Language 1033
,Article Language 1033
,Tags Language 1033
,PageName Language 1033
,RelatedSection Language 1033
)
KEY INDEX PK_tbl_SYSTEM_HELP
ON SystemHelp
WITH CHANGE_TRACKING AUTO
GO
Obviously you can take my script modify and extend it to incorporate any missing settings that you require as I have created it purely for moving my own existing indexes. However as the script stands its another good example of using the system views to solve common DBA problems and I am still scratching my head about why you can script almost every other object in SQL 2005 apart from the fulltext indexes. Maybe there is a hidden button I have not found yet! If anyone knows where it is please show me.
Posts
8 comments:
Hi Rob,
Nice bit of code!
I just had a couple of problems with it:
1. It doesn't handle full text indexed views (and seemed to get stuck in an infinite loop of printing blank lines), and
2. It seemed to miss some tables/views in the catalogue.
Below is my modified version which seems to fix these, the changes were quite simple - for prob 1 replace "sys.tables" with "sys.objects" in the first looped select, and for prob 2. ensure this select is in object_id order.
Cheers!
Nick.
/*
Author: Rob Reid
Create Date: 12-Mar-09
Details: SQL script to generate the SQL required to script out a specified fulltext catalog
and all fulltext indexes within that catalog.
Modified 25-Mar-09 by NGB, to accomodate FT Indexed views
*/
DECLARE @Catalog NVARCHAR(128),
@SQL NVARCHAR(MAX),
@COLS NVARCHAR(4000),
@Owner NVARCHAR(128),
@Table NVARCHAR(128),
@ObjectID INT,
@AccentOn BIT,
@CatalogID INT,
@IndexID INT,
@Max_objectId INT,
@NL CHAR(2),
@Path NVARCHAR(255)
-- Specify name of catalog to script
SELECT @Catalog = 'MyCatalogue'
SELECT @NL = CHAR(13)+CHAR(10) --Carriage Return
-- Check catalog exists
IF EXISTS(SELECT Name FROM sys.fulltext_catalogs WHERE Name=@Catalog)
BEGIN
-- Store the catalog details
SELECT @CatalogID = i.fulltext_catalog_id
,@ObjectID = 0
,@Max_objectId = MAX(object_id)
,@AccentOn = is_accent_sensitivity_on
,@Path = [path]
FROM sys.fulltext_index_catalog_usages as i
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
GROUP BY i.fulltext_catalog_id,[path],is_accent_sensitivity_on
-- Script out catalog
PRINT 'CREATE FULLTEXT CATALOG ' + @Catalog + @NL
PRINT 'IN PATH N' + QUOTENAME(@Path, '''') +@NL
PRINT 'WITH ACCENT_SENSITIVITY = ' + CASE @AccentOn WHEN 1 THEN 'ON' ELSE 'OFF' END
PRINT 'GO'
-- Loop through all fulltext indexes within catalog
WHILE @ObjectID < @Max_objectId
BEGIN
SELECT TOP 1
@ObjectID = MIN(i.object_id)
,@Owner = u.Name
,@Table = t.Name
,@IndexID = unique_index_id
FROM sys.objects as t
JOIN sysusers as u
ON u.uid = t.schema_id
JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
AND i.object_id > @ObjectID
GROUP BY i.object_id, u.Name,t.Name,unique_index_id
ORDER BY i.object_id
-- Script Fulltext Index
SELECT @COLS = NULL,
@SQL = 'CREATE FULLTEXT INDEX ON ' + QUOTENAME(@Owner)+'.'+QUOTENAME(@Table)+' ('+@NL
-- Script columns in index
SELECT @COLS = COALESCE(@COLS+',','') + QUOTENAME(c.Name) + ' Language ' + CAST(Language_id as varchar) +' '+@NL
FROM sys.fulltext_index_columns as fi
JOIN sys.columns as c
ON c.object_id = fi.object_id
AND c.column_id = fi.column_id
WHERE fi.object_id = @ObjectID
ORDER BY fi.column_id
-- Script unique key index
SELECT @SQL = @SQL + @COLS + ') ' + @NL + 'KEY INDEX '+ QUOTENAME(i.Name) + @NL+
'ON ' + QUOTENAME(@Catalog) + @NL +
'WITH CHANGE_TRACKING ' + fi.change_tracking_state_desc + @NL + 'GO' + @NL
FROM sys.indexes as i
JOIN sys.fulltext_indexes as fi
ON i.object_id = fi.object_id
WHERE i.Object_ID=@ObjectID
AND Index_Id = @IndexID
-- Output script SQL
PRINT @SQL
END
END
Thanks for the comment. I personally had no issues with my version but then I wasn't using full text indexed views so thanks for the update. I'm sure there are other tweaks that can be done and I am still wondering why there isn't an option in the MS console to script all indexes within a catalog.
Hi,
Another minor improvement - I've added the ability to script the use of stoplists in SQL 2k8, it checks the compatibility level before doing so so it doesn't break with SQL 2k5.
/*
Author: Rob Reid
Create Date: 12-Mar-09
Details: SQL script to generate the SQL required to script out a specified fulltext catalog
and all fulltext indexes within that catalog.
From: http://blog.strictly-software.com/2009/03/sql-server-2005-script-fulltext-indexes.html
Modified 25-Mar-09 by NGB, to accomodate FT Indexed views
Modified 17-Feb-10 by Andrew Hancox, to accomodate FT Indexed views
*/
DECLARE @Catalog NVARCHAR(128),
@SQL NVARCHAR(MAX),
@COLS NVARCHAR(4000),
@Owner NVARCHAR(128),
@Table NVARCHAR(128),
@ObjectID INT,
@AccentOn BIT,
@CatalogID INT,
@IndexID INT,
@Max_objectId INT,
@NL CHAR(2),
@Path NVARCHAR(255)
-- Specify name of catalog to script
SELECT @Catalog = 'MyCatalogue'
SELECT @NL = CHAR(13)+CHAR(10) --Carriage Return
-- Check catalog exists
IF EXISTS(SELECT Name FROM sys.fulltext_catalogs WHERE Name=@Catalog)
BEGIN
-- Store the catalog details
SELECT @CatalogID = i.fulltext_catalog_id
,@ObjectID = 0
,@Max_objectId = MAX(object_id)
,@AccentOn = is_accent_sensitivity_on
,@Path = [path]
FROM sys.fulltext_index_catalog_usages as i
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
GROUP BY i.fulltext_catalog_id,[path],is_accent_sensitivity_on
-- Script out catalog
PRINT 'CREATE FULLTEXT CATALOG ' + @Catalog + @NL
PRINT 'IN PATH N' + QUOTENAME(@Path, '''') +@NL
PRINT 'WITH ACCENT_SENSITIVITY = ' + CASE @AccentOn WHEN 1 THEN 'ON' ELSE 'OFF' END
PRINT 'GO'
-- Loop through all fulltext indexes within catalog
WHILE @ObjectID < @Max_objectId
BEGIN
SELECT TOP 1
@ObjectID = MIN(i.object_id)
,@Owner = u.Name
,@Table = t.Name
,@IndexID = unique_index_id
FROM sys.objects as t
JOIN sysusers as u
ON u.uid = t.schema_id
JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
AND i.object_id > @ObjectID
GROUP BY i.object_id, u.Name,t.Name,unique_index_id
ORDER BY i.object_id
-- Script Fulltext Index
SELECT @COLS = NULL,
@SQL = 'CREATE FULLTEXT INDEX ON ' + QUOTENAME(@Owner)+'.'+QUOTENAME(@Table)+' ('+@NL
-- Script columns in index
SELECT @COLS = COALESCE(@COLS+',','') + QUOTENAME(c.Name) + ' Language ' + CAST(Language_id as varchar) +' '+@NL
FROM sys.fulltext_index_columns as fi
JOIN sys.columns as c
ON c.object_id = fi.object_id
AND c.column_id = fi.column_id
WHERE fi.object_id = @ObjectID
ORDER BY fi.column_id
-- Script unique key index
SELECT @SQL = @SQL + @COLS + ') ' + @NL + 'KEY INDEX '+ QUOTENAME(i.Name) + @NL+
'ON ' + QUOTENAME(@Catalog) + @NL +
'WITH CHANGE_TRACKING ' + fi.change_tracking_state_desc + @NL
FROM sys.indexes as i
JOIN sys.fulltext_indexes as fi
ON i.object_id = fi.object_id
WHERE i.Object_ID=@ObjectID
AND Index_Id = @IndexID
declare @compLevel int
select @compLevel = compatibility_level from sys.databases where name=db_name()
if @compLevel = 100
BEGIN
select @SQL = @SQL + ', STOPLIST = ' +stplsts.name + @NL
from sys.fulltext_indexes idxs
inner join sys.fulltext_stoplists stplsts
on idxs.stoplist_id=stplsts.stoplist_id
where idxs.Object_ID=@ObjectID
END
SELECT @SQL = @SQL + 'GO' + @NL
-- Output script SQL
PRINT @SQL
END
END
Thanks a lot, Rob (and Andrew). This was exactly what I was looking for.
Thanks Rob and Andrew. Oddly I had the same problem as Andrew, only 1 of the 3 indexes in my catalog was output and I don't use any views. Andrew's modified version worked though :D
Oops, I should have credited Nick rather than Andrew in my comment as it was his version I used :|
I have changed the script a little, so that it scripts all Fultextes in one database:
/*
Details: SQL script to generate the SQL required to script out all fulltext catalogs in one database
and all fulltext indexes within that catalog.
*/
DECLARE @Catalog NVARCHAR(128),
@SQL NVARCHAR(MAX),
@COLS NVARCHAR(4000),
@Owner NVARCHAR(128),
@Table NVARCHAR(128),
@ObjectID INT,
@AccentOn BIT,
@CatalogID INT,
@IndexID INT,
@Max_objectId INT,
@NL CHAR(2)
-- Loop thouh all Catalogs in the Database
declare KatalogCursor CURSOR FOR
SELECT name FROM sys.sysfulltextcatalogs
--Declare @SQL nvarchar(4000)
OPEN KatalogCursor
FETCH NEXT FROM KatalogCursor INTO @Catalog
WHILE @@FETCH_STATUS = 0
BEGIN
-- Specify name of catalog to script
PRINT '-- Fulltext Catalog = ' + @Catalog
SELECT @NL = CHAR(13)+CHAR(10) --Carriage Return
-- Check catalog exists
IF EXISTS(SELECT Name FROM sys.fulltext_catalogs WHERE Name=@Catalog)
BEGIN
-- Store the catalog details
SELECT @CatalogID = i.fulltext_catalog_id
,@ObjectID = 0
,@Max_objectId = MAX(object_id)
,@AccentOn = is_accent_sensitivity_on
FROM sys.fulltext_index_catalog_usages as i
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
GROUP BY i.fulltext_catalog_id,is_accent_sensitivity_on
-- Script out catalog
PRINT 'CREATE FULLTEXT CATALOG ' + @Catalog + @NL
PRINT 'WITH ACCENT_SENSITIVITY = ' + CASE @AccentOn WHEN 1 THEN 'ON' ELSE 'OFF' END
PRINT 'GO'
-- Loop through all fulltext indexes within catalog
declare FTCursor CURSOR FOR
SELECT
i.object_id
,u.Name
, t.Name
, unique_index_id
FROM sys.tables as t
JOIN sysusers as u
ON u.uid = t.schema_id
JOIN sys.fulltext_indexes i
ON t.object_id = i.object_id
JOIN sys.fulltext_catalogs c
ON i.fulltext_catalog_id = c.fulltext_catalog_id
WHERE c.Name = @Catalog
OPEN FTCursor
FETCH NEXT FROM FTCursor INTO @ObjectID,@Owner, @Table,@IndexID
WHILE @@FETCH_STATUS = 0
BEGIN
-- print 'ObjectID = ' + cast(@ObjectID as varchar(max))
-- Script Fulltext Index
SELECT @COLS = NULL,
@SQL = 'CREATE FULLTEXT INDEX ON ' + QUOTENAME(@Owner)+'.'+QUOTENAME(@Table)+' ('+@NL
-- Script columns in index
SELECT @COLS = COALESCE(@COLS+',','') + c.Name + ' Language ' + CAST(Language_id as varchar) +' '+@NL
FROM sys.fulltext_index_columns as fi
JOIN sys.columns as c
ON c.object_id = fi.object_id
AND c.column_id = fi.column_id
WHERE fi.object_id = @ObjectID
-- Script unique key index
SELECT @SQL = @SQL + @COLS + ') ' + @NL + 'KEY INDEX '+ i.Name + @NL+
'ON ' + @Catalog + @NL +
'WITH CHANGE_TRACKING ' + fi.change_tracking_state_desc + @NL + 'GO' + @NL
FROM sys.indexes as i
JOIN sys.fulltext_indexes as fi
ON i.object_id = fi.object_id
WHERE i.Object_ID=@ObjectID
AND Index_Id = @IndexID
-- Output script SQL
PRINT @SQL
FETCH NEXT FROM FTCursor INTO @ObjectID,@Owner, @Table,@IndexID
END
END
CLOSE FTCursor
DEALLOCATE FTCursor
FETCH NEXT FROM KatalogCursor INTO @Catalog
END
CLOSE KatalogCursor
DEALLOCATE KatalogCursor
I have updated the code to take into account all of your helpful changes plus I have cleaned it up, removed redundancy (e.g repeated calls to set the same variable etc), added an option to turn debug on and some better formatting (e.g tabbing).
Thanks for all your updates.
Post a Comment