Skip to content

Instantly share code, notes, and snippets.

View ronmichael's full-sized avatar

Ron Michael Zettlemoyer ronmichael

View GitHub Profile
@ronmichael
ronmichael / tables_and_columns_view.sql
Created February 19, 2016 22:40
A view of all tables and columns
select
dbo.sysobjects.name TableName,
dbo.syscolumns.name ColumnName,
dbo.systypes.name as Type
from dbo.sysobjects
join dbo.syscolumns on dbo.syscolumns.id = dbo.sysobjects.id
join dbo.systypes on dbo.systypes.xtype = dbo.syscolumns.xtype
where dbo.systypes.name != 'sysname'
@ronmichael
ronmichael / missing-permissions.sql
Created March 13, 2015 22:47
Identify all MSSQL database objects with no defined permissions
select type_desc, name
from sys.objects o
left join sys.database_permissions p on p.major_id=o.object_id
where p.class is null
and type_desc not in ('SYSTEM_TABLE','INTERNAL_TABLE','SERVICE_QUEUE','SQL_TRIGGER','TYPE_TABLE')
and type_desc not like '%CONSTRAINT%'
order by type_desc, name
@ronmichael
ronmichael / allparents.sql
Created February 5, 2015 14:22
Return all parent records in SQL hierarchy
/*
if you have a table of rows with parent rows, this will return one row for each row and each of its parents.
thanks to http://stackoverflow.com/questions/13487006/use-sql-server-cte-to-return-all-parent-records.
*/
with items(uniqueid,parentid) as (
select uniqueid, uniqueid
from equipment
@ronmichael
ronmichael / formatphone.js
Created November 8, 2014 18:27
Format a phone number
/*
Formats a typical US phone number, including an extension (if preceeded by something like x, ex, ext, etc).
Crude but adequate.
*/
String.prototype.formatPhone = function () {
var phone = this;
if (!phone) return "";
@ronmichael
ronmichael / analyze_tempdb_performance.sql
Last active August 29, 2015 14:05
Analyze MSSQL tempdb performance
SELECT
files.physical_name,
files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id
WHERE files.type_desc = 'ROWS'
/*
@ronmichael
ronmichael / merge-schedule.sql
Last active May 24, 2016 20:15
Merge and delete redundant rows. Say you have a table that represents a calendar and hours worked by individuals and say you want to merge "duplicate" events on the same day (same person, same day, same type of event). You want the remaining record to include the sum of all hours of all the redundant records and for the other records to be removed.
declare @personid int = 1900, @start date = '2/24/2014', @stop date ='3/2/2014';
merge people_schedules ps
using (
select row_number() over ( partition by ps2.eventid, ps2.date order by ps2.uniqueid ) as row, ps2.uniqueid, ps2.personid, ps2.eventid, ps2.date, ps2.servicerequestid,
sum(ps3.labortimeregular) LaborTimeTotal,
from People_Schedules ps2
join People_Schedules ps3 on ps2.eventid=ps3.eventid and ps2.personid=ps3.personid and ps2.date=ps3.date
@ronmichael
ronmichael / nodependencies.sql
Created January 30, 2014 20:31
MSSQL: Report on all database objects that have nothing else depending on them - that are not referenced by any other objects in the database.
select o.type_desc type, o.name
from sys.OBJECTS o
left join sys.sql_expression_dependencies ref on ref.referenced_id = o.object_id
left join sys.OBJECTS o2 on o2.object_id = ref.referencing_id
where o2.name is null
and o.type_desc not in ('SYSTEM_TABLE','SQL_TRIGGER','INTERNAL_TABLE','SERVICE_QUEUE','TYPE_TABLE')
and o.type_desc not like '%CONSTRAINT%'
order by o.type_desc, o.name
declare @x xml = '<data UniqueID="1" Name="Bob"/><data UniqueID="2" Name="Joan"/>'
select * from dbo.fnu_XML_ToTable(@x)
@ronmichael
ronmichael / logon-trigger.sql
Created October 25, 2013 14:14
Apply custom logic to prevent some users from logging into MSSQL by applying a logon trigger. This particular example looks at the user's name as well as IP address.
CREATE TRIGGER [access_trigger]
ON ALL SERVER
with execute as 'sa' -- needed to query sys.dm_exec_connections table
FOR LOGON
AS
BEGIN
if original_login() not in ('superadmin', 'anothersuperadmin', 'mydomain\admin')
and exists (
@ronmichael
ronmichael / getTextBoundingRect.js
Created September 6, 2013 22:01
Get the bounding box coordinates of the actual text within an HTML input box
// http://stackoverflow.com/questions/6930578/get-cursor-or-text-position-in-pixels-for-input-element/7948715#7948715
function getTextBoundingRect(input, selectionStart, selectionEnd, debug) {
// Basic parameter validation
if (!input || !('value' in input)) return input;
if (typeof selectionStart == "string") selectionStart = parseFloat(selectionStart);
if (typeof selectionStart != "number" || isNaN(selectionStart)) {
selectionStart = 0;
}