August 2013 Entries

How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers. The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.  I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create...

Simple command to disable all Foreign Key checks

After blogging about how to disable all Foreign Key constraints and the undocumented sp for looping through all tables in a database, I thought about combining the two. So I’ve now got a much easier way to disable FK constraints in a database. The script below will loop through each table and disable the FK check. --Disable FK Checks EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" I’ve got an application that has a SQL CE offline mode where the Foreign Key keyword is not supported when running scripts against it.  (At...

How to disable all Foreign Key contraints

Here is a script that will loop through all tables in your database and disable the foreign key constraints. DECLARE @TableName nvarchar(256) SET @TableName = '' WHILE @TableName IS NOT NULL BEGIN SET @TableName =(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +...

Add your own error message to a ValidationSummary

There are time where you need to display a message to the users.  Here’s how to do it with a Validation Summary on your ASP.NET page. I use this when I want to display an error message that doesn’t come from an exception or another validation. This is helpful when you want all messages to have the same look and feel. Below is the code. CustomValidator val = new CustomValidator(); val.IsValid = false; val.ErrorMessage = "My error...