Tim Scott othtim

othtim / sanity checks

Created July 5, 2013 20:22

SchoolLogic database sanity checks

othtim / gist:5936989

Created July 5, 2013 20:19

use OLE Automation procedures to create a decent regex in t-sql




	sp_configure 'Ole Automation Procedures', 1;
	RECONFIGURE


	GO
	CREATE FUNCTION dbo.RegexReplace
	(

othtim / gist:5936971

Created July 5, 2013 20:17

check users logged in in SchoolLogic database

	--assumes default IIS/ASP.net timeout of 20 minutes

	select
	row_number() over (partition by cusername order by cusername) as [count]
	, cUserName, tLogin, cApplication, cmachinename
	from log
	where DATEDIFF(MINUTE, tlogin, CURRENT_TIMESTAMP) < 20
	and lSuccess = 1
	and tLogout = ''
	order by tLogin desc

othtim / gist:5936956

Created July 5, 2013 20:14

stats in pre- and post- reindex fragmentation






	--save PRE stats on index fragmentation
	IF OBJECT_ID('tempdb..#tmp_PRE_reindex', 'U') IS NOT NULL DROP TABLE #tmp_PRE_reindex
	SELECT
	ROW_NUMBER() over (order by sDips.index_id) as 'rowid',
	OBJECT_NAME(sDips.OBJECT_ID) as 'objid',

othtim / gist:5936927

Last active October 24, 2018 00:42

shows reads/writes on all tables in a DB as percentages. for choosing better indexes



	--show stats on table reads/writes
	Select
	object_schema_name(UStat.object_id) + '.' + object_name(UStat.object_id) As [Object Name],
	Case
	When sum(User_Updates + User_Seeks + User_Scans + User_Lookups) = 0
	Then Null
	Else convert(decimal(5,2),round(Cast(sum(User_Seeks + User_Scans + User_Lookups) As Decimal)
	/ Cast(sum(User_Updates

othtim / gist:5936805

Last active December 19, 2015 09:59

fizzbuzz tsql



	IF OBJECT_ID ( 'dbo.fb', 'P' ) IS NOT NULL
	drop procedure dbo.fb;

	GO

	CREATE PROCEDURE dbo.fb (@int int)
	AS