It doesn't actually disable constraints that were previously disabled; run a quick test and see for yourself.

Here's a variant for FK's and CK's that fully mirrors the Trusted/Untrusted, Enabled/Disabled states (it also generates more than one column at once, suitable for greater automation).

If you're going to...

You've got a hardcoded 'mytable' in there; try to see if there's a significant speed difference in putting the object_id of 'mytable' directly into the DMV parameters as a number...

To cross-pollinate this article discussion with a closely related article that includes a very simple SQL statement for limited scripting of SQL 2005 indexes:

http://www.sqlservercentral.com/scripts/T-SQL/71058/[/url]

Thank you for the useful and very compact script. While I understand not supporting SQL 2008 features like filtered indexes and data compression, you're also missing support for include...

EDITED - minor IsClustered bug fixed

Alteration in the middle - the "Disallowed" requires a double negation logic:

IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0

SET @sParamSQL = @sParamSQL + 'ON,'

ELSE

...

Minor alterations at the end:

-- Commas removed from the end of ON and OFF so variable following arguments work properly with leading commas

IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)

SET @sParamSQL =...

This is wonderful!

But does anyone know how to figure out what the DATA_COMPRESSION setting is for a given index (none, row, or page)?

It doesn't appear to be in...

Turn on SQL Profiler with TextData, HostName, ApplicationName, CPU, Reads, Writes, and Duration (and whatever else you like) for the SQL:BatchCompleted and RPC:Completed events only.

Set it to run...

I regularly set up routine index maintenance and backups on 3rd party databases.

I often add a new database containing my preferred basic tools into the SQL Server instance (a smallint...

One other trick is to put TempDB on, say, a pair of local SSD's in RAID1; keep it off the SAN.

Given the price per SAN spindle, and the...

Simple example:

DISK ROTATIONAL SPEED

A 15000

B ...

Check both systems for Untrusted constraints; it's very common to disable a constraint, add data, and re-enable it without the required "WITH CHECK CHECK" syntax.

SELECT

CASE OBJECTPROPERTY(soConstraint.id,'CnstIsNotTrusted')

...

I'll simply agree with statements from two prior posts, slightly reworded:

1) In practice, with normal humans using the data, NULL must have an explicit operational definition to be meaningful. ...

I don't know, but I'm very interested in the answer as well.

Are you using plain Excel 2010, or PowerPivot for Excel 2010?