Sometimes you need to delete all dictionary items from the Umbraco dictionary. 

The problem

Deleting 100’s of dictionary items can be a PITA.  Imagine you have to right click for every dictionary item and select delete.

Side information

First you probably want to know which tables the dictionary is using.  There are 2: one for the items, one for the translations.  They call cmsDictionary and cmsLangaugeText. 

If you ever want to get information from the dictionary, just join these tables and you have what you need.

select d.pk, lt.pk, d.[key], lt.languageId, l.languageISOCode, lt.value
from cmsDictionary d inner join cmsLanguageText lt on d.id = lt.UniqueId
left join umbracoLanguage l on lt.languageId = l.id
where d.pk = 6

Solution

The fastest way to remove all umbraco dictionary items is through SQL.  To remove ALL dictionary items, just run this SQL script:

delete from cmsLanguageText
delete from cmsDictionary

Don’t forget to touch the web.config because dictionary items are heavily cached!

There are a lot of people struggling with finding data.  What about these two solutions... Google gave me these solutions...

When you know little about a database or can't remember it is often nice to be able to do global type searches on that database. This scenario can be thought of in two ways.

• Search database schema
• Search database data

Below I describe how to do both on a MS SQL database.

Search MS SQL Server for any text | Just Geeks 

And what do you think of this problem...  I guess you know the problem...

I was forced recently to do some maintenance and bug fixing on an aging .NET-based CMS.

Most of the problems were in the SQL Server database, with lots of corrupted entries. The most frustrating thing however was that at times I didn’t even know where to find the entries. Try finding some specific strings in a database with a hundred tables, each with many columns (poorly named, obviously) and tens of thousands of records – it’s like finding the proverbial needle in the haystack.

Searching in all tables and columns of a database | RichNetApps Developers' blog

DDL stands for Data definition language.  Nothing new until now, you might know DDL as the alter, create and drop statements from SQL.

However, in Microsoft SQL Server 2005 it is possible to add triggers for these statements. 

These kind of triggers allow you to audit these potentially dangerous statements:

CREATE TRIGGER safety
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE
AS
   PRINT 'You must disable Trigger "safety" to drop, create or alter tables!'
   ROLLBACK ;

DDL triggers fire only in response to DDL events specified by Transact-SQL DDL syntax. System stored procedures that perform DDL-like operations are not supported.

To see a list of the triggers created (in the system catalog), you can use this statement

SELECT name,te.type_desc,te.type
FROM sys.triggers t
JOIN sys.trigger_events te on t.object_id = te.object_id
WHERE t.parent_class=0
AND name IN('ddlTestEvents1','ddlTestEvents2')
ORDER BY name,te.type_desc

Where ddlTestEvents1 and ddlTestEvents2 are of course the names of the events.

If you need to create for the 3 statements the same trigger, you can use

FOR DDL_TABLE_EVENTS

instead of all the individual events.  You will observe the same effect in the triggers catalog.

Today I got a very strange error on my Microsoft Access ADP Project.

Microsoft Office Access can't find the object 'SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_litextendedproperty(N'MS_DisplayViewsOnSharePointSite', N'user', N'dbo', N'table', N'TableName', NULL, NULL)'.

To be honest, I had no idea what this meant.  Neither the explanation followed by this error.
After thinking a little while, I found the solution.

Earlier today I tried to add a database schema.  And deleted it afterwards. 

If you look at the tables changed by the database schema, and the tables which where not involved, the only difference are the Extended Properties on the tables.  The tables with extended properties will also give this error in Microsoft Access 2007.  For the record, I am using SQL Server 2005 for this project.

The solution

What you need to do: open the table properties in Microsoft SQL Server Management Studio.
Then go the extended properties. 

When I deleted the MS_Filter, MS_OrderBy my problem was gone.