othtim’s gists

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

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: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: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: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 / sanity checks

Created July 5, 2013 20:22

SchoolLogic database sanity checks

othtim / gist:6075601

Last active December 20, 2015 05:08

generate table of months, dates, hours. cross reference with actual useage data to avoid blank spaces in graphs.


	# i dont remember if i wrote this, or took it from somewhere

	select
	month_number,
	day_number,
	hour_number,
	isnull(d.data,0)
	FROM
	(

othtim / gist:6075658

Last active December 20, 2015 05:09

rebuild indexes with statistics


	# this still needs some work

	--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',
	si.name,
	sDips.index_id,

othtim / gist:6233930

Last active December 21, 2015 02:19

Move values from db A to db B

	<?php

	echo "Retaining lookup tables <BR /><BR />";



	///////////////////////////////////////////////////////////////////////////////////////

othtim / gist:6429001

Last active December 22, 2015 06:09


	# i dont remember what this is for

	----

	EXEC sp_configure 'show advanced options', 1
	GO
	RECONFIGURE
	GO
	EXEC sp_configure 'xp_cmdshell', 1

Tim Scott othtim