RickyAdams.com

SELECT (SELECT RIGHT(‘00000’ + CONVERT(VARCHAR(5),FLOOR(CAST([EP_departmentnumbertext] AS INT)/1000)*1000), 5)) AS DIVISION, * from [databasename].[dbo].[people] ep where EP_NAME like ‘%Adams%’

Recover SA Password on Microsoft SQL Server Introduction This article applies to Microsoft SQL Server 2005, and Microsoft SQL Server 2008 at the time of this writing. If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB. Members of the server’s Local Administrator’s group can access SQL server…

Read More Read More

TSQL Table Row Counts for All Database Tables SELECT TableName = t.NAME, TableSchema = s.Name, RowCounts = p.rows FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id WHERE t.is_ms_shipped = 0 GROUP BY t.NAME, s.Name, p.Rows ORDER BY s.Name, t.Name; go  

TSQL Delete Large Number of Rows Without Filling the Transaction Log Script to delete in chunks with commit every number of rows:   DECLARE @Count INT Declare @for_delete INT Declare @chunk_size INT SELECT @chunk_size=10000 SELECT @Count = 0 select @for_delete=count(*)from [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’ While (@Count < @for_delete) BEGIN SELECT @Count = @Count + @chunk_size BEGINTRAN DELETE top(@chunk_size) FROM [ION_Data].[dbo].[DataLog2] where TimestampUTC <‘2014-01-01’ COMMIT TRAN   Ref Source: http://dbtricks.com/?p=44  5/22/2015