Category Archives: T-SQL

T-SQL: Delete Rows in a Batch Loop

Here’s a simple pattern to perform an action in a batch loop with T-SQL. DECLARE @RowCount INT = 1; — Initialize to a value >0 to begin the loop   SET ROWCOUNT 20000; — The number of rows to delete … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

T-SQL: Describe a temp table with sp_help

If you are looking to verify the structure of a temp table you have created within a SQL query batch, you can use sp_help to describe the table. SELECT Top 1 * INTO #MyTempTable FROM dbo.SomeTable   EXEC tempdb.dbo.sp_help N’#MyTempTable’ … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

T-SQL: Try/Catch Pattern

Here’s a simple try/catch pattern for a SQL Server query to help roll back a transaction when something goes awry: BEGIN Tran;   BEGIN Try — do stuff END Try BEGIN Catch IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; Throw END … Continue reading

Posted in MS SQL Server, T-SQL | Leave a comment

T-SQL: Convert from UTC Date to Local Date

Here’s a quick T-SQL expression to convert from a UTC Date to Local Date: DATEADD(HH, DATEDIFF(HH, GETUTCDATE(), GETDATE()), MyUtcDate) AS MyLocalDate An even better idea would be to avoid the conversion by using the datetimeoffset sql column type.

Posted in T-SQL | Leave a comment

T-SQL: Disable Constraint Checks On All Tables With A Single Line Query

Microsoft’s “undocumented” but quite useful stored procedure, sp_msforeachtable, you can easily disable constraint checks on all tables in a database. Here’s how: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" To re-enable the checks after you are done, simply change … Continue reading

Posted in T-SQL | Leave a comment

T-SQL: Get Row Counts For Every Table In A Database

Here’s a quick way to get table row counts for all tables in a database using dynamic SQL, the built-in INFORMATION_SCHEMA.TABLES view, and the “magic” of “XML PATH(”)”:   DECLARE @TABLES TABLE (TableName NVARCHAR(1000))   INSERT @TABLES SELECT TABLE_SCHEMA + … Continue reading

Posted in T-SQL | Leave a comment