The SQL Full Text Catalog, provides a fast search of you products into you DashCommerce's  database. But what happens if you mess up the catalog? You will receive errors like

A critical error has occurred: Cannot drop full-text catalog 'dashCommerce_Catalog' because it contains a full-text index

and you will not able to drop the catalog

Cannot drop full-text catalog 'dashCommerce_Catalog' because it contains a full-text index.

Unless you follow the steps illustrated below.

Connect to you DashCommerce's database,  and launch:

SELECT name, ftcatid FROM sysobjects WHERE ftcatid > 0
GO

This will return the table where the Full Text Catalg is use:

Now, we can drop it:


EXEC sp_fulltext_table 'dashCommerce_Store_Product', 'drop'
GO

DROP FULLTEXT CATALOG dashCommerce_Catalog
GO

And recreate it, by using the fulltextcatalog.sql script, located under /Install/Scripts

EXEC sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG dashCommerce_Catalog
GO

CREATE FULLTEXT INDEX ON [dbo].[dashCommerce_Store_Product] KEY INDEX [PK_dashCommerce_Products] ON [dashCommerce_Catalog] WITH CHANGE_TRACKING AUTO
GO

ALTER FULLTEXT INDEX ON [dbo].[dashCommerce_Store_Product] ADD ([Name] LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [dbo].[dashCommerce_Store_Product] ADD (ShortDescription LANGUAGE 1033)
GO
ALTER FULLTEXT INDEX ON [dbo].[dashCommerce_Store_Product] ADD (BaseSku LANGUAGE 1033)
GO

In SQL Server 2005 you can get a list of all your Tables with a particular name using the system table sys.table:

SELECT *

FROM sys.tables

WHERE name LIKE '%YOUR_TABLE_NAME%'

but if you need to do the same task in a SQL Server 2000, you should use

use YOURDB

select *

from INFORMATION_SCHEMA.TABLES

where TABLE_NAME LIKE '%YOUR_TABLE_NAME%'

because sys.table is not supported into SQL 2000.

Here's a reminder for myself when I need to launch a stored procedure with an output parameter directly from SQL Server Management. Who knows... maybe can helps you too.

declare @P1 int

set @P1=NULL

declare @P2 char(1)

set @P2=NULL

exec STORED_PROCEDURE 0, 'foo', 144133917, '0', @P1 output, @P2 output select @P1, @P2

go

Let's says you need to find out all the stored procedures that reference a column or that just contains a text. You can use:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%foobar%' 
    AND ROUTINE_TYPE='PROCEDURE'

Things get simplier if you are in SQL Server 2005; start a new query and paste this:

SELECT Name 
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%' 

Reference: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

If you need to query your sql server's database to find out where a specific column is present into a table's schema, you can lauch this query against your database.

USE YOUR_DATA_BASE_NAME
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%YOUR_Column_NAME%'
ORDER BY schema_name, table_name;