5 Microsoft SQL Server Scripts You Wished You Knew a Long Time Ago

Over the last few years there have been scenarios that as a developer I’ve come across many times and today I thought I’d share some SQL specific scripts that I find myself needing every once in a while and I always have to go hunting for them. This post will consolidate some of the more commonly useful ones that I think will be valuable to other developers as well. 

1. Identify/Delete Duplicate Records
   Here’s a script that will enable you to quickly identify and/or delete duplicate records from any SQL table:

   Select

   ;WITH cte AS (SELECT PersonID, ROW_NUMBER() OVER (PARTITION BY PersonID, OrganizationID ORDER BY isPrimary desc) RN FROM   Personstoorganizations)
   SELECT * FROM cte WHERE  RN > 1

   Delete

   ;WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY PersonID, OrganizationID ORDER BY isPrimary desc) RN FROM   Personstoorganizations)
   DELETE FROM cte WHERE  RN > 1

   You will notice that this snippet uses a CTE (Common Table Expression) in conjunction with the partition and row number feature available since SQL 2005. When using this snippet it is recommended to always use the Select before the Delete to ensure what is being returned “makes sense”. It’s also recommended to back up the database or run this snippet in a controlled development environment.

   The two import things in this snippet to adjust are the columns on which to partition again as well as the order by clause for said partition. In the example above the query is retrieving records where PersonID and OrganizationID in conjunction exist more than once in the database table and it is being sorted by the isPrimary flag to return the records that are “false”, since if any are true those we would like to keep over any that are duplicate and false.

2. Reseed Identity Columns For An Entire DatabaseSometimes the seed key for identity columns gets out of whack (especially after using some data comparison tools). In those cases this little script is a life saver:

   EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 
   BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

   Please note that this script makes use of the undocumented stored procedure found in the master database called “sp_MSForEachTable”. Since it’s undocumented you will not want to rely on it for any regular jobs, SSIS packages, etc., but instead use it on an as needed basis.

3. Finally See That Damn Restore Database Progress
   Have you ever started a database restore only to stare at that damn spinner for what seems like ages? Then being asked by upper management: “Where do we stand? When is this thing going to be back up?”? Well my friends you are not alone, behold:

   SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
   CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
   CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
   CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
   CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
   CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1 THEN 1000ELSE (r.statement_end_offset-r.statement_start_offset)/2 END) FROM sys.dm_exec_sql_text(sql_handle))) FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

   

4. Re-Index An Entire Database
   This is a quick and simple script to re-index a database and also update statistics:

   USE MyDatabase
   GO
   EXEC sp_MSforeachtable@command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
   GO 
   EXEC sp_updatestats
   GO

5. Clear All Data
   Before going live sometimes you are in need to do a database “reset” to get rid of all that test data and I’ve found the following script to be particularly useful in those cases:

   --Disable Constraints & Triggers 
   EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
   EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' 
   --Perform delete operation on all table for cleanup 
   EXEC sp_MSforeachtable 'DELETE ?' 
   --Enable Constraints & Triggers again 
   EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
   EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
   --Reset Identity on tables with identity column 
   EXEC sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'