Tag: SQL Server

Shhh! Secret Procedures!

I don’t have to tell you that the folks at Microsoft can be quite sneaky so, in case you don’t already know, there are a number of unofficially documented SQL stored procedures that can make your life not only better, but often cursor-free.

The one that is most helpful to me is entitled “sp_msforeachtable.” Using this stored procedure you can accomplish the same tasks on each table.

You can Google it for more information but a short and simple article can be found at DatabaseJournal.com:

“Beginning with version 6.5 of SQL Server, Microsoft provides a stored procedure called sp_MSreachfortable. Using the question mark as a place holder for all table names, the procedure will do the same as the above query in a single line. You can replace the above cursor with this :

sp_MSforeachtable @command1=”print ‘?’ dbcc checktable (‘?’)”

For more handy secrets, do a search on “undocument sql server stored procedures” or http://lmgtfy.com/?q=undocumented+sql+server+stored+procedures .

Disabling and Enabling Table Constraints

Back in the ages of pre-Sql Server 2005, we had to drop and re-create constraints to modify certain data. I guess it was habit when I found myself beginning to do this today. Fortunately, I remembered the newer-improved way (of course, I DID have to look it up). Instead of dropping and re-creating constraint,s they can simply be disabled and enabled:

ALTER TABLE testTable NOCHECK CONSTRAINT FK_testTable_Task

–do your data deletes, etc here

ALTER TABLE testTable CHECK CONSTRAINT FK_testTable_Task

Voila, no risky dropping involved. Of course, be careful when manipulating your data in such a manner. This should not be normal practice.