How to disable all foreign key constraints on all tables in SQL Server?

The other day I wanted to import some table data from Excel using the import data facility. But, as it goes, SQL Server insisted to import some tables having FKs before their corresponding PK tables. So we have to disable/enable FK constraints on some or all tables.
Here is a very usefull script from decipherinfosys
I tried this out on SQL server 2000. Its working fine.

/************** DISABLE ALL TABLE CONSTRAINTS *********************************

This script will disable all constraints on all tables within the database
that it is run in.

******************************************************************************/

SET NOCOUNT ON
SET ROWCOUNT 0

DECLARE @Count int
DECLARE @String nvarchar (1000)
DECLARE @ConstraintName varchar(128)
DECLARE @TableName varchar(128)

--Find all constraints and their respective tables from the sysobjects table and place into a temp table.
--Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
--You should use the ALTER INDEX...DISABLE command in SQL Server 2005
SELECT
name AS constraintname,
object_name(parent_obj)  AS tablename
INTO #Const_Table
FROM sysobjects s
where xtype in ('F')

SELECT @Count = Count(*) FROM #Const_Table

--Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
--Used as an alternative to a cursor.
SET ROWCOUNT 1

--Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
--The rowcount of one ensures that only one tablename and constraint name is picked.
SELECT @TableName = TableName, @ConstraintName = ConstraintName
FROM #Const_Table

--Build execution string to disable constraint.
SET @String = 'ALTER TABLE ['+ @tablename + '] NOCHECK CONSTRAINT [' + @constraintname +']'

--Execute the SQL
exec sp_executesql @string

--Remove this row from the temp table, since it has now been processed.
DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName

SET @Count = @Count - 1
END -- Loop

set rowcount 0

After having re-enabled the constrains you can check constraints using the following command:

DBCC CHECKCONSTRAINTS

And look at your constraints using this command:

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

IEnumerable, IEnumerators, IEnumerable<string> and yield

If you want to use custom data collection in the foreach loop, you have to make sure that the collection imprements a GetEnumerator() method that returns an IEnumerator interface.
In order to indicate that your list implements the GetEnumerator() method you can implement the IEnumerable interface as follows:

public class MyEnumerableList : IEnumberable
{
   public IEnumerator GetEnumerator(){...}

}

Here are the framework definitions for .NET Framework 1

public interface IEnumerable 
{
   IEnumerator GetEnumerator();
}
public interface IEnumerator 
{
   object Current{get;}
   bool MoveNext();
   void Reset();
}

Here is a custom class that implements an iterator in .Net 1.

public class MyNumbers : IEnumerable
{
    string[] _numberNames = { "Eins", "Zwei", "Drei", "Vier" };

    IEnumerator IEnumerable.GetEnumerator()
    {
        return new MyEnumerator(this);
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable)this).GetEnumerator();
    }

    //Inner class for the iteration
    class MyEnumerator : IEnumerator
    {
        // inner collection
        MyNumbers _myNumbers;
        // keep the index in mind
        int _current;
        // constructor
        public MyEnumerator(MyNumbers collection)
        {
            _myNumbers = collection;
            _current = -1;
        }
        void IEnumerator.Reset()
        {
            _current = -1;
        }
        bool IEnumerator.MoveNext()
        {
            _current++;
            return (_current < _myNumbers._numberNames.Length);
        }
        string IEnumerator.Current
        {
            get
            {
                if (_current == -1) throw new InvalidOperationException();
                return _myNumbers._numberNames[_current];
            }
        }
        object IEnumerator.Current
        {
            get
            {
                return ((IEnumerator)this).Current;
            }
        }
        public void Dispose() { }
    }
}

In the .NET Framework 2.0 they added a generic interface that inherits from the older one.

public interface IEnumerable<T> : IEnumerable
{   
   IEnumerator<T> GetEnumerator();
} 
public interface IEnumerator<T> : IEnumerator,IDisposable
{
   T Current{get;}
}

Add now thanks to the new yield statement the compiler generates the inner iterator class itself in order to keep the state.

public class MyNumbers2 : IEnumerable<string>
{
    string[] _numberNames = { "Eins", "Zwei", "Drei", "Vier" };
    IEnumerator<string> IEnumerable<string>.GetEnumerator()
    {
        for (int i = 0; i < _numberNames.Length; i++)
            yield return _numberNames[i];
    }
    IEnumerator IEnumerable.GetEnumerator()
    {
        return ((IEnumerable<string>)this).GetEnumerator();
    }
}

Just created this blog

I've just created this new blog about things I found on the web or made up and like to put somewhere if not in mind.

This will be mainly about IT, programming, philosophy, language learning etc.

"Donkey bridge" is not really English - the correct word would be "Mnemonic Device". But I prefer the direct and literal translation "DONKEY BRIDGE" of the expression we use in German: "Eselsbrücke".